One of the most useful features of TM1 is the ability for users to create custom rollups, by way of User Defined Consolidation (UDC). Unfortunately, this feature is only available in Perspectives and Architect. That is, until now. Introducing DefineCalc.
DefineCalc is a function in IBM Planning Analytics for Microsoft Excel (PAfE), that allows users to register calculation definitions on the TM1 server.
Essentially users can create synthetic members using MDX expressions, and reference them in their reports. Therefore, a UDC can be created in PAfE using DefineCalc. Note that DefineCalc is a User Defined Calculation (UDC too, but different).
Syntax of DefineCalc
=DefineCalc(string sDatasource, string sServerName, string sCalcMun, string sExpression, [Optional] bool bOutputMun, [Optional] bool bDisableScramble)In simple terms:
=DefineCalc(CubeName (optional), PA server (optional), MUN (Member Unique Name) (required), MDX expression (required), Output name or value (optional), Scramble Member name (optional))Example
Let’s say I wanted to create a subtotal of the Southern, Western, and New Zealand regions of Nebula Fit. Using DefineCalc in PAfE, I can register this custom consolidation as follows:
=DefineCalc( "*", "*", "[Branch].[South West NZ UDC]", "[Branch].[200]+[Branch].[300]+[Branch].[400]")
The “*” means that the calculation does not apply to a specific cube or server, but rather globally for the Branch dimension.
Notice how name of the MUN is displayed, which can then be referenced with a DBRW formula to retrieve values.
Another way of achieving a server-side consolidation is by way of the AGGREGATE function. This will perform an aggregation of a subset (“South West NZ” in this case). Here’s an example:
=DefineCalc( "*","*", "[Branch].[South West NZ Subset]", "AGGREGATE(TM1SubsetToSet([Branch],'South West NZ'))")
Who Can Use or Create DefineCalc?
The good news here is that all users can utilise a MUN, even if they didn’t create it. More good news is that the MUN isn’t limited to the same workbook, as it is stored on the server!
The screenshot below indicates a read only user, referencing the South West NZ UDC synthetic member, and returning the same result:
Not only can an end user with limited READ access reference a synthetic member created by another user, they can create their own DefineCalc UDC’s! Below example shows MUN “Total Company ex WA” created by a read-only user, returning values:
Why DefineCalc Matters
DefineCalc restores the UDC capability that users once had in Perspectives, and by harnessing the power of MDX calculations, users are no longer limited to pre-defined consolidations and calculations.
Note: you can only have one calculation per MUN (Member Unique Name). As with rules, there is an order of precedence for your calculations relative to the data source and server name (more on that in the documentation).
To clear a calculation from the server, you can set an empty expression for your MUN and recalculate.
Want to Know More?
We’re passionate about TM1 and ready to help. Please reach out if you would like to discuss DefineCalc (or just anything TM1) using the form below.
