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.