cancel
Showing results for 
Search instead for 
Did you mean: 

BW Query with Hierarchy - getMembers including Hierarchy Nodes

Former Member
0 Kudos

Dear all, I am looking for a way to read out a hierarchy INCLUDING the hierarchy nodes and sub-nodes.

The standard way like DS_13.getMembers oder DS_13.getMemberList will only bring up the Elements of the Hierarchy, but not the nodes.

I tried using the tree component which shows all elements quite well, but only offers a method for selected items. There is no way to read out the tree component withou manually selecting all items.

My aim is to read out the hierarchy structur and put it into a string or array.

Do you have an idea to do so? Attached you find an example of the hierarchy.

Best regards

Stefan Hüttner

Accepted Solutions (1)

Accepted Solutions (1)

MustafaBensan
Active Contributor
0 Kudos

Hi Stefan,

Unfortunately, it is not yet possible to traverse a hierarchy with standard Design Studio functionality.  However, you should be able to achieve your requirement with the following community SDK component:

Regards,

Mustafa.

Former Member
0 Kudos

Dear Mustafa, thank you.

This is really helpfull, but due to our IT policies, we cannot use SDK components without maintenance agreement in our production environment. There is no commercial support for this SDK and the disclaimer makes it quite clear that bugs may be encountered.

However, thank you for the hint.

Stefan

MustafaBensan
Active Contributor
0 Kudos

Hi Stefan,

At the moment the only way to access the hierarchy is via the SDK.  I recall seeing a related idea in the Idea Place but it was marked as "For future consideration" if I recall

correctly, so I don't expect to see it in the standard functionality anytime soon.

Regards,

Mustafa.

Former Member
0 Kudos

Hi Stefan,

No bugs have been known yet, and we used the same code in a production application at one of our customers.

Note you could just take the code and create you own extension with it. You'll just need to mention the source origin as part of the licence.

This hierarchy member accessor is very easy to create. Anyway, hope the other solutions below works for you.

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Stefan,

I assume you are using connection to BW.

You could also load the data from hierarchy table into a separate DSO/CUBE. In BW enrich the data from the table with the relevant parent/node information. (My source table is /BI0/HCOSTCENTER)

The second step would be to create a query on your new DSO/Cube and add that to your DS application.

Now use the value for 0Costcenter you are getting from your original query to filter your new query.

This way you should be able to get the node/parent information without an SDK.

For me it works fine. I use it to expand one hierarchy according to the value selected in a different hierarchy.

Best regards

Dominik

MustafaBensan
Active Contributor
0 Kudos

Hi Dominik,

That's a very creative and obviously effective approach.  In this context, I'm curious to know how you've implemented the particular scenario of "expand one hierarchy according to the value selected in a different hierarchy" from a UI component perspective?  If you want to drive one hierarchy from another, couldn't you just use one or more Crosstabs with hierarchy enabled on the relevant dimensions?

Regards,

Mustafa.

Former Member
0 Kudos

Hi Mustafa,

I am using different crosstabs (=different BW Queries) with the same hierarchy enabled on the relevant dimensions. The different Crosstabs are on different tabs, so every time I switch the tab I can update the navigational state of the hierarchy of the new tab with the selected value of the first query. This was the only way it was possible for me to expand the queries as desired. (Perhaps I am missing something.)

Problem one: To my knowledge the expand command in DS only works if the relevant node is already visible. So if, in one query you expand to level 5 and in your second query the hierarchy is expanded only to level one or two, I couldn’t find a way to transfer the navigation state of the hierarchy to my second query. The command “Expand level 5” will not work because level 5 is not visible.

Problem two: When you click on expand, we could not find a way to trigger an event with this. So if you click “+” sign, there is no way to copy that behaviour to a second query/crosstab/hierarchy. Cause you can only use the “on select”; but there is no “on Expand”. As well, I think there is no way to get the expanded node and write it into a DS variable.

I admit that my solution is really a little complicated, but I couldn’t find another way (besides SDK's). This was all done with DS 1.4, so it might be that there is a more elegant approach with 1.6.

Best regards Dominik

MustafaBensan
Active Contributor
0 Kudos

Hi Dominik,

Thanks for taking the time to write a very detailed explanation.  This certainly makes the issue perfectly clear. 

The two problems you have raised are still valid in Design Studio 1.6.  However, the following blog documents an interesting way to implement hierarchy navigation in the Scorecard component that might address your second problem related to identifying a node expand/collapse:

Like the Crosstab, the Scorecard only has an On Select event.  The technique described in the blog uses the getSelection() method (also available for Crosstab), to loop through the data and indirectly determine which node has been expanded, within the On Select event.  I suspect however that the On Select event of the Scorecard is probably more granular than that of the Crosstab.

Overall, it sounds to me like your requirement is to drive the hierarchy in the other tabs by the user interaction on the hierarchy in the first tab.  Correct me if I have misunderstood but this leads me to believe that each of the queries in the tabs have common dimensions (which is how you link the hierarchies) but different measures.  If this is the case, would it not make sense to simply combine all the cubes of each of the queries in a single MultiProvider joined by the common dimensions, then create just one query with all the desired measures (key figures) and assign this to a single Crosstab, with an appropriate filter to display the different sets of measures which would correspond to what is currently shown in each tab?  This way you don't have to synchronise multiple hierarchies but can still control which measures are displayed.

Regards,

Mustafa.

Former Member
0 Kudos

Hi Mustafa,

Thanks a lot. The blog sounds really interesting and I'll take a closer look at it. Your input is much appreciated.

Yes, you have understood our requirement and yes in theory it would make sense to combine all data in one query. But unfortunately our application is just to big. We have six or seven crosstabs, each up to ten key figures. This would leave us with a query that has up to 60-70 columns. This would lead to problems in terms of performance. And I think it would be very complicated to maintain. Additionally the tabs are also used to show a wide range of other information/graphs (very different for every tab). So I think this is not an option.

As our application is working fine at the moment, I don't think we are going to remodel it anytime soon.

Regards

Dominik

MustafaBensan
Active Contributor
0 Kudos

Hi Dominik,

Thanks for the further clarification.  I can understand that with such a large and complex application you would prefer to take the "if it ain't broke, don't fix it" approach instead of remodelling

Regards,

Mustafa.

IngoH
Active Contributor
0 Kudos

Hello Stefan,

let me know in case that is still of interest.

we do have a hierarchical filter that provides access to nodes, leaf, level, ....

regards

Ingo Hilgefort, Visual BI