![\"\"](\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2022\/06\/CUBE1-min.jpg\")
<\/p>\nIBM Planning Analytics (PA) subsets can be made dynamic by using multi-dimensional expressions (MDX). Doing so allows for automation and will ensure that subsets update if the dimension changes. \u00a0MDX allows for many different use cases such as filtering by attributes, levels or using a wildcard search to display all elements that meet a specific naming pattern.<\/p>\n
For example, below is a cube view that references a dynamic subset of periods that changes based on the version selected in the view. When the Version element selected is \u201cDraft\u201d, 2022 periods are shown because the \u201cYear\u201d attribute for \u201cDraft\u201d is populated to \u201c2022\u201d. The MDX statement on the Periods dimension will look to equal the string value of the \u201cYear\u201d attribute for the version element selected. When a user changes the Version element to \u201cBudget\u201d, periods from 2023 appear.<\/p>\n
<\/p>\n
To link the two dimensions of Versions and Periods in this view, an attribute was created on the Versions dimension called \u201cYear\u201d. Each version has an associated year such as Draft being 2022, Budget being 2023 etc.:<\/p>\n
![\"\"](\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2022\/06\/CUBE2-min.jpg\")
<\/p>\n
In the Periods dimension, we have a rollup of \u201cAll Time\u201d that\u2019s the parent of all years. The leaf elements are months that roll up to quarters which rollup to years. The first screenshot here shows the elements of \u201c2022\u201d without the alias on to give insight into the Periods dimension structure.<\/p>\n
![\"\"](\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2022\/06\/CUBE3-min.jpg\")
<\/p>\n
With the alias on, the 3 leaf elements appear like this:<\/p>\n
![\"\"](\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2022\/06\/CUBE4-min.jpg\")
<\/p>\n
The below MDX tell us to filter the periods dimension for where the current member selected from the Periods dimension is equal to the \u201cYear\u201d attribute of the current member selected from the Version dimension. Please note, the \u201c.CURRENTMEMBER\u201d function is only useful in a subset used to manipulate a cube view because it depends on an element within the cube view to be populated. The MDX below would only return one element, being the year. For example, if \u201cDraft\u201d were chosen in our cube view, we\u2019d see the element \u201c2022\u201d.\u00a0 But if we change our Version dimension selection to \u201cBudget\u201d, our period would update to \u201c2023\u201d.<\/p>\n
![\"\"](\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2022\/06\/CUBE5-min.jpg\")
<\/p>\n
To further develop the MDX, we can wrap a TM1DRILLDOWNMEMBER function around the above statement to expand the \u201c2022\u201d element. When developing MDX, we do a lot of \u201cwrapping\u201d to add complexity because we are nesting set expressions within functions to return new set expressions. Therefore, it\u2019s often helpful to read MDX from the inside out, to see the layers of added expressions. With the TM1DRILLDOWNMEMBER function in place, we now have the year, quarters, and months of \u201c2022\u201d. Wrapping a FILTERBYLEVEL ( <<subset>>, 0) around the subset returned by this MDX expression will return only the leaf-level elements, which are the months of \u201c2022\u201d.<\/p>\n
Lastly, we add a comma after the expression that returns our month and the entire above expression to give us the year. We can use commas in between each final MDX expression to return both expressions\u2019 products within the same subset. Our final dynamic subset is the 12 months of a given year followed by the year itself.<\/p>\n
![\"\"](\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2022\/06\/CUBE6-min.jpg\")
<\/p>\n
We now have a cube view that automatically changes periods based on version selected. This trick can be powerful to develop a user-friendly User Interface (UI). From a user standpoint, this will save extra clicks and adjustments of the cube view but still allows the user the ability to adjust the periods dimension without depending on version if needed. Also, the dynamically changing cube view can help create a more robust UI in general. Now, instead of having separate UIs for Draft and Budget planning, we can utilize one UI which is ideal for maintenance and management.<\/p>\n