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:
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 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: |
<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 The calculated member When data for 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]) |
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
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 |