Skip to main content

%MDX (MDX)

Executes an MDX query outside of the context of the current query and then returns a single result. This function is a Business Intelligence extension to MDX.

Returned Type

This function returns a number or a string.

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.

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

FeedbackOpens in a new tab