MDX With Section

Beginning with the keyword WITH at the very start of a query, you can define a buffer of reusable logic lasting for the length of the query execution. This can save time in lines of code written as well as in execution time.

If varying attributes are enabled, the WITH section can also be used to define perspective for each varying attribute dimension. In case of multiple varying attributes, perspective setting can be defined for each varying attribute dimension separately.

In the WITH section, you can create the following reusable elements:

Calculated members
Named Sets

Syntax

WITH
      SET set_name AS ' set '
      | MEMBER calculated_member_name AS ' <numeric_value_expr> '
      [, <solve_order_specification> ]
      | <perspective_specification>

Table 4-9 MDX WITH Section Elements

Item	Description
set_name	The name of the set that will be defined after the `AS` keyword. Any name can be used; it should be something that helps you remember the nature of the set. For example, a set name could be `Best5Books`, which names a set of the five top-selling paperback titles in December: `WITH SET [Best5Books] AS 'Topcount ( [Paperbacks].members, 5, ([Measures].[Sales], [Scenario].[Actual], [Year].[Dec]) )'`
set	The logic of a set specification; this can be re-used because it is being named. Must be enclosed in single quotation marks. In the example above, the Topcount function defines the entire set.
calculated_member_name	A name for a hypothetical member existing for the duration of query execution. In its definition, you must associate the calculated member with a dimension (as [Max Qtr2 Sales] is associated with the Measures dimension, in the example that follows). For example, the calculated member named `Max Qtr2 Sales` has its value calculated at execution time using the Max function: `WITH MEMBER [Measures].[Max Qtr2 Sales] AS 'Max ( {[Year].[Qtr2]}, [Measures].[Sales] )'` Calculated members do not work with metadata functions such as Children, Descendants, Parent, and Siblings. For example, if there is a calculated member defined as [CM1], you cannot use it in the following way: `[CM1].children`.
<numeric_value_expr>	An expression involving real members in the database outline, compared using mathematical functions. The value resulting from the expression is applied to the calculated member. By using calculated members, you can create and analyze a great many scenarios without the need to modify the database outline.
<solve_order_specification>	Optional. By adding `,SOLVE_ORDER = n` to the end of each calculated member, you can specify the order in which the members are calculated. For example, solve order in the following hypothetical query is indicated in bold: `WITH MEMBER [Product].[mbr1] AS 'calculation', SOLVE_ORDER = 2 MEMBER [Product].[mbr2] AS 'calculation', SOLVE_ORDER = 1 SELECT {[Year].children} on columns, { [Product].[mbr1], [Product].[mbr2] } on rows` See Usage Examples for Solve Order.
<perspective_specification>	`PERSPECTIVE REALITY \| tuple FOR dimension` When a database uses varying attributes, base members associated with the varying attributes are aggregated according to the specified perspective. You can set the perspective to reality (using the REALITY keyword) or to explicit (using an input tuple consisting of level 0 members). Reality-based evaluation and reporting is the default, in which independent members are determined by the current context. When using explicit evaluation and reporting, you specify a tuple of level 0 members from the independent dimension to be used as the context. For an example of a reality-based perspective, see the example for AttributeEx. For an example of an explicit perspective, see the example for WithAttrEx.

Usage Examples for Solve Order

WITH 
MEMBER 
  [Measures].[Profit Percent] 
  AS 'Profit *100 /Sales', SOLVE_ORDER=20  
MEMBER 
  [Year].[FirstFourMonths] 
  AS 'Sum(Jan:Apr)',SOLVE_ORDER=10  
SELECT
  {[Profit], [Sales], [Profit Percent]} 
ON COLUMNS,
  {[Jan], [Feb], [Mar], [Apr], [FirstFourMonths]} 
ON ROWS
FROM Sample.Basic

The calculated member [Profit Percent], defined in the Measures dimension, calculates Profit as a percentage of Sales.

The calculated member [FirstFourMonths], defined in the Year dimension, calculates sum of data for first four months.

When data for ([Profit Percent], [FirstFourMonths]) is evaluated, SOLVE_ORDER specifies the order of evaluation, ensuring that [Profit Percent] is evaluated first, and resulting in a correct value for percentage. If you change the order of evaluation, you will see that the percentage value is not correct. In this example, SOLVE_ORDER specifies that sum should be calculated before percentage.

Tie-Case Example for Solve Order

When evaluating a cell identified by multiple calculated members, the SOLVE_ORDER value is used to determine the order in which the expressions are evaluated. The expression that is used to evaluate the cell is that of the calculated member with the highest SOLVE_ORDER value. In this case, [Profit Percent]'s expression is used to evaluate ([Profit Percent], [FirstFourMonths]). The example above is calculated as:

([Profit Percent], [FirstFourMonths])
    = ([Profit], [FirstFourMonths]) * 100 / ([Sales], [FirstFourMonths])
    = (([Profit], [Jan]) + ([Profit], [Feb]) + ([Profit], [Mar]) + ([Profit], [Apr])) * 100 / 
          (([Sales], [Jan]) + ([Sales], [Feb]) + ([Sales], [Mar]) + ([Sales], [Apr]))

A tie situation is possible because calculated members may have the same SOLVE_ORDER value. The tie is broken based on the position of the dimensions to which the calculated members are attached:

For aggregate storage outlines, the calculated member belonging to the dimension that comes later in the outline is the one that wins in this case.
For block storage database outlines (and for pre-Release 7.1.2 aggregate storage outlines), the solve order property applies to calculated members defined in an MDX query. The calculated member belonging to the dimension that comes earlier in the outline is the one that wins in this case, and its expression is used to evaluate the cell.

Calculated Members

For examples of queries using calculated members, see examples for the following functions: