Specifying the MDX Query Manually

Specifying the MDX Query Manually

Sometimes it is useful to see and then modify the MDX query that the system generates for a pivot table. To do so:

1. Click the Query Text button .

   The system then displays a dialog box that displays the query used by this pivot table.

2. If you want to use a different query, click Manual Mode.

3. Edit the query. See the following subsection for an example.

   If you had displayed a detail listing, the bottom area of this dialog box also displays the listing query that the system used:

   

4. Click OK.

When the Analyzer displays a pivot table that is defined by a manually edited or manually entered MDX query, the Query Text button changes to the following: . Also, the Rows, Columns, and Measures boxes are grayed out. You cannot use locally defined calculated members unless you also add the appropriate WITH clause to your query. You can, however, drag items to the Filters box; the system applies these filters but does not modify the manual query text. That is, the base query and its filters are stored separately within the pivot table definition.

Modifying Details of the 80/20 Suppression Option

For example, if you had used the 80/20 suppression option, the MDX query might look like this (with harmless line breaks added):

SELECT 
NON EMPTY {[Measures].[Amount Sold],[Measures].[Units Sold],[Measures].[%COUNT]} 
ON 0,
NON EMPTY 
{TOPPERCENT([Product].[P1].[Product Name].Members,80),
%LABEL(SUM(BOTTOMPERCENT([Product].[P1].[Product Name].Members,20)),"Other",,,,"font-style:italic;")} 
ON 1 
FROM [HoleFoods]

For the TOPPERCENT and BOTTOMPERCENT functions:

1. The first argument specifies the set of members to use.

2. The second argument specifies the percentage.

3. The third argument (omitted in the preceding example) specifies the measure to use for ranking the members.

To change the percentages, change the second arguments for TOPPERCENT and BOTTOMPERCENT. For example:

SELECT 
NON EMPTY {[Measures].[Amount Sold],[Measures].[Units Sold],[Measures].[%COUNT]} ON 0,
NON EMPTY 
{TOPPERCENT([Product].[P1].[Product Name].Members,90),
%LABEL(SUM(BOTTOMPERCENT([Product].[P1].[Product Name].Members,10)),"Other",,,,"font-style:italic;")}
ON 1 
FROM [HoleFoods]

For details on MDX, see Using InterSystems MDX and InterSystems MDX Reference.