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 a 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, when 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.