Skip to Content
Actions

Cascading Prompts in Webi, similar to Crystal Reports (NOT Cascading LOVs)

September 15, 2016 | 595 Views |
 bi41cascading parameterscascading promptscrystal reportsidtinformation designinformation design tool
Follow

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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. 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.

    (0) 
    1. Mahboob Mohammed 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

      (0) 
  4. 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

    (0) 

Leave a Reply