IBM Planning Analytics for Excel (PAx) version 80 introduced a new function called “DefineCalc.” This function allows a user to create a custom calculation using TM1/Planning Analytics data via the use of MDX.
There are a series of parameters, and the key ones are:
- sDataSource – The connection URL used when adding your connection to Pax. You can enter “*”if you would like the calculation to apply to all data sources.
- sServerName = Your TM1/PA server name. You can enter “*” if you would like the calculation to apply to all servers.
- sCalcMUN = The name for the calculation. This will be referenced in your DBRW formulas.
- sExpression = The MDX expression being used for the calculation (note: if you would like to remove a custom calculation then set this variable to be blank).
One use case for this function is to grab the total value of a subset. In the example below, we have a report that uses a subset called “DefineCalc Example”:
The following DefineCalc formula was used to create a total:
Now, let’s break this apart by each parameter:
- sDataSource – “*”
- This will apply to all data sources (if multiple)
- sServerName = “*”
- This will apply to all TM1/PA servers (if multiple)
- sCalcMUN = “[bpmAccount].[Example 1]”
- This first half of this value defines the bpmAccount dimension
- The second half of this value consists of a unique name within the bpmAccount dimension
- sExpression = “AGGREGATE(TM1SubsetToSet([bpmAccount],’DefineCalc Example’))”
- This is an MDX expression that will give the total of the “DefineCalc Example” subset within the bpmAccount dimension
The result of this formula is populated in the cell as “Example 1.”
We can now reference “Example 1” within the DBRW formula as an element in the bpmAccount dimension.
The =Sum formula of the three data rows matches the DBRW that references the DefineCalc function.
This shows one common use case for the new DefineCalc function, but the use MDX allows for countless other applications!
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: MDX Syntax Explained
IBM Planning Analytics Tips & Tricks: Dynamic Subsets Based on a Cube