BPC provides several MDX functions that you can use in your dimension rule formulas. You can use some of these MDX functions in advance rule formulas as well.
A majority of the MDX functions define industry-standard financial ratios. You can use ratios to evaluate the performance of your business and identify potential problems. Ratios expose factors such as the earning power, solvency, efficiency, and debt load of your business.
This section also discusses user-defined functions. The term 'user-defined functions' comes from Microsoft Analysis Services. BPC supplies user-defined functions, but you can create your own user-defined
functions as well.
Common MDX functions
The following table describes some of the more common MDX functions. For additional MDX functions, see the Analysis Services Manager Help and look for help on MDX expressions and the function list.
MDX Function Description
Ancestor Returns the ancestor of a member at a specified level.
ClosingPeriod Returns the last sibling among the descendants of a member at a level.
Cousin Returns the member with the same relative position under a member as the member specified.
Current Member Returns the current member along a dimension during an iteration. Default Member Returns the default member of a dimension.
FirstChild Returns the first child of a member.
FirstSibling Returns the first child of the parent of a member.
IsEmpty Determines if an expression evaluates to the empty cell value.
Item Returns a member from a tuple.
Lag Returns a member prior to the specified member along the member's dimension.
LastChild Returns the last child of a member.
LastSibling Returns the last child of the parent of a member.
Lead Returns a member further along the specified member's dimension. Members Returns the member whose name is specified by a string
expression.
NextMember Returns the next member in the level that contains a specified member.
OpeningPeriod Returns the first sibling among the descendants of a member at a level.
ParallelPeriod Returns a member from a prior period in the same relative position as a specified member.
MDX Function Description
Parent Returns the parent of a member.
PrevMember Returns the previous member in the level that contains a specified member.
Aggregate Returns a calculated value using the appropriate aggregate function, based on the context of the function.
Avg Returns the average value of a numeric expression evaluated over a set.
CoalesceEmpty Coalesces an empty cell value to a number.
Correlation Returns the correlation of two series evaluated over a set. Count Returns the number of tuples in a set, empty cells included unless
the optional EXCLUDEEMPTY flag is used.
Covariance Returns the covariance of two series evaluated over a set (biased). CovarianceN Returns the covariance of two series evaluated over a set
(unbiased).
IIf Returns one of two values determined by a logical test.
LinRegIntercept Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.
LinRegPoint Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.
LinRegR2 Calculates the linear regression of a set and returns R2 (the coefficient of determination).
LinRegSlope Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b.
LinRegVariance Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.
Max Returns the maximum value of a numeric expression evaluated over a set.
Median Returns the median value of a numeric expression evaluated over a set.
Min Returns the minimum value of a numeric expression evaluated over a set.
Stdev Returns the standard deviation of a numeric expression evaluated over a set (unbiased).
StdevP Returns the standard deviation of a numeric expression evaluated over a set (biased).
Sum Returns the sum of a numeric expression evaluated over a set.
MDX formula syntax
BPC dimension and advanced rule formulas are based on the multi-dimensional expression language used by Microsoft SQL Analysis Services, called MDX. For details on the syntax and usage of this powerful
Advanced rules formulas can also be based on Structured Query Language (SQL). Please consult the appropriate reference guides for more information and examples of using SQL.
Syntax basics
•
The calculated member, which is the member into which the calculated values are written in the application, must be preceded with a pound (#) sign everywhere in the formula.•
When the dimension name is omitted from the left side of the formula, the ACCOUNT dimension is assumed. If it is omitted from the right side of the formula the dimension is automatically derived by Analysis Services.•
The right side expression is not enclosed in single quotes.•
If the calculated member is used on the right side of the formula, it must be enclosed in square brackets [].•
Remarks are denoted by double forward slashes (//). Any text to the right of double slashes is ignored during validation. This way, you can put remarks in the rules file to improve readability.•
Brackets are required by SQL when passing parameters that contain delimiters like "." or other special characters, to let the rules engine better understand where the parameter begins and where it ends. This is also true when a parameter contains a set of comma-delimited members as in the following:[2001.JAN, 2001.FEB]
Consider always using brackets around parameters to be sure that they are parsed correctly.
MDX syntax exceptions
There are two exceptions to MDX syntax with BPC:
•
Replace the keyword AS with an equal (=) sign•
Do not use single quotes around expressions For example:[ACCOUNT].[#GROSSSALES] = -[ACCOUNT].[UNITS]*[ACCOUNT].[INPUTPRICE] [ACCOUNT].[#COST] = -[ACCOUNT].[#GROSSSALES]*80/100
Note that this structure:
{member} = {expression}[, solve_order = n]
...is the normal syntax for calculated members required by MDX queries. The only exceptions are the equal sign ("=") in place of the “AS” keyword and the lack of single quotes around the expression. The above formula can also be written in a simpler format where the dimension name is omitted, for example: #GROSSSALES = -UNITS*INPUTPRICE
#COST = -[#GROSSSALES]*80/100 Building dimension rule formulas
BPC gives you the ability to define very powerful rule formulas for calculating dimension members. BPC uses the MDX (multi-dimensional expression) language used by Microsoft Analysis Services. You can use any Microsoft MDX functions to create dimension rule formulas. You enter formulas using the MDX syntax, in the Formula column in a member sheet.
For more information about Microsoft's MDX reference, see MDX Reference (http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/olapdmad/agmdxbasics_04qg.asp?frame=true) at the Microsoft Web site. For a complete list of Microsoft MDX functions, see the MDX function list
(http://msdn.microsoft.com/library/default.asp?url=/library/en- us/olapdmad/agmdxfunctions_9elw.asp).
Dimension files
You define your dimension rules formulas in dimension Excel files that you can access from the eAdmin > Manage Dimensions dialog box. When you validate these files, the dimension rules formulas are gathered
In order to use formulas in a dimension you must add a property called 'Formula' to the member sheet using the Manage Dimensions task in the Admin module. 'Formula' is a user-defined property, so you must define the length of the field. Enter a field length that is at least equal to the length of your longest formula.
Simple rule formula examples
Following are some examples of simple MDX formulas that can be used in the Formula column for an account.
Task Syntax and example
Adding two accounts Syntax: [dimension].[account1] + [dimension].[account2] Example (PreTax Income): [Account].[Operating Income] + [Account].[OtherExp]
Adding a range of
accounts Syntax: [dimension].[account1]:[dimension].[account2] Example (Total Personnel Exp):
SUM([Account].[Salaries]:[Account].[Commission]) Calculating a
percentage Syntax: [dimension].[account1] / [dimension].[account2] or (to prevent division by zero):
Iif([dimension].[account1] = 0, Null,
[dimension].[account2]/[dimension].[account1])
Example (Gross Margin Pct): Iif([Account].[Revenue] = 0, Null, [Account].[Gross Margin]/[Account].[Revenue])
Multiplying by a
factor Syntax: [dimension].[account1]*[dimension].[account2] Example (Taxes): [Account].[Pretax Income] * -0.35 Calculating a YTD
value Syntax: [dimension].[account1],[Measures].[YTD]
Example (Current Year Net Income): ([Account].[Net Income], [Measures].[YTD])
Advanced rule formula examples
Advanced rule formulas include movement calculations and allocating expenses based on entity type.