DeepSee MDX Reference
WITH Clause
|
|
Defines one or more calculated members, named sets, or parameters for use in the SELECT statement.
WITH with_details1 with_details2 ... select_statement
-
-
MEMBER calc_mem_definition
%PARM named_parameter_definition
You can mix these subclauses in a single WITH clause.
Tip:
Notice that there is no comma to separate the WITH subclauses from each other. Nor is there a comma between the WITH clause and the SELECT statement.
The following sections provide the details for the MEMBER, SET, and %PARM subclauses.
In a WITH clause, MEMBER defines a calculated member for use in the query. The MEMBER subclause has the following syntax:
MEMBER calc_mem_details, FORMAT_STRING='format_details', SOLVE_ORDER=integer
cube_name.[dimension_name].[new_member_name] AS 'value_expression'
-
-
-
new_member_name is the name of a member; the member may or may not be already defined in the cube. If it is, the definition given here takes precedence.
-
value_expression is an MDX expression that defines the calculated member, typically in terms of references to other members.
MEASURES.[test score]/MEASURES.[%COUNT]
In any context where you use this calculated member, DeepSee first evaluates the
Test Score and
%COUNT measures in that context and then performs the division.
%OR({colord.red,colord.blue,colord.yellow})
This new member refers to all the records of the fact table that correspond to the
red,
yellow, or
blue members of the
colord dimension.
-
-
SOLVE_ORDER=integer is an optional clause that specifies the relative order in which to evaluate this calculated member. This clause is relevant only if the query contains calculated members on both axes. See
SOLVE_ORDER Clause.
The first example shows a calculated member defined within the WITH clause:
WITH MEMBER MEASURES.avgage AS 'MEASURES.age/MEASURES.%COUNT'
SELECT MEASURES.avgage ON 0, diagd.MEMBERS ON 1 FROM patients
1 None 33.24
2 asthma 34.79
3 CHD 67.49
4 diabetes 57.24
5 osteoporosis 79.46
DeepSee first evaluates the
Age and
%COUNT measures and then performs the division for the
avgage measure.
In a WITH clause, SET defines a named set for use in the query. The SET subclause has the following syntax:
SET set_name AS 'set_expression'
-
set_name is an unquoted string that names the set.
-
The following example shows a named set defined within the WITH clause:
WITH SET testset AS '{homed.city.members}' SELECT MEASURES.[%COUNT] ON 0, testset ON 1 FROM patients
Patient Count
1 Cedar Falls 1,045
2 Centerville 1,069
3 Cypress 1,150
4 Elm Heights 1,104
5 Juniper 1,155
6 Magnolia 1,111
7 Pine 1,138
8 Redwood 1,111
9 Spruce 1,117
In a WITH clause, %PARM defines a named parameter for use in the query. The %PARM subclause has the following syntax:
%PARM parameter_name AS 'value:default_value'
%PARM parameter_name AS 'value:default_value,caption:label'
-
-
-
label is the caption to use when prompting for a value of this parameter.
When you run a query within the MDX shell, the shell prompts you for values of any named parameters.
Then, to refer to the parameter within the query itself, use
@parameter_name
>>WITH %PARM c as 'value:Pine' select homed.[city].@c ON 0 FROM patients
Please supply parameter value(s) for this query:
C [Pine]:
Pine
1,073
---------------------------------------------------------------------------
Elapsed time: 2.136337s
>>WITH %PARM c as 'value:Pine' select homed.[city].@c ON 0 FROM patients
Please supply parameter value(s) for this query:
C [Pine]:Magnolia
Magnolia
1,113
---------------------------------------------------------------------------
Elapsed time: 2.627897s
>>WITH %PARM c as 'value:Pine,caption:city' select homed.[city].@c ON 0 FROM patients
Please supply parameter value(s) for this query:
city [Pine]:
Pine
1,073
---------------------------------------------------------------------------
Elapsed time: 2.235228s
>>WITH %PARM c AS 'value:5,caption:count' SELECT TOPCOUNT(birthd.decade.MEMBERS, @c) ON 1 FROM patients
Please supply parameter value(s) for this query:
count [5]:3
1 1970s 1,593
2 1960s 1,505
3 2000s 1,442
---------------------------------------------------------------------------
Elapsed time: 1.207581s
The WITH clause defines elements that are available only during the query that defines them.
To define calculated members and named sets for use during the entire session, use the following statements: