Hello Folks,
I have a requirement when Layaway is pickedup, I should not show the Layaway’s (all that are tied up with this Layaway number) in the report. I am getting data as below - How can I do this in webi?
| LayawayNo | Retail Type | Retail Type desc |
|---|---|---|
| L001 | S01 | Setup |
| L001 | S02 | Deposit |
| L001 | S03 | Installment |
| L001 | S04 | Pickup |
I have tried using Hide and display within IF statement, but its only working for one row not all of the related Layaway’s. Appreciate your help.
Thanks,
Kris
Hi Kris,
Don’t you want to do it at the query level? If yes, you could use a subquery to exclude all Layaway Nos which have a Retail Type = S04. Below is what the subquery (in the filter area of query panel) would look like, and the data for those Layaways with Retail Type = S04, wouldn’t be even pulled in the report. Let us know if this helps. 
Thanks,
Mohammed
Hi Kris11,
Whatever Mohammed said is true if you can carry out at the Query level.
At the report level you may consider below idea.
Create the variable as below
var_lay : CASE WHEN retail_type_desc =‘Pickup’ Then LayawayNo Else ‘’ END.
From the above variable you will get all the list of the LayawayNo.
Apply the var_lay variable in Hide when condition as
=LayawayNo <>var_lay
Kindly let me know if it works.
Thanks,
Jagadeesh C
Hi Mohmmed,
Kudos for the sub query idea, it worked for me.
I appreciate your screenshot, that helped!
Regards, Kris
Happy to help! Once you start working with subqueries, you can’t stop, they help a lot in complex scenarios.
You can have a subquery looking at another context (which is ideal for developers, but complex for creating adhoc reports by business users), but you can create predefined filters in IDT or UDT using that subquery so they can use it in the reports.
At one of my clients, I created a predefined filter using a subquery, that would give a list of all All Active Members (for a specific membership), and users would use it in the main query where they’d be pulling Online Shopping Orders created by those members. The possibilities are many, enjoy working with the subqueries!
Mohammed
I didn’t knew about this feature until you pointed. Yes, it’s great feature to have and I am exploring this further.
Thanks again for sharing the idea.
Regards,
Kris
One limitation which is understandable and seems obvious that the subquery can’t call a different Universe in the subquery. But, I’m happy with being able to call other contexts from the Universe we’re using in the query.
Tag me in any of your complex requirements for creating sub query, let’s see if I’m able to help.
Thanks,
Mohammed
