Creating proper WEBI BEX hierarchy prompts

August 9, 2013 by: David Lai

In this article I’d like to present how to properly create a hierarchical selection prompt in WEBI.

The most natural way to create WEBI selection prompts is to create the selection prompt directly on WEBI. Unfortunately whoever designed the WEBI hierarchical selection prompt did not put proper thought into it.

Let’s first go through the incorrect way of creating a WEBI hierarchy prompt and see what happens

Step 1

Create a simple BEX query that has a hierarchy characteristic and key figure

Step 2

Open the query in WEBI and drag the appropriate objects; and turn the prompt selection on

webi prompt query
image-743

Step 3

Run the query and select a node.  In our example we select “Expense”.

webi prompt runtime
image-744

Step 4

For our results most people would think that “Expense” plus all its descendants would show up.  However we only see the “Expense” node by itself.

webi prompt output1
image-745
Step 5

The only way to solve this is to select each and every node as shown on the selection below.

But just imagine if we had 3 more hierarchy levels and a lot more nodes.  The user would take forever to make his/her selection!!

webi prompt runtime 2
image-746

From several forum postings it seemed like there was no solution, but fortunately there is a way to solve this! :)

Correct steps to create WEBI hierarchy prompt

Step 1

On the same BEX query we will need to create a BEX prompt on the hierarchy characteristic by dragging the characteristic onto the “Characteristic Restrictions” column and then filtering on a “Hierarchy Node Variable”.
In our example we create a optional multiple-selection prompt.

bex variable prompt
image-747

Step 2

Open the query in WEBI and drag the appropriate objects.  Notice that you won’t be able to turn on the prompt selection option for the hierarchy object.

Step 3

Execute the query and the BEX prompt will come up allowing you to make your selection.  Go ahead and select “Expense” again.

Step 4

Hmm wait a minute.. there’s still only 1 node displaying!

Step 5

Here’s the trick.  Now go back to the query, click on the hierarchy characteristic and click on the “Hierarchy” tab.

hierarchy tab
image-748

Step 6

We can see that the default expand to level is 0.  This means that when we run our query on WEBI, the results will expand to 0 levels and that is why we didn’t see the children nodes of the “Expense” node.

hierarchy default 0
image-749
Step 7

So let’s change the expand to level so that it is the level of the farthest descendent.  In our example our descendants go up to level 6 so we will change the “expand to level” to level 6

hierarchy expand to 6
image-750
Step 8

Now let’s re-execute our query and we will see that our result table shows “Expense” including all of its descendants

webi prompt output2
image-751

Comments

7 Responses to “Creating proper WEBI BEX hierarchy prompts”
  1. Vamsi says:

    My questions are :

    1. If you select all 6 levels in Step 2, would not it show it in data in WebI in Step 4 ?

    2. If you change the BEx setting to display deepest level, would not hurt performance with every report even though user is not interested in hierarchy as it is optional prompt and best practice is to have minimum number of super-BEx queries when BOBJ is front-end for BW ?

    Vamsi

    • David Lai says:

      Hi Vamsi,
      Here’s some answers to your questions

      1. In step 2 we are checking “Enable Parameter” (Prompt), so we are not able to select which level we want to show data up to. When you are making selections afterwards, it will only show whatever nodes you select. Thus if I just select Expense, it will only show the expense node which is not what people usually want.

      2. Setting BEX setting to the deepest level wouldn’t hurt performance, because the user has the option to select deeper down if they want or at a higher level if they want. For example, if users want to select 3 levels after Expense they can do so. If users want to see everything from Expense downwards they can also do so.
      This is much better than having to manually select hundreds of nodes.

      Please let me know what you think.

      Thanks!
      David

  2. Shardul says:

    Hi David

    I followed all the steps you described.

    It did work for me.

    But my requirement is slightly different. I don’t want to put it as a Global Prompt Filter while refreshing the query.
    Instead I want to define a input control of type ‘Tree List’ over the ‘Hierarchy Characteristic’ and achieve the same functionality within the report.

    So for example if I am checking/selecting a parent node in tree list (assuming that I am filtering a Vertical table in report having the same hierarchy characteristic), then in the vertical table I want to get the parent node along with the respective child node(s) in it.

    How can I make it possible?
    Please help me by suggest me a solution or a work around at earliest. Hope I am not expecting too much.

    (By the way I am amazed to see your posts… Fantastic… Mind Blowing… Keep it up)

    Thanks in Advance
    Shardul

    • David Lai says:

      Hi Shardul,
      Yes you can.
      If you create an input control on the hierarchy object. You’ll be able to right click on your selection during run time and do stuff such as “select all descendents”

      Hope that helps!

  3. Janice says:

    Hi David,

    AWESOME!

    I’m having this problem and luckily I found your blog. I followed the steps and it really works!
    Thank you so much! Keep on writing entries like this.

    :) :) :)

  4. Michael says:

    Hi David,

    I actually do not want the hierarchy to appear in Webi at all. The infoprovider was built without a hierarchy in HANA running on BW. However for some reason when I access the HANA view from Webi every field in what is expected to be a flat table shows as a hierarchy.

    This is causing query performance issues when we combine the size of the table and the hierarchy feature.

    Should we be using a Bex layer between the objects and Webi?
    There is no entry on the Hierarchy tab in HANA Studio, so this is puzzling.
    Is there something additional to do in HANA modeler or BW to remove the hierarchy.

    Thanks,
    Mike

  5. Michael says:

    I discovered the Webi connection to HANA must be type “Relational” not “OLAP”

Leave a Reply


seven − 4 =