DeepSee MDX Reference
%MDX
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Executes an MDX query outside of the context of the current query and then returns a single result. This function is a DeepSee extension to MDX.
Returned Type
This function returns a number or a string.
Syntax and Details
%MDX(mdx_query,parmName1,parmValue1,parmName2,parmValue2)
Where:
This function executes the given query and returns a single value; if the query returns multiple rows or columns, the function returns only the upper left cell. You use this to include a subquery within another query.
DeepSee provides the special %CONTEXT parameter which you can use within %MDX. For details, see %KPI, which also accepts this parameter.
Important:
Remember to quote the %CONTEXT parameter if you use it.
Example
You use %MDX to obtain a value that you want to include in a query but that would otherwise be affected by the row and column definition of the query. For example, you can use it if you need to access the total record count:
WITH MEMBER A.FRACTION AS 'MEASURES.[%COUNT]/%MDX("SELECT FROM patients")' 
SELECT { MEASURES.[%COUNT], A.FRACTION } ON 0, diagd.MEMBERS ON 1 FROM patients
 
                       Patient Count             FRACTION
1 None                          8,428                 0.84
2 asthma                          712                 0.07
3 CHD                             343                 0.03
4 diabetes                        485                 0.05
5 osteoporosis                    212                 0.02
The following example uses %MDX with a named parameter (City):
SELECT 
%MDX("WITH %PARM City AS 'value:[All Cities]' SELECT FROM HOLEFOODS WHERE Outlet.@City",
"City",Outlet.CurrentMember.Properties("NAME")) ON 0,
Outlet.City.Members on 1 FROM HOLEFOODS
 
                                      NAME
 1 Amsterdam                          1,633
 2 Antwerp                              421
 3 Atlanta                            3,331
 4 Bangalore                          3,786
...
In this case, the subquery is as follows:
WITH %PARM City AS 'value:[All Cities]' SELECT FROM HOLEFOODS WHERE Outlet.@City
The following examples show the effect of the %CONTEXT parameter. First, the following query uses %MDX without %CONTEXT:
WITH 
MEMBER [MEASURES].[PercentOfAllRevenue] AS '100 * MEASURES.[Amount Sold] / 
%MDX("SELECT MEASURES.[Amount Sold] ON 0 FROM holefoods")' 

SELECT NON EMPTY [Channel].[H1].[Channel Name].Members ON 0,
NON EMPTY [Product].[P1].[Product Category].Members ON 1 
FROM [HoleFoods] 
WHERE [MEASURES].[PERCENTOFALLREVENUE]
 
                        No Channel             Online             Retail
1 Candy                        0.28               0.83               0.61
2 Cereal                       0.11               0.58               0.39
3 Dairy                        0.23               2.43               1.01
4 Fruit                        1.04               7.55               3.76
5 Pasta                        0.79               7.19               4.14
6 Seafood                      3.60              22.23              10.41
7 Snack                        2.58              10.84               7.28
8 Vegetable                    1.42               6.63               4.05
The calculated member [MEASURES].[PercentOfAllRevenue] computes the Amount Sold measure for the current pivot table cell, divided by the aggregate value for that measure across the entire cube. This value is then divided by 100, so the values displayed in the results add up to 100.
In contrast, consider the results when we use %CONTEXT as "rows":
WITH 
MEMBER [MEASURES].[PercentOfRows] AS '100 * MEASURES.[Amount Sold] / 
%MDX("SELECT MEASURES.[Amount Sold] ON 0 FROM holefoods","%CONTEXT","rows")' 

SELECT NON EMPTY [Channel].[H1].[Channel Name].Members ON 0,
NON EMPTY [Product].[P1].[Product Category].Members ON 1 
FROM [HoleFoods] 
WHERE [MEASURES].[PercentOfRows]
 
                        No Channel             Online             Retail
1 Candy                       16.08              48.30              35.62
2 Cereal                      10.29              53.69              36.02
3 Dairy                        6.38              66.15              27.48
4 Fruit                        8.41              61.14              30.45
5 Pasta                        6.49              59.33              34.18
6 Seafood                      9.93              61.34              28.73
7 Snack                       12.46              52.38              35.16
8 Vegetable                   11.72              54.77              33.51
In this case, the %MDX subquery uses the row context. As a result, the numbers in each row add up to 100.
Now consider the results when we use %CONTEXT as "columns":
WITH 
MEMBER [MEASURES].[PercentOfCols] AS '100 * MEASURES.[Amount Sold] / 
%MDX("SELECT MEASURES.[Amount Sold] ON 0 FROM holefoods","%CONTEXT","columns")' 

SELECT NON EMPTY [Channel].[H1].[Channel Name].Members ON 0,
NON EMPTY [Product].[P1].[Product Category].Members ON 1 
FROM [HoleFoods] 
WHERE [MEASURES].[PercentOfCols]
 
                        No Channel             Online             Retail
1 Candy                        2.76               1.43               1.94
2 Cereal                       1.10               0.99               1.22
3 Dairy                        2.34               4.18               3.19
4 Fruit                       10.35              12.96              11.88
5 Pasta                        7.83              12.34              13.08
6 Seafood                     35.83              38.14              32.89
7 Snack                       25.67              18.60              22.99
8 Vegetable                   14.12              11.37              12.80
In this case, the numbers in each column add up to 100.
See Also