DRILLTHROUGH Statement (MDX)
Syntax and Details
DRILLTHROUGH optionalmaxrows select_statement
DRILLTHROUGH optionalmaxrows select_statement RETURN fieldname1, fieldname2, ...
DRILLTHROUGH optionalmaxrows select_statement RETURN fieldname1, ... %ORDER BY fieldname3, ...
DRILLTHROUGH optionalmaxrows select_statement %LISTING [listingname]
optionalmaxrows, which is optional, has the form MAXROWS integer
This argument specifies the maximum number of rows to return. The default is 1000.
select_statement is a statement that uses SELECT.
fieldname1, fieldname2, fieldname3, fieldname4, and so on are names of fields in the base class used by the cube.
listingname is the name of a detail listing. This listing must already be defined and the user must have permission to use it. If this name does not include spaces, you can omit the square brackets around it. The name of the listing is case-sensitive.
The RETURN clause specifies the fields to display. The %ORDER BY clause is an InterSystems extension to MDX. If included, this clause specifies how to sort the displayed records.
The %LISTING clause is another InterSystems extension to MDX. This clause specifies the listing to use. If you specify this clause, the system displays the fields as given in that listing.
If you do not specify the RETURN clause or the %LISTING clause, the query uses the default listing defined in the cube.
Internally, the system builds and uses an SQL query.
If the SELECT statement returns more than one cell of data, the listing shows only the fields associated with the first cell.
Additional Options for RETURN and ORDER BY
In the RETURN and %ORDER BY clauses, note the following points:
You can use arrow syntax to refer to a property in another table. See Implicit Joins (Arrow Syntax) in Using InterSystems SQL.
You can include aliases.
You can use standard SQL and InterSystems IRIS® functions. To use a standard SQL function, enclose it within parentheses so that the function name is not interpreted as a field name; this is not necessary for InterSystems SQL functions, which start with the percent character (%).
You can use more advanced SQL features if you use source.field_name rather than field_name.
... RETURN %ID,%EXTERNAL(Field1) F1,'$'||source.Sales Sales
The first line of any listing is a heading that indicates the field names or their aliases. Below the heading, the listing has a column of data below each heading. In this case, the columns would be as follows:
%ID — this column displays the %ID field
F1 — this column uses the InterSystems SQL %EXTERNAL function to return the value of the Field1 field in DISPLAY format
Sales — this column displays the Sales field, preceded by a dollar sign ($)
The first example does not use RETURN, so it uses the default listing as defined in the cube:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients # PatientID Age Gender TestScore HomeCity DoctorGrou 1: SUBJ_10161 0 F 76 3 I 2: SUBJ_10330 0 F 3 II 3: SUBJ_10554 0 F 68 3 II 4: SUBJ_10555 0 F 78 3 II 5: SUBJ_10686 0 F 91 3 I ...
The next example uses the MAXROWS argument:
DRILLTHROUGH MAXROWS 3 SELECT homed.Magnolia ON 1 FROM patients # PatientID Age Gender TestScore HomeCity DoctorGrou 1: SUBJ_10161 0 F 76 3 I 2: SUBJ_10330 0 F 3 II 3: SUBJ_10554 0 F 68 3 II
The next example uses the RETURN clause:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients RETURN Gender, HomeCity->PostalCode # Gender PostalCode 1: F 34577 2: F 34577 3: F 34577 4: F 34577 5: F 34577 ...
The next example also uses the %ORDER BY clause:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients RETURN PatientID, Age, Gender %ORDER BY Age # PatientID Age Gender 1: SUBJ_101616 0 F 2: SUBJ_102705 0 M 3: SUBJ_103210 0 M 4: SUBJ_103300 0 F 5: SUBJ_103972 0 M ...
The last example uses the %ORDER BY clause with two field names to specify the order:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients RETURN PatientID, Age, Gender %ORDER BY Gender, Age # PatientID Age Gender 1: SUBJ_101616 0 F 2: SUBJ_103300 0 F 3: SUBJ_105548 0 F 4: SUBJ_105556 0 F 5: SUBJ_106865 0 F ...
In this case, the records are sorted first by gender. Within gender, they are sorted by age.