DRILLTHROUGH Statement (MDX)

Displays the lowest-level data associated with the first cell of results of a given SELECT statement.

Syntax and Details

DRILLTHROUGH optionalmaxrows select_statement

Or:

DRILLTHROUGH optionalmaxrows select_statement RETURN fieldname1, fieldname2, ...

Or:

DRILLTHROUGH optionalmaxrows select_statement RETURN fieldname1, ... %ORDER BY fieldname3, ...

Or:

DRILLTHROUGH optionalmaxrows select_statement %LISTING [listingname]

Where:

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.

Important:

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).
You can include aliases.
You can use standard SQL and InterSystems IRIS® data platform 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.

For example:

... 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 ($)

Example

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.

DROP MEMBER Statement (MDX)

DRILLFACTS Statement (MDX)