Skip to Content
Author's profile photo Mahboob Mohammed

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.

Snap 01.png

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:

Snap 02.png

Assign this new City LOV to the City dimension

Snap 03.png

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

Snap 04.png

Assign this new Store Name LOV to Store name dimension

Snap 05.png

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

Snap 06.png

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.

Snap 07.png

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:

Snap 08.png

Snap 09.png

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)

Snap 10.png

And then, we’ll see the Cities in Texas as shown below.

Snap 11.png

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.

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Amit Kumar
      Amit Kumar

      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.

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      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

      Author's profile photo Ray Khan
      Ray Khan

      Good stuff Mahboob! Keep posting!

      Ray Khan

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks Ray Khan!

      Author's profile photo Former Member
      Former Member

      Very nice post!

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Glad you liked it Mona!

      Author's profile photo Gopinath Thangaraj
      Gopinath Thangaraj

      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

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      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

      Author's profile photo Gopinath Thangaraj
      Gopinath Thangaraj

       

      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 ?

      Author's profile photo Krishnaprasad MT
      Krishnaprasad MT

      Its really useful one!!

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Glad you liked it and/or may be using it!

      Author's profile photo Ercole Lugari
      Ercole Lugari

      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?

       

       

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      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