%MDX (MDX)
Returned Type
Syntax and Details
%MDX(mdx_query,parmName1,parmValue1,parmName2,parmValue2)
Where:
-
mdx_query is a quoted MDX query. It should return a single value; only the upper left cell is used.
The query can include named parameters, calculated members, and named sets.
-
parmName1, parmName2, and so on are optional named parameters in the query. These must be quoted.
The order in which you list parameters does not affect the query.
You can specify up to 16 parameters and their values.
-
parmValue1, parmValue2, and so on are the corresponding values of the named parameters.
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.
The system provides the special %CONTEXT parameter which you can use within %MDX. For details, see %KPI, which also accepts this parameter.
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.
