DefineCalc

User-defined calculations (UDC) are supported through the DefineCalc function. You can use this function to register or unregister a calculation definition in IBM Planning Analytics for Microsoft Excel.

Note: The UDC integration is offered only with core Custom Reports. UDC integration is not available for accessory functions in Custom Reports that are not created automatically by the built in content generators.

Users can use DefineCalc() to create synthetic members that can be incorporated into their reporting. MDX lets you create new named synthetic members that correspond to MDX expressions. With DefineCalc(), you can instruct Planning Analytics for Excel on how to rewrite MDX expressions that are sent to the data source when your report references the synthetic members.

Syntax

=DefineCalc(string sDatasource, string sServerName, string sCalcMun, string sExpression, [Optional] bool bOutputMun, [Optional] bool bDisableScramble)

Argument	Description
`sDatasource`	Name of the datasource subject to injection. Use * if you want to apply the new calculation to all data sources.
`sServerName`	Name of the server subject to injection. Use * if you want to apply the new calculation to all databases.
`sCalcMun`	Member unique name (MUN) of the new expression that you want to register. Use valid MDX notation for the virtual member name.
`sExpression`	The expression that registers as a runtime calculation injection. The expression is injected directly into MDX queries and must be a valid MDX. If the `sExpression` is null or empty, the `sCalcMun` definition is deleted.
`bOutputMun`	By default, `bOutputMun` is set to false and returns the member name. If `bOutputMun` is set to true, it returns the value of `sCalcMun`.
`bDisableScramble`	By default, `bDisableScramble` is set to `false`, which scrambles the MUN name in the MDX. This avoids any potential collision if the MUN name collides with another existing name. When `bDisableScramble` is set to `true`, the calculation disables anti-collision MUN scrambling. Do not disable this parameter if you are injecting subset aggregates as a calculation of the same name. Important: When `bDisableScramble` is not set to `true`, MUN matching is white space sensitive. If a calculation refers to another calculation as part of its definition, disable scramble (set `bDisableScramble` to `true`) for the calculation that is being referenced.

The definitions are runtime scoped, relative to unique combinations of datasource, server name, and calculation MUN. You cannot have multiple active definitions for the same name and server-connection pairing; the most recently calculated one applies except when a more precise definition is available. This avoids having multiple DefineCalc() calls for the same sCalcMun member name in your session.

The order of precedence for definitions is as follows:

When you set an empty sExpression, it removes that particular calculation from the system. The normal behavior of Alt + F9 (or an equivalent macro) deletes the existing registered DefineCalc() definitions and forces a global re-evaluation of the workbook. This can be useful to verify that your final workbook’s definitions are accurate as definitions can accumulate during a session as you edit DefineCalc() formulas.

Note: Any syntax error in a DefineCalc MUN or sExpression can cause cascading problems with other MDX calculations and result in errors that return bad values for the query.

Examples

In this example, DefineCalc() defines a calculation that is called foo on the plan_lines dimension default hierarchy, for the Planning Sample server on any data source. The expression for the UDC is the sum of line 1 and line 2.

=DefineCalc("*","Planning Sample","[plan_lines].[foo]","[plan_lines].[line 1] + [plan_lines].[line 2]")

In the following example, DefineCalc defines a calculation that is called 'MySubset' on the plan_lines dimension default hierarchy, for the Planning Sample server on any data source, and the sExpression is the aggregate of the subset MySubset on the plan_lines dimension.

=DefineCalc("*","Planning Sample","[plan_lines].[MySubset]","AGGREGATE(TM1SubsetToSet([plan_lines],'MySubset'))")

This would create the comparable auto subset UDC outcome from a Perspectives report.