Using MDX Formulas

An MDX formula must always be an MDX numeric value expression. In MDX, a numeric value expression is any combination of functions, operators, and member names that does one of the following actions:

A numeric value expression is different from a set expression. A set expression is used on query axes and describes members and member combinations. A numeric value expression specifies a value.

A numeric value expression is used in queries to build calculated members, which are logical members created for analytical purposes in the WITH section of the query, but which do not exist in the outline.

The following query defines a calculated member and uses a numeric value expression to provide a value for it:

WITH MEMBER
 [Measures].[Prod Count]
AS
 'Count (
    Crossjoin (
     {[Units]},
     {[Products].children}
    )
  )', SOLVE_ORDER=1
SELECT
 {[Geography].children}
ON COLUMNS,
 {
  Crossjoin (
     {[Units]},
     {[Products].children}
    ),
   ([Measures].[Prod Count], [Products])
 }
ON ROWS
FROM
 ASOsamp.Sample

In the sample query, the WITH clause defines a calculated member, Product Count, in the Measures dimension, as follows:

WITH MEMBER
 [Measures].[Prod Count]

The numeric value expression follows the WITH clause and is enclosed in single quotation marks. In the sample query, the numeric value expression is specified as follows:

'Count ( 
    Crossjoin (
     {[Units]},
     {[Products].children}
    )
  )'

The SOLVE_ORDER property specifies the order in which members and formulas are evaluated. See Calculation Order.

Note:

For an explanation of the syntax rules used to build the numeric value expression in the example, see the documentation in the Oracle Essbase Technical Reference for the Count, CrossJoin, and Children functions.

A numeric value expression also can be used as an MDX formula to calculate the value of an existing outline member.

Therefore, rather than creating the example query, you can create an outline member on the Measures dimension called Prod Count that is calculated in the outline in the same way that the hypothetical Prod Count was calculated in the sample query.

  To create a calculated member with a formula:

  1. Create a member.

  2. Attach an MDX formula to the member.

    Assuming that you created the example Prod Count member, you would use the following formula, which is the equivalent of the numeric value expression used to create the calculated member in the example query:

    Count(Crossjoin ( {[Units]}, {[Products].children}))
  3. Verify the formula by verifying the outline.

    When you retrieve data from the aggregate storage database, the formula is used to calculate the member value.

    You can use substitution variables within formulas. For example, you could define a substitution variable named “EstimatedPercent” and provide different percentages as substitution variable values. See Using Substitution Variables.

Before applying formulas to members in the outline, you can write MDX queries that contain calculated members. When you can write an MDX query that returns the calculated member results that you want, you are ready to apply the logic of the numeric value expression to an outline member and validate and test the expression. See Writing MDX Queries. For syntax information about MDX, see the Oracle Essbase Technical Reference.