A useful feature of building a cube view in Planning Analytics Workspace is the availability of using MDX to control dimension element selections. This functionality can be leveraged to use the selection made in one title element drive what element a separate dimension uses. In this article I have two examples. The first is using element attributes on a title dimension to determine what element to display on a secondary title dimension. The second is using a mapping cube to display specified rows using a mapping from the selected title dimension element.
In the first example, an attribute called “Year” is created on the Version dimension. This attribute is populated with the year that should be chosen for any selected Version. This attribute will be referenced in an MDX statement created in the Year dimension.
In the set editor for the Year dimension, open the MDX editor on the top right.
Because the dimension is being used in a PAW cube view, elements from other dimensions can be used in the MDX that will create our subset. The MDX in the image below converts the Year attribute from the Version that is currently selected in the Version dimension into the Year element that should be displayed with the selected Version. The MDX statement notably uses the STRTOMEMBER function and a reference to the Version attribute cube using CURRENTMEMBER.
Now, when a version is selected, the Year dimension automatically updates to the desired element.
For the second example, a mapping cube is needed to determine what Account elements should be shown when a Company element is selected. To do this, a simple mapping cube was created.
Then, with Company as a title dimension and Account on the rows, open the set editor for the Account dimension to access the MDX editor.
In the MDX statement, the full Account dimension ( TM1SUBSETALL ) is filtered to only the accounts that should be displayed for the selected account. This is done by referencing both the Company and Account current elements using CURRENTMEMBER to determine if the flag in the mapping cube is set to 1.
With this MDX statement applied to the Account subset, the rows of accounts that are displayed update when the selected Company is changed based on their mapping.
This functionality is useful in easing the user experience when there are set relationships between dimensions. In addition, any of the controlling dimensions can be dragged out of the cube view so that the dimensions can be hidden. With Synchronize dimensions turned on, the MDX statement will still work as intended.
Update: If you are receiving a “StrToMember error in value expression” error in the MDX, it may be because there is a reference to an empty value. Every possible return to the CurrentMember lookup must have a valid value. For instance, if one of the versions in the first example did not have its Year attribute populated, it would return an error even if it is not used in the subset in the cube viewer. To remedy this, you either need to ensure all elements have the attribute populated, or use the MDX CoalesceEmpty() function to force a default. For instance I could force the return to be 2021 if the attribute were empty with the following MDX:
StrToMember(“[Year].[Year].[” + CoalesceEmpty(
[}ElementAttributes_Version].([Version].[Version].CurrentMember,[}ElementAttributes_Version].[Year]) , “2021” ) + “]” )
Or even default to a year stored in a Global Variables Cube:
StrToMember(“[Year].[Year].[” + CoalesceEmpty(
[}ElementAttributes_Version].([Version].[Version].CurrentMember,[}ElementAttributes_Version].[Year]) , [Global Variables].([Global Variables].[Current Year],[Global Variables Measure].[String]) ) + “]” )