Using WITH to Create Calculated Members

Using WITH to Create Calculated Members

Similar to the way it is used in named sets, the WITH keyword in Multidimensional Expressions (MDX) is used to describe calculated members.

The following syntax is used to add the WITH keyword to the MDX SELECT statement:

[WITH <formula_specification>]
      [, <formula_specification>]
SELECT [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

The <formula_specification> value for calculated members is further broken out in the following syntax definition:

<formula_specification> ::= MEMBER <member_name> 
                            AS '<value_expression>'
                            [,SOLVE_ORDER = <unsigned integer>]
                            [,<cell_property>=<value_expression>...]

The <member_name> value is the fully qualified name of the calculated member, including the dimension or level to which the calculated member is associated, and the <value_expression> value, after it has been evaluated, returns the value of the calculated member. Optionally, the SOLVE_ORDER keyword can be used to specify the solve order of the calculated member; if not used, the solve order of the calculated member is set by default to 0.

The values of intrinsic cell properties for a calculated member can be optionally specified by supplying the name of the cell property in the <cell_property> value and the value of the cell property in the <value_expression> value.

For example, the following MDX query example defines two calculated members. The first calculated member, [Measures].[StoreType], is used to represent the Store Type member property. The second calculated member, [Measures].[ProfitPct], is used to calculate the total profit margin for a given store, and represent it as a formatted percentile value.

WITH
   MEMBER [Measures].[StoreType] AS 
   '[Store].CurrentMember.Properties("Store Type")',
   SOLVE_ORDER = 2
   MEMBER [Measures].[ProfitPct] AS 
   'Val((Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales])',
   SOLVE_ORDER = 1, FORMAT_STRING = 'Percent'
SELECT
   { [Store].[Store Name].Members} ON COLUMNS,
   { [Measures].[Store Sales], [Measures].[Store Cost], [Measures].[StoreType],
   [Measures].[ProfitPct] } ON ROWS
FROM Sales

Calculated members can be created at any point within a hierarchy. For example, the following MDX query example defines a calculated member, created as a child member of the [Beer and Wine] member, to determine whether a given store has at least 100.00 in unit sales for beer and wine:

WITH 
   MEMBER [Product].[Beer and Wine].[BigSeller] AS
  'IIf([Product].[Beer and Wine] > 100, "Yes","No")'
SELECT
   {[Product].[BigSeller]} ON COLUMNS,
   {Store.[Store Name].Members} ON ROWS
FROM Sales

You can also create calculated members that depend not only on existing members in a cube, but also on other calculated members defined in the same MDX expression. The following example illustrates such an MDX expression:

WITH
   MEMBER [Measures].[ProfitPct] AS 
   'Val((Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales])',
   SOLVE_ORDER = 1, FORMAT_STRING = 'Percent'
   MEMBER [Measures].[ProfitValue] AS 
   '[Measures].[Store Sales] * [Measures].[ProfitPct]',
   SOLVE_ORDER = 2, FORMAT_STRING = 'Currency'
SELECT
   { [Store].[Store Name].Members} ON COLUMNS,
   { [Measures].[Store Sales], [Measures].[Store Cost], [Measures].[ProfitValue],
   [Measures].[ProfitPct] } ON ROWS
FROM Sales

The second calculated member, [Measures].[ProfitValue], uses the value created in the first calculated member, [Measures].[ProfitPct], to generate its value.