With Section

The WITH section is for defining referenceable sets or members that can be used multiple times during the life of a query.

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

In the WITH section, you can create the following re-usable elements:

Syntax

WITH
      SET set_name AS ' set '
      | MEMBER calculated_member_name AS ' <numeric_value_expr> '
      [, <solve_order_specification> ]
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 calcualted 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

Usage Example 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. 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.

In the example above, if the SOLVE_ORDER clauses are omitted (so that both have SOLVE_ORDER of 0) or if both are set to 10, there is a tie. The expression for Year.[FirstFourMonths] would be used to evaluate ([Profit Percent], [FirstFourMonths]) because Year comes before Measures in the outline. Hence the calculation would be done as follows:

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

Calculated Members

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

Abs
Avg
BottomPercent
Case
ClosingPeriod
Count
Exp
FirstSibling
IIF
Int
Lag
LastPeriods
Lead
Ln
Max
Min
Mod
NextMember
NonEmptyCount
Ordinal
PrevMember
Remainder
Sum
Todate

Named Sets

For examples of queries using named sets, see examples for the following functions:

BottomPercent
CurrentTuple
Filter (example 3)
Generate
Parent (example 2)

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com