If your IBM Planning Analytics model contains a cube that uses an index or slot dimension, you may encounter a situation where you’ll need to create a template that will allow your users to add data to an unused index.
Below is an example of a staffing cube that has slots for open positions in the Employee dimension. This example is a dynamic report that’s zero suppressed to show only indexes that have value.
If users want to update the first three open positions in the report, then there isn’t an issue. However, if they want to add a new open position, how will they be able to do so since position O-004 isn’t displayed due to zero suppression?
The straightforward answer is to unsuppress zero. But what if your index dimension contains hundreds or thousands of elements? One such example is a CapEx model in which each asset is represented by a different index. Unsuppressing the report could include hundreds or thousands of blank rows.
Since we’re working with a dynamic report, we can use MDX to create a dynamic subset that will limit the number of rows displayed when used in conjunction with unsuppressing zeroes. The MDX formula will then be reference by the TM1RPTROW function. We’ll use the HEAD MDX function which has the following syntax:
HEAD( {set}, Count).
In our example, it can be written as such to return a total of 5 elements:
HEAD(DESCENDANTS([bpmStaff_Employee].[Total Open]),5)
In most index cubes, there will be a Count measure to let you keep track of how many indexes are used. Use this Count measure to dynamically determine the Count parameter of the HEAD MDX function. In our example, our cube has a Headcount measure since we’re in the Staff cube. This will serve as our Count measure.
If we rewrite our MDX to be the one below, it’ll return six total rows: three for the existing open headcount and three extra which we added in the formula. Our report contains the Total element, so it only shows 2 extra indexes at the end.
=”HEAD(DESCENDANTS([bpmStaff_Employee].[Total Open]),”&DBRW(…,”Headcount”)+3&”)”
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Using Charts and Data Series with Dynamic Reports
IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Process Editor Function Help
IBM Planning Analytics Tips & Tricks: Refresh PAW Visualizations Automatically