For a long time we have been able to use a DBRW inside a SUBNM in a Planning Analytics for Excel report to automatically display the Current Month (defined in a System Control cube) in a month selector in a report. With this method whenever we opened a report, it would automatically refresh onto the defined Current Month and mean we didn’t need to select and refresh. Here is an even cleverer method to allow us to have a period selector automatically update to display the current month and then how to use it in Planning Analytics Workspace and Planning Analytics for Excel. A Dynamic Duo, if you will! There are a few steps involved.
Create a Dynamic Subset that Displays the Current Month as the First Element
Using MDX we want to create a dynamic subset that shows the first element as the current period and then the remaining elements from the current year. We will do this in two pieces of MDX:
- A reference to the reporting period defined in the System cube.
- A list of elements for the current year.
Part 1 – Current Month Defined in the System Cube
This first piece of MDX will pull the parameter Reporting Period intersected with the measure ElementID from the System cube.
FILTER(TM1SUBSETALL([Period]) , [Period].CURRENTMEMBER.NAME = [System].([System Parameter].[Reporting Period],[System Measure].[ElementID]))In our System cube, we have Reporting Period defined as May-2024, so this will return that single element from the Period dimension.
Part 2 – A List of Elements for the Current Year
The second piece of the MDX will add the remaining periods of the year into the set. This gets the N level (leaves) elements from the Period dimension that are descendants of the Reporting Year.
DESCENDANTS(STRTOMEMBER(" [Period].[Period].[" + [System].([System Parameter].[Reporting Year],[System Measure].[ElementID]) + "]") , 99 , LEAVES)In the System cube, we have Reporting Year defined as 2024, so this will return all the leaf level elements underneath that, so Jan-2024, Feb-2024 and so on.
Combine the Two Parts
We then need to combine the two parts above into a single MDX statement with a simple plus sign between them. It’ll look like this (you can see the plus sign in red below:
FILTER(TM1SUBSETALL([Period]) , [Period].CURRENTMEMBER.NAME = [System].([System Parameter].[Reporting Period],[System Measure].[ElementID])) + DESCENDANTS(STRTOMEMBER(" [Period].[Period].[" + [System].([System Parameter].[Reporting Year],[System Measure].[ElementID]) + "]") , 99 , LEAVES)Resulting Dynamic Set
This will result in a set that looks like this. Note that the current period is set as the very first period and is not repeated in the list below. This means that the current period will be displayed as the first element in a picklist when the set is chosen. Then, as we change to June or July, this will dynamically update and always have the current period at the top of the list. Then all you need to do is save it and you can use it wherever you like – Planning Analytics Workspace or Planning Analytics for Excel included. A bit clever, eh!
Use the Dynamic Subset to Display the Current Month in PAW and PAfE
Then, using this fab little dynamic subset to display the current month as the first element in a pick list in Planning Analytics Workspace or Planning Analytics for Excel is super simple.
Dynamic Subset in Planning Analytics Workspace
To use this new subset in a Planning Analytics Workspace selector, just edit your PAW book, edit the set on the selector and choose the new subset.
Dynamic Subset in Planning Analytics for Excel
To use this new subset in a Planning Analytics Excel select your SUBNM formula and edit the set so it is referring to that same new dynamic set.
Do You Need Help Making Your PAW or PAfE Smarter?
If you need any help with making your Planning Analytics Workspace or Planning Analytics for Excel environment smarter, like, for example, automating reports so they always display the current period automatically as the first element, give us a yell. We’d love to help you.
