Cascading Prompts in Webi, similar to Crystal Reports (NOT Cascading LOVs)
Many a time, we’ve seen developers/users asking for Cascading Prompts (similar to Crystal Reports), where the LOV’s of the subsequent filter (on object which is next level lower in hierarchy) should dynamically be filtered depending on the value chosen in the first filter and so on.
I’m using BusinessObjects 4.1 SP5 and eFashion Universe converted to UNX in this demo.
Example (check out the below image): Assume we have are 3 prompts in a report, on State, City and Store name. Our requirement is, as soon as we select a State (say Texas), the LOV for State should list only the Cities in Texas, and when we select a City (say Houston), then the LOV for Store name should only show list of Store names in Houston. We’ve such a feature in Crystal Reports, but not in Webi. (Side note: Cascading Input Controls was added in BI 4.2 SP3 which is cool!) What we want now are Cascading Prompts, and let me remind you, I’m not talking about Cascading LOVs (in which LOVs are displayed Hierarchically), there are a lot of blogs on how to achieve that already.
So, let’s see how to achieve that in Webi, oh wait, actually most (if not all) of the work is done in the Universe. The solution (idea) is simple:
- Create an LOV for City, with a Prompt on State in it, and associate that LOV to City dimension
- Create an LOV for Store name, with a Prompt on City in it, and associate that LOV to Store name dimension
Step 1: Launch the IDT (Information Design Tool) -> open the Business Layer (BLX) -> go to Parameters and Lists of Values -> create a List of values based on business layer objects for City with City in Result Objects and Prompt on State as below:
Assign this new City LOV to the City dimension
Step 2: Go back to Parameters and List of Values -> create a List of values based on business layer objects for Store name with Store name in Result Objects and Prompt on City as below
Assign this new Store Name LOV to Store name dimension
Step 3: Save and Publish the Universe to Repository
Step 4: Create a report to get few Store Details in Result Objects and a prompt on Store Name only as below, and click Run Query
Step 5: Click Run Query, we see 3 prompts, even though we created prompt on Store name (3rd prompt in the below image) only in the query. The first 2 are the prompts we created in LOVs of City and Store name in the Universe.
Something cool to note is, when you click on Enter City or Enter Store Name directly without answering the prompt for State is, we see a message that this prompt needs values for one of more following prompts as shown below:
Now, when we select a State (say Texas) in the prompt for State, and then click on prompt for City, we’ll see the LOV show a message Refreshing list of values (if your machine is slow as mine, or you may not get it)
And then, we’ll see the Cities in Texas as shown below.
And then, when we select a City (say Houston), we’ll see the Store names from Houston in the LOVs for Store name in prompt for Store name.
Voila, we got it.
Bonus: If you want to be able to select multiple values in State, City or Store name, you’ll have to update the Prompts in LOVs with In List option.
Hope you enjoyed this blog and will try it.
Good try..but did not understand how this is different from cascading LOV's.
other thing i am thinking user need to refresh the LOV's for each prompt every time.
As much difference, as there is between, Cascading LOV in Webi and Cascading Prompts in Crystal Reports 😉
Also, I've seen people requesting this specifically, as they don't want to see the whole list with all values at each level of hierarchy populated already, which is what happens in Cascading LOVs.
Just so you know, user's don't have to manually refresh the LOV's for each prompt every time, it happens automatically. Ex: As soon as we select a value fro State (in its prompt), the LOV for Cities is refreshed automatically and populated.
Thanks,
Mahboob Mohammed
Good stuff Mahboob! Keep posting!
Ray Khan
Thanks Ray Khan!
Very nice post!
Glad you liked it Mona!
Sounds Good,
I have one requirement like this with small change need solution.
In Webi report Cascading Prompts in LOV's with multiple repeated data with semicolon
City is column name, having multiple repeated LOV's with semicolon for different Mem ID's
In webi report prompt i need to show the distinct values of city alone also if i select one city repeated for same city with differen Member ID data has to come in report
Ex:
MemID City
~~~~~~~~~~~~~
Mem56 Texas
Mem57 SanAntonio; Texas
Mem58 Dallas;Texas;SanAntonio
Mem59 Dallas;SanAntonio
Mem60 Austin;Texas
Mem61 Austin;Colorado
Mem62 Florida;Austin;Texas
Expected Output:
~~~~~~~~~~~~~~~~~~~~~
When i run the report user have to get the city prompt inlist as below
Texas
SanAntonio
Dallas
Austin
Colorado
Florida
If user selects the Texas it is repeated in 5-times and Also user gets Mem56, Mem57, Mem58, Mem60 and Mem62 ID's data into the report.
Please give me any solutions.
Thanks,
Gopinath Thangaraj
Hi Gopi,
Thanks for stopping by on my blog post, glad you liked it!!
This can be a nice question for discussion, can you please post it here, so the Gurus can respond too?
https://answers.sap.com/tags/907900296036854683333078008146613
Thanks,
Mahboob Mohammed
Thanks for great suggestion!!!
On the day I posted here., I posted already
https://answers.sap.com/questions/218683/webi-report-cascading-prompts-in-lovs-with-multipl.html
still no answers., I have done with static level code using case statement in universe level., Dynamically how it’s possible. Data might be grow depends on business requirements. Anyways waiting for suggestions from Guru’s ?
Its really useful one!!
Glad you liked it and/or may be using it!
Great! I’m using already this from previous version but I noticed that in webi 4.2 when you run the query is working well but if you try to retrive the list of value is not working.
I’m getting this error no value display
Any ideas why?
Â
Â
Hi,
Thanks, and glad you found it helpful!
Can you make sure that you've LOVs Associated to that dimension?
https://www.youtube.com/watch?v=S7_RD_eWmg4
Thanks,
Mahboob Mohammed