I am looking at building a tree map chart. In these chart data is grouped into sections. I’m looking at the best way to return data in a hierarchy so I can group accounts. Is it possible to use MDX to return data with a dimension hierarchy? I can then use this returned data to build the chart data object.
For example the parent level might be Cash cost. This as several children like labor and fuel. These children also have children, which are the leaf nodes or actual accounts.
My original though was to return the child elements using $tm1Ui.dimensionElements, and then to loop through these elements and return their children with the $tm1Ui.cubeExecuteMdx and some MDX. I can do both of these separately, however not one inside a loop of the other.
I think the issue here is that when I loop through the first level of elements and issue the MDX command, the loop does not wait for the result. Something to do with the promise.
What I want to know is, should I continue to head down this best path, or should I be looking at getting the dimension hierarchy and data with another method.
@plim I did look at this option and it does return the elements and their levels. However I can’t see a parent - child relationship. I know that everything on level 1 rolls up to the one element on level 2, but it does not tell me what level 1 element the level 0 elements roll up to.
The left is my hierarchy. From the right, through the dimesionElements function with showHierarchy = true, it returned element “10” or the Assets as the top element. Under Assets, there are three more. I just expanded PPE, for which you can see that it has 6 accounts under it (check out the 6 elements under Components). I had just expanded the Construction in Progress account.
From the returned JSON data, the Construction in Progress (Level 0) is part of the Components belonging to PPE.
I had just collapsed a few of the other properties just to make have a not so big screenshot. But the JSON data on the right, represents the hierarchy on the left.
Been looking at this with Ben Siu. The issue seems to around the $tm1Ui.dimensionElements service. The last two attributes of this server are showHierarchy and Elementsonly. Both are boolean. I can’t see to work out what Canvas is expecting as a boolean. I have tried ‘Yes’, ‘Y’, ‘true’, true, ‘1’,1,‘0’,0 and ‘’. Would be great to include quick examples in the documentation.
The only thing that seems to work is by using ‘’ and leaving them blank. Anything else will not return the children. Examples are below.
This is my subset
This is my command that works. $tm1Ui.dimensionElements('Wesfarmers_prod', 'account_tb', '' , 'Mine cash costs accounts', '', '', '', '', '')
It returns the below. You can see that object 1 has 29 components.
How if I change the attributes to anything, true and true in this example, I lose the components. $tm1Ui.dimensionElements('Wesfarmers_prod', 'account_tb', '' , 'Mine cash costs accounts', '', '', '', true, true)
As you can see below I get no components in object 1.
Test.1 is the name of the subset I have used previously containing the hierarchy. The elementsOnly should be false as what it means is it will not retrieve the Components part if set to true. And of the Components part are not retrieved, the hierarchy will not be constructed.
Also just a note that, the subset should include all the elements that you want it to show. It should include the leaf elements. As the API works by what is present in the subset.
Just checked with Brian and it’s showing up with the components correctly now.
However it seems the elementsOnly parameter is defaulted to true. As when we omit that parameter, the parent and component property is no longer available in the object.
Also, if we set the last 2 parameters, showHierarchy and elementsOnly, as ‘’, the results is quite different again. The subset is returned as a flat structure but when we expand each element, it still shows the immediate components within that consolidated element.
@plim. All sorted. using true for showHierarchy and false for elementsOnly gives me the required result. using ‘’ for showHierarchy and ‘’ for elementsOnly also gives me the required result but in a different object layout. Think I will go with the true, false attributes as using ‘’ may not work in the future releases.
