Cascade Filter for Hierarchy dimension

[Former Member]

Hi experts, I try to develop a design Studio Dashboard with BW DS. I would like to create a cascade filter for the same dimension but in different levels from hierarchy. First filter would have upper level node of the dimension that will filter same dimension but in lower level values. First selector has manually added Items corresponding the Hierarchy nodes from Level 2. I didn't find how to get specific level of dimension node with function getMemberList(). I created a DS with Level 2 of hierarchy but when I get the values with DS.getMemberList("ZREP", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, "ALL")) I get all final values from last level node. There is a way to get specific values from hierarchy level with getMemberList() ? Once I get all list values from a level on REP_SELECTOR on click on the selector has the following script: DS_1.setFilter("ZREP", REP_SELECTOR.getSelectedValue()); SHOP_SELECTOR.setItems(DS_1.getMemberList("ZREP", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, "ALL")); Unfortunately shop_selector is showing all values from ZREP without any previous filter. Do you know what is wrong on it? Thank you very much

[MustafaBensan · Accepted solution]

Hi Oscar,

After some preliminary testing I have an idea for an alternative approach than the one I suggested above (and hopefully on that is a little more straightforward than using a hierarchy node variable).  I will get back to you on this once I have an example set up.  In the meantime, it would be helpful if you could answer the following questions to clarify your requirement:

1)  Are you using a typical BW hierarchy, where the nodes are just text items with the leaves represented by the "ZREP" characteristic, or are all of the different hierarchy nodes themselves also a "ZREP" characteristic?  Could you perhaps share a screenshot of a sample of your hierarchy structure to give me a better idea?

2)  Can you provide a browser screenshot of your Design Studio application showing the list selectors for the hierarchy levels and the other components that a selection will drive?

Thanks,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

about the hierarchy I don't know exactly what you mean with a typical BW hierarchy I show you an image hoping with the icons and keys it will be enough to have an idea about.

And the browser example of the filters selectors:

Thinking about the approach above, Could I setFilter for 2 dimensions? I mean If I would need to get less data to be returned, like filtering  with the hierarchy variable and 0COLMONTH variable, can I script 2 setFilters before getting results from DS?

Thank you very much

[MustafaBensan · Accepted solution]

Hi Oscar,

Thanks for the screenshots.  Your requirement is perfectly clear now.  I will come back to you shortly with a solution example that uses setFilter instead of the hierarchy variable.

Regards,

Mustafa.

[MustafaBensan · Accepted solution]

Hi Oscar,

So let me explain a solution that will hopefully meet your needs.  A couple of comments before we begin.  Since you need to cascade through a hierarchy, for your first list selector it is not possible to use the standard Dropdown Box or List Box components because as you know, these must be populated with the getMemberList() method and this method does not return the hierarchy structure but only the member values (excluding hierarchy nodes).  So my proposed solution relies on using a CrossTab component as a selection list.  The benefit of this approach is that the hierarchy structure is preserved.

The application layout is as follows:

This example assumes that the selection in the first filter list populates (cascades) the second filter list and the second filter list selection filters the chart.  The first filter list selection does not also filter the chart but this functionality can easily be added if necessary.

The steps are as follows:

1.  The hierarchy has been set up as per your example, with a topmost root node and one level of text nodes below that.  The final level is represented by characteristics.  A screenshot is shown below:

2.  Define two global script variables to represent the key value of the root node of the hierarchy as well as the standard member selection value for all members, as shown below:

3.  Add 3 data sources to the application, all based on the same BEx Query, which includes the dimension (characteristic) that has the hierarchy, as shown below:

4.  Add two CrossTab components and one Chart component as shown above.

5.  Activate the hierarchy for the first data source DS_1 and set the default level to 2 as shown below:

6.  Activate the hierarchy for the second data source DS_2 and set the default level to 2 as shown below:

7.  Set the default members of the second data source DS_2 to represent the first level 1 group in the hierarchy as shown below.  This is required to ensure that the second list is populated with a default selection on startup.

7.  Define the initial view of the third data source DS_3 in accordance with the requirements of the chart, as follows:

8.  Assign data source DS_1 to the first CrossTab, assign data source DS_2 to the second CrossTab and assign the third data source DS_3 to the Chart.

9.  Define the script for the On Select event of the first CrossTab as shown below.  The purpose of this script is to populate the second CrossTab based on the selection in the first one to cascade the filter.  Note that a selection of the root node is excluded to prevent duplication of the first level of the hierarchy in the second CrossTab list.

10.  Define the script for the On Select event of the second CrossTab as shown below to filter the chart based on the selection:

And that's it!  Let me know if this gets us closer to what you're looking for.  We can then refine the approach as needed.

Regards,

Mustafa.

[Former Member · Accepted solution]

Uau Mustafa! amazing approach.

I tried and It works perfectly. A pity about dropdown component that not get hierarchy structure.

Thank you very much for your great dedication.

Òscar

[MustafaBensan · Accepted solution]

Hi Oscar,

Good to know this worked for you.  Sometimes we just have to get a little creative .

Regards,

Mustafa.

[Former Member]

I apology for the way the message is written, advanced editor is not working on my browser.

[MustafaBensan]

Hi Oscar,

Unfortunately it is not possible to obtain hierarchy information from the getMemberList() method.  A similar issue is discussed in the post Key of root node by clicking child.

In your scenario though, you might be able to achieve the desired result by using a hierarchy node variable in the BEx Query that feeds your filter.  You could try an approach like this:

1.  Define a hierarchy node variable for dimension ZREP;

2.  Set the default expansion level to 1;

3.  Populate the first selector from the BEx Query passing the topmost node as the hierarchy node variable value;

4.  When a node is selected from the first selector list, use this value to pass to the BEx Query hierarchy node variable;

5.  Populate the second selector list with the new result set;

6.  Repeat steps 4 and 5 for each of the remaining selector lists.

Regards,

Mustafa.

[Former Member]

Hi Mustafa,

thank you very much for you quick response.

I try to do the approach above you gave me and I didn't succeed. The second selector is populating with all final values from the hierarchy.

Can you explain me a bit more about 2nt and 3rd steps?

2. When you said "set the default expansion level" do you mean on DS initialize?

3. How do you populate it with the first top node?

I followed the steps

I created a hierarchy node variable for ZCREP on the Bex Query:

I let the first selector with manually values from the top level hierarchy

And then on select from the filter selector I write the following script:

OPORTUNIDA_REPRE_SELECT show me ZCREP without any filter.

I check it on Analyzer and the hierarchical variable works perfectly.

I get some doubts about DS:

Do I need to activate the hierarchy until the first filter node?
Does it matter hierarchy activation on the DS initialize?

Thank you very much