DeepSee MDX Reference
WITH Clause
[Back] 
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Defines one or more calculated members, named sets, or parameters for use in the SELECT statement.
Syntax and Details
WITH with_details1 with_details2 ... select_statement
Where:
The following sections provide the details for the MEMBER, SET, and %PARM subclauses.
WITH MEMBER
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
Where calc_mem_details is as follows:
cube_name.[dimension_name].[new_member_name] AS 'value_expression'
And:
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.
WITH SET
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'
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
WITH %PARM
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'
Or:
%PARM parameter_name AS 'value:default_value,caption:label'
Then, to refer to the parameter within the query itself, use @parameter_name
For example:
>>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
See Also
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:
For information on defining calculated members and named sets as part of the cube definition (available in all sessions), see Defining DeepSee Models.