Thanks for your feedback!
Need to tell us more? Click here or use the Feedback button.

Is this page helpful?

Contents

Using the Result Set API
Basic Example
Preparing and Executing a Query
Printing the Query Results
Examining the Query Results
Examining the Query Results for a DRILLTHROUGH Query
Examining the Query Metadata
Other Methods
Executing Query Files

Executing Business Intelligence Queries Programmatically

This page describes how to use the InterSystems IRIS® data platform Business Intelligence result set API, as well as how to execute files that contain MDX files. These options may be necessary for your Business Intelligence implementation.

For information on %ShowPlan() and %PrintStatistics(), see How the Analytics Engine Works. .

Using the Result Set API

The class %DeepSee.ResultSetOpens in a new tab enables you to execute MDX queries against cubes and to view and examine the results. To use this class, do the following:

Create an instance of %DeepSee.ResultSetOpens in a new tab.
For example:
```
 set rset=##class(%DeepSee.ResultSet).%New()
```
Optionally disable use of the cache. To do so, set the %UseCache property of that instance equal to 0. For example:
```
 set rset.%UseCache=0
```
By default, caching is enabled.
Optionally enabling tracing. To enable detailed tracing during the prepare phrase, set the %Trace property of the result set instance equal. To enable tracing for all phases of the query, set the %dstrace variable equal to 1. For example:
```
 set rset.%Trace=1
 set %dstrace=1
```
By default, tracing is disabled.
Create an MDX query, as a string. For example:
```
 set query="SELECT MEASURES.[%COUNT] ON 0, diagd.MEMBERS ON 1 FROM patients"
```
For details on the MDX syntax and functions supported in Business Intelligence, see Using InterSystems MDX and InterSystems MDX Reference.
Prepare and execute the query. Typically you do this as follows:
1. Call the %PrepareMDX() method of your instance, using your query string as the argument.
2. Call %Execute() or %ExecuteAsynch().
Each of these methods returns a status, which your code should check before proceeding.
Or you can call %ExecuteDirect(), which prepares and executes the query.
Or you can call lower-level methods of the %DeepSee.ResultSetOpens in a new tab; these are not discussed here.

Note:

If the query uses any plug-ins, note that %Execute() and %ExecuteDirect() do not return until all pending results are complete. Specifically they do not return until the analytics engine has finished executing any plug-ins used in the query.
If you used %ExecuteAsynch(), periodically check to see whether the query has completed. If the query uses any plug-ins, make sure that any pending results are also complete; pending results are the results from the plug-ins, which are executed separately from the query.
To determine the status of the query, call the %GetStatus() method of your instance. Or call the %GetQueryStatus() class method of %DeepSee.ResultSetOpens in a new tab. These methods return the status of the query and also (separately) the status of any pending results; see the class documentation for details.
Optionally, to cancel a query that has not yet completed, call the %CancelQuery() class method.
Your instance of %DeepSee.ResultSetOpens in a new tab now contains the query results. Now you can use methods of this instance to perform tasks such as the following:
- Print the results.
- Get cell values, get the number of cells or axes in the result set, and otherwise examine the results.
- Get the metadata for the query itself, such as the query plan, the SQL used for the listing, the MDX used for a range of cells in the query, and so on.
- Get the query statistics.

Basic Example

The following example creates and prepares a query, executes it, returns the result set as output, and displays the results:

ClassMethod RunQuery1(Output result As %DeepSee.ResultSet) As %Status
{
 Set rset=##class(%DeepSee.ResultSet).%New()
 Set query="SELECT MEASURES.[%COUNT] ON 0, diagd.MEMBERS ON 1 FROM patients"
 Set status=rset.%PrepareMDX(query)
 If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}

 Set status=rset.%Execute()
 If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}
   
 Write !, "Full results are as follows ***************",!
 Do rset.%Print()
 Quit $$$OK
}

When you run this method in the Terminal, you see results like the following:

SAMPLES>do ##class(BI.APISamples).RunQuery1()
 
Full results are as follows ***************
                             Patient Count
1 None                                8,394
2 asthma                                671
3 CHD                                   357
4 diabetes                              563
5 osteoporosis                          212

Preparing and Executing a Query

When you prepare and execute a query, you typically use the following methods:

%PrepareMDX()

method %PrepareMDX(pMDX As %String) as %Status

Parses the query, converts it to a runtime query object, and prepares it for execution.

%Execute()

method %Execute(ByRef pParms) as %Status

Executes the query synchronously; the pParms argument is discussed after this list. Use this only after you have prepared the query.

%ExecuteAsynch()

method %ExecuteAsynch(Output pQueryKey As %String, 
                      ByRef pParms, 
                      pWait As %Boolean = 0) as %Status

Executes the query asynchronously (or synchronously depending on the value of pWait). The arguments are discussed after this list. Use this only after you have prepared the query.

%ExecuteDirect()

classmethod %ExecuteDirect(pMDX As %String, 
                           ByRef pParms, 
                           Output pSC As %Status) as %DeepSee.ResultSet

Prepares and executes the query and then returns the result set. pSC is the status, which you should check. For the other arguments, see the discussion after this list.

Where:

pParms— Specifies the values of any named parameters to use in this query. This is a multidimensional array with one or more nodes as follows:

Node Value

Parameter name, not case-sensitive Value of this parameter

These values override any values for the same parameters given within the body of the query itself.
pQueryKey — Returns the unique key for this query, for use when later referring to the query (to cancel it, get the cell count, or for other uses).
pWait — Specifies whether to wait until the query has completed, before returning from this method call.
If pWait is true, %ExecuteAsynch() runs synchronously.

Node	Value
Parameter name, not case-sensitive	Value of this parameter

The following sample uses a query that contains a named parameter; this is an InterSystems extension to MDX:

ClassMethod RunQuery2(city as %String = "Magnolia",Output result As %DeepSee.ResultSet) As %Status
{
 Set rset=##class(%DeepSee.ResultSet).%New()
 Set query="WITH %PARM c as 'value:Pine' "
             _"SELECT homed.[city].@c ON 0 FROM patients"
 Set status=rset.%PrepareMDX(query)
 If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}

 Set myparms("c")=city
 Set status=rset.%Execute(.myparms)
 If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}
   
 Write !, "Full results are as follows ***************",!
 Do rset.%Print()
 Quit $$$OK
}

The following shows an example Terminal session:

d ##class(BI.APISamples).RunQuery2("Centerville")
 
Full results are as follows ***************
                               Centerville
                                      1,124

Printing the Query Results

To display the query results for diagnostic purposes, use one of the following methods:

%Print()

Prints the query results and returns a status. For an example, see Basic Example and Preparing and Executing a Query, earlier in this page.

%PrintListing()

If the query uses the MDX DRILLTHROUGH clause, this method performs the drillthrough for the first cell of the query, and prints the results to the current device. Otherwise, it prints an error.

This method does not return anything.

Important:

Both methods include a line number at the start of each line of data (that is, after any column headings). The line number is not part of the results.

The following example demonstrates %PrintListing():

ClassMethod RunQuery3() 
{
    Set rset=##class(%DeepSee.ResultSet).%New()
    
    Set query="DRILLTHROUGH SELECT gend.female ON 0,birthd.[1913] ON 1 "
             _"FROM patients RETURN PatientID,PrimaryCarePhysician->LastName"

    Set status=rset.%PrepareMDX(query)
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit}

    Set status=rset.%Execute()
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit}
    
    Write !, "Listing details for the first cell are as follows ***************",!
    Do rset.%PrintListing()
}

You can use this in the Terminal as follows:

SAMPLES>d ##class(BI.APISamples).RunQuery3()
 
Listing details for the first cell are as follows ***************
   #  PatientID      LastName
   1: SUBJ_101317    Xiang
   2: SUBJ_104971    North
   3: SUBJ_105093    Klausner
   4: SUBJ_109070    Quine

Examining the Query Results

To work with the query results programmatically, you first need to understand their organization. The result set is a set of cells organized by a set of axes. Unless you are sure of the organization of the result set, use %GetRowCount() and %GetColumnCount() to get information about the number of rows and columns.

Then to access the value in a given cell, use the %GetOrdinalValue() method. Or to access the column and row header labels, use the %GetOrdinalLabel() method. Or to get detailed information about members used in a cell, use the %GetAxisMembers() method. The following subsections give the details.

Note:

There are different methods to examine the results of a DRILLTHROUGH query. See the next section.

Getting the Number of Columns and Rows

To get the number of columns in the result set, use %GetColumnCount().

Similarly, to get the number of rows, use %GetRowCount().

For example, the following method prints a given result set and then uses the preceding methods to report on the axes of this result set:

ClassMethod ShowRowAndColInfo(rset As %DeepSee.ResultSet)
{
    //print query results
    write !, "Result set for comparison",!
    do rset.%Print()
    
    set colCount=rset.%GetColumnCount()
    set rowCount=rset.%GetRowCount()
    write !, "This result set has ",colCount, " column(s)"
    write !, "This result set has ",rowCount, " row(s)"
}

The following shows example output from this method:

Result set for comparison
                             Patient Count
1 None                                  844
2 asthma                                 55
3 CHD                                    38
4 diabetes                               55
5 osteoporosis                           26
 
This result set has 1 column(s)
This result set has 5 row(s)

The following shows output based on a different result set:

Result set for comparison
 
1 0 to 29->Female                       207
2 0 to 29->Male                         192
3 30 to 59->Female                      205
4 30 to 59->Male                        209
5 60+->Female                           115
6 60+->Male                              72
 
This result set has 1 column(s)
This result set has 6 row(s)

As noted earlier, remember that %Print() includes a line number at the start of each line of data, and this line number is not part of the results.

Getting the Value of a Given Cell

To get the value of a given cell, use %GetOrdinalValue(). This method has the following signature:

method %GetOrdinalValue(colNumber,rowNumber) as %String

Where colNumber is the column number (and 1 represents the first column). Similarly, rowNumber is the row number (and 1 represents the first row). If there is no such cell within the result set, the method returns null.

Getting the Column or Row Labels

To get the labels used for a column or a row, call the %GetOrdinalLabel() method of your instance. This method has the following signature:

method %GetOrdinalLabel(Output pLabel As %String, 
                        pAxis As %Integer, 
                        pPosition As %Integer, 
                        Output pFormat As %String) as %Integer

Where:

pLabel is a multidimensional array with one node for each label as follows:

Node Value

Integer that represents the label number; the first label is 1, and so on. Label

In this array, the first label is the most specific (innermost) label, the second label is the next most specific, and so on. See the example.
This array is returned as an output parameter.
pAxis is the axis to examine. Use 1 to get the column labels or use 2 to get the row labels.
pPosition is the position along the axis to examine. The first position is 1.

Node	Value
Integer that represents the label number; the first label is 1, and so on.	Label

This method returns the number of labels at the given position on the given axis. The following shows an example. It executes a CROSSJOIN query (so that an axis has multiple labels), displays the results so that you can compare them to the labels, and then it iterates through the members on that axis, printing the labels for each:

ClassMethod ShowRowLabels() As %Status
{
    Set rset=##class(%DeepSee.ResultSet).%New()
    Set query="SELECT CROSSJOIN(aged.[age group].MEMBERS,"
    Set query=query_"gend.gender.MEMBERS) ON 1 FROM patients"
    Set status=rset.%PrepareMDX(query)
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}

    Set status=rset.%Execute()
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}
    
    Write !, "Full results are as follows ***************",!
    Do rset.%Print()
    
    Write !, "Labels used on the rows are as follows ***************",!
    For j=1:1:rset.%GetRowCount() {
        Write !, "Row ",j
        Set labelcount=rset.%GetOrdinalLabel(.pLabel,2,j)
        For i=1:1:labelcount {
            Write !, "    label("_i_") is "_pLabel(i)
            }
       }
    
    Quit $$$OK
}

When executed in the Terminal, this method gives output like the following:

SAMPLES>d ##class(BI.APISamples).ShowRowLabels()
 
Full results are as follows ***************
 
1 0 to 29->Female                       207
2 0 to 29->Male                         192
3 30 to 59->Female                      205
4 30 to 59->Male                        209
5 60+->Female                           115
6 60+->Male                              72
  
Labels used on the rows are as follows ***************
 
Row 1
    label(1) is Female
    label(2) is 0 to 29
Row 2
    label(1) is Male
    label(2) is 0 to 29
Row 3
    label(1) is Female
    label(2) is 30 to 59
Row 4
    label(1) is Male
    label(2) is 30 to 59
Row 5
    label(1) is Female
    label(2) is 60 +
Row 6
    label(1) is Male
    label(2) is 60 +
SAMPLES>

Getting Details for Cell Contents

So far, this page has provided instructions only on obtaining labels and cell values. In some cases, you might need more specific information about the contents of a given cell.

First, it is useful to review the concepts, with some example queries for reference. Consider the following query results, as seen in the Business Intelligence shell:

                             Patient Count
1 None                                  844
2 asthma                                 55
3 CHD                                    38
4 diabetes                               55
5 osteoporosis                           26

In this example, each row corresponds to one member of the diagnosis dimension. The column corresponds to one member (Patient Count) of the Measures dimension. The following shows another example:

                             Patient Count
1 0 to 29->Female                       207
2 0 to 29->Male                         192
3 30 to 59->Female                      205
4 30 to 59->Male                        209
5 60+->Female                           115
6 60+->Male                              72

In this example, each row corresponds to a tuple that combines one member of the age group dimension with one member of the gender dimension. (A tuple is a intersection of members.)

In general, in an MDX result set, each row corresponds to a tuple and each column corresponds to a tuple. Each of these tuples might be a simple member as in the first example, or might be a combination of multiple members as shown in the second example. A tuple may or may not include a measure.

For any given cell, you might need to find information about the tuple of the column to which it belongs and the tuple of the row to which it belongs. To get information about these tuples, do the following:

Invoke the %GetAxisMembers() method of your result set:
```
method %GetAxisMembers(pAxis As %Integer, 
                       Output pKey, 
                       pItemNo As %Integer = "") as %Status
```
Finds information for the requested axis (and the optional requested item on that axis), writes that to a process-private global and returns, by reference, a key that you can use to retrieve information from that global. (The system writes this information to a process-private global because potentially there can be a large amount of information, and it is impossible to determine its structure ahead of time.)
pAxis optionally specifies the axis you are interested in:
- Use 0 to return information about the slicer axis (the WHERE clause).
- Use 1 to return information about the columns (this is axis 0 in MDX).
- Use 2 to return information about the rows.
pKey, which is returned as an output parameter, is a key that you use later to access the information.
pItemNo optionally specifies the tuple on that axis for which you want information. If you specify this argument, the method writes data only for that tuple; if you omit it, the method writes data for all tuples. Use 1 for the first tuple on an axis.
Use pKey to retrieve the appropriate node or nodes from the process-private global ^||DeepSee.AxisMembers. The %GetAxisMembers() method writes data to the nodes ^||DeepSee.AxisMembers(pKey,pAxis,j,k) where:
- pKey is the key returned by the %GetAxisMembers() method.
- pAxis is an integer that specifies the axis.
- j is an integer that specifies the tuple in which you are interested. Use 0 for the first tuple on an axis.
- k is an integer that specifies the member of the tuple in which you are interested. Use 1 for the first member of a tuple.
Retrieve the appropriate list items from each of those nodes. Each node of ^||DeepSee.AxisMembers has a value of the following form:
```
$LB(nodeno,text,dimName,hierName,levelName,memberKey,dimNo,hierNo,levelNo)
```
Where:
- nodeno is the node number of this part of the axis.
- text is the text for this part of the axis.
- dimName, hierName, and levelName are the names of the dimension, hierarchy, and level used for this part of the axis.
- memberKey is the key for the member used for this part of the axis.
- dimNo, hierNo, and levelNo are the numbers of the dimension, hierarchy, and level used for this part of the axis.
Kill the generated nodes of the process-private global ^||DeepSee.AxisMembers.
Or, if you are certain that no other processes are using the %GetAxisMembers() method, kill the entire global.
The system does not automatically kill this global.

The following example method prints a description of the column and row tuples for a given cell, given a result set and a cell position:

ClassMethod ShowCellDetails(rset As %DeepSee.ResultSet, col As %Integer = 1, row As %Integer = 1)
{
    //print query results 
    write !, "Result set for comparison",!  
    do rset.%Print()

    //call %GetAxisMembers to build process-private global with info 
    //for given result set and axis; return key of node that has this info
    Set status=rset.%GetAxisMembers(1,.columnkey)
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit}
    Set status=rset.%GetAxisMembers(2,.rowkey)
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit}
    
    write !, "We are looking at the cell ("_col_","_row_")"
    write !, "The value in this cell is ", rset.%GetOrdinalValue(col,row)
    write !, "For this cell, the column is a tuple that combines the following member(s):"
    set i=0
    while (i '= "") {
        write !, "   Member ",i
        set infolist=^||DeepSee.AxisMembers(columnkey,1,col,i)
        write:$LI(infolist,3)'="" !, "      Dimension name: ",$LI(infolist,3)
        write:$LI(infolist,4)'="" !, "      Hierarchy name: ",$LI(infolist,4)
        write:$LI(infolist,5)'="" !, "      Level name: ",$LI(infolist,5)
        write:$LI(infolist,6)'="" !, "      Member key: ",$LI(infolist,6)
        set i=$ORDER( ^||DeepSee.AxisMembers(columnkey,1,col,i) )
    }

    write !, "For this cell, the row is a tuple that combines the following member(s):"
    set i=0
    while (i '= "") {
        write !, "   Member ",i
        set infolist=^||DeepSee.AxisMembers(rowkey,2,row,i)
        write:$LI(infolist,3)'="" !, "      Dimension name: ",$LI(infolist,3)
        write:$LI(infolist,4)'="" !, "      Hierarchy name: ",$LI(infolist,4)
        write:$LI(infolist,5)'="" !, "      Level name: ",$LI(infolist,5)
        write:$LI(infolist,6)'="" !, "      Member key: ",$LI(infolist,6)
        set i=$ORDER( ^||DeepSee.AxisMembers(rowkey,2,row,i) )
    }
    Kill ^||DeepSee.AxisMembers(columnkey)
    Kill ^||DeepSee.AxisMembers(rowkey)
}

The following shows example output for this method:

 
Result set for comparison
                           0 to 29           30 to 59                60+
 1 Female->None                 189                184                 62
 2 Female->asthma                18                  7                  7
 3 Female->CHD                    *                  4                 14
 4 Female->diabetes               *                 11                 23
 5 Female->osteopor               *                  *                 23
 6 Male->None                   178                186                 45
 7 Male->asthma                  14                  7                  2
 8 Male->CHD                      *                  5                 15
 9 Male->diabetes                 *                 11                 10
10 Male->osteoporos               *                  *                  3
 
We are looking at the cell (2,6)
The value in this cell is 186
For this cell, the column is a tuple that combines the following member(s):
   Member 0
      Dimension name: AgeD
      Hierarchy name: H1
      Level name: Age Group
      Member key: 30 to 59
For this cell, the row is a tuple that combines the following member(s):
   Member 0
      Dimension name: GenD
      Hierarchy name: H1
      Level name: Gender
      Member key: Male
   Member 1
      Dimension name: DiagD
      Hierarchy name: H1
      Level name: Diagnoses
      Member key: <null>

Examining the Query Results for a DRILLTHROUGH Query

If the query uses the MDX DRILLTHROUGH statement, then you use a different technique to examine the results.

In this case, use the following method of your instance of %DeepSee.ResultSetOpens in a new tab:

method %GetListingResultSet(Output pRS As %SQL.StatementResult, Output pFieldList As %List) as %Status

This method returns the following as output parameters:

pRS is an instance of %SQL.StatementResultOpens in a new tab that contains the results from the DRILLTHROUGH query.
pFieldList is a list (in $LIST format) of the fields in this result set.

Use pRS in the same way that you use any other instance of %SQL.StatementResultOpens in a new tab; see the class reference for details.

Examining the Query Metadata

You can use the following methods to get the cube name, query text, and other metadata for any instance of %DeepSee.ResultSetOpens in a new tab. (For information on accessing the query plan, see the next section.)

%GetCubeName()

method %GetCubeName() as %String

Returns the name of the cube that the query uses. The query must be prepared before you can use this method.

%GetListingSQL()

method %GetListingSQL() as %String

Returns the SQL statement used to display the source data, if the query is a DRILLTHROUGH query.

%GetParameterInfo()

method %GetParameterInfo(Output pParms) as %Status

Returns a multidimensional array that contains the parameters used in the query, along with the values used for them. This array has the structure described earlier in this page.

%GetQueryText()

method %GetQueryText() as %String

Returns a string that contains the MDX query that was used to create this result set.

%GetSlicerForCellRange()

method %GetSlicerForCellRange(Output pSlicer As %String, 
                              pStartRow As %Integer, pStartCol As %Integer, 
                              pEndRow As %Integer, pEndCol As %Integer) 
                              as %Status

Returns, by reference, a string that contains the MDX slicer statement for the given range of cells. You specify a range of cells by indicating a rectangle that consists of a starting row and column and an ending row and column. The first cell position on any axis is 1.

%IsDrillThrough()

method %IsDrillThrough() as %Boolean

Returns true if the query is a DRILLTHROUGH query; returns false otherwise.

For example, the following method generates a report on the basic metadata:

ClassMethod ShowQueryMetadata(rset As %DeepSee.ResultSet) As %Status
{
    Set cubename=rset.%GetCubeName()
    Write !, "This result set comes from the following cube: ",cubename,!
    
    Set status=rset.%GetParameterInfo(.pParms)
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}
    If $DATA(pParms) {
        Write "The query uses the following parameters:",!
        Set p = $ORDER(pParms(""))
            While (p '= "") {
                Write $$$UPPER(p), " = " ,$GET(pParms(p,"VALUE")),!
                Set p = $ORDER(pParms(p))
        }
    }
    Set query=rset.%GetQueryText()
    Write "The query is as follows:",!, query,!

    Set isdrill=rset.%IsDrillThrough()
    If isdrill {
        Set listingsql=rset.%GetListingSQL()
        Write !!, "It uses the following SQL to drill into the source table:"
        Write !, listingsql
        }
}

The following examples (with line breaks added for readability) show output from this method, using several sample result sets. In the first case, we use GetResultSet1() of the sample class BI.APISamples:

SAMPLES>set rs1=##class(BI.APISamples).GetResultSet1()
 
SAMPLES>d ##class(BI.APISamples).ShowQueryMetadata(rs1)
 
This result set comes from the following cube: patients
The query is as follows:
SELECT {[MEASURES].[AVG TEST SCORE],[MEASURES].[%COUNT]} ON 0,
[DIAGD].[H1].[DIAGNOSES].MEMBERS ON 1 FROM [PATIENTS]

In the next example, we use GetResultSet2(), which uses a query that contains named parameters:

SAMPLES>set rs2=##class(BI.APISamples).GetResultSet2()
 
SAMPLES>d ##class(BI.APISamples).ShowQueryMetadata(rs2)
 
This result set comes from the following cube: patients
The query uses the following parameters:
C = Magnolia
The query is as follows:
SELECT [HOMED].[H1].[CITY].MAGNOLIA ON 0,%SEARCH ON 1 FROM [PATIENTS]

In the next example, we use GetResultSet3(), which uses a query that does a drillthrough:

SAMPLES>set rs3=##class(BI.APISamples).GetResultSet3()
 
SAMPLES>d ##class(BI.APISamples).ShowQueryMetadata(rs3)
 
This result set comes from the following cube: patients
The query is as follows:
DRILLTHROUGH SELECT [GEND].[H1].[GENDER].[FEMALE] ON 0,[BIRTHD].[H1].[YEAR].[1913] ON 1 
FROM [PATIENTS] RETURN  PatientID, PrimaryCarePhysician-> LastName
 
 
It uses the following SQL to drill into the source table:
SELECT TOP 1000 PatientID,PrimaryCarePhysician-> LastName FROM 
BI_Study.Patient source WHERE source.%ID IN (SELECT _DSsourceId FROM 
BI_Model_PatientsCube.Listing WHERE _DSqueryKey = '1858160995')

The following example method generates a report that shows the MDX slicer for a given range of cells, in a given result set:

ClassMethod ShowSlicerStatement(rset As %DeepSee.ResultSet, Row1 As %Integer = 1, 
Col1 As %Integer = 1, Row2 As %Integer, Col2 As %Integer) As %Status
{
    If '$DATA(Row2) {Set Row2=Row1}
    If '$DATA(Col2) {Set Col2=Col1}

    Set status=rset.%GetSlicerForCellRange(.slicer,Row1,Col1,Row2,Col2)
    If $$$ISERR(status) {Do $System.Status.DisplayError(status) Quit status}
    
    Write !, "The requested cell range:"
    Write !, "   Columns ",Col1, " through ", Col2
    Write !, "   Rows    ",Row1, " through ", Row2

    Write !, "The slicer statement for the given cell range is as follows:"
    Write !, slicer
    
    If 'rset.%IsDrillThrough(){
        Write !!, "For comparison, the query results are as follows:",!
        Do rset.%Print()
    }
    Else {
           Write !!, "This is a drillthrough query and %Print "
            _"does not provide a useful basis of comparison"
           }
}

To try this method, we use GetResultSet4() of BI.APISamples, which uses a query that has different levels for rows and columns:

SAMPLES>d ##class(BI.APISamples).ShowSlicerStatement(rs4)
 
The requested cell range:
   Columns 1 through 1
   Rows    1 through 1
The slicer statement for the given cell range is as follows:
CROSSJOIN({[AgeD].[H1].[Age Bucket].&[0 to 9]},{[GenD].[H1].[Gender].&[Female]})
 
For comparison, the query results are as follows:
                              Female                 Male
1 0 to 9                          689                  724
2 10 to 19                        672                  722
3 20 to 29                        654                  699
4 30 to 39                        837                  778
5 40 to 49                        742                  788
6 50 to 59                        551                  515
7 60 to 69                        384                  322
8 70 to 79                        338                  268
9 80+                             204                  113

Other Methods

The class %DeepSee.ResultSetOpens in a new tab also provides additional methods like the following:

%GetCellCount()
%FormatNumber()
%GetOrdinalLabel()
%GetOrdinalKey()
%GetQueryKey()
%GetRowTotal()
%GetColumnTotal()
%GetGrandTotal()

For a full list and details, see the class reference.

Executing Query Files

The system provides a tool for executing MDX queries that have been saved in files. The output can be written to the current device or to a file. The output results include statistics on the query run.

This tool can be useful for simple testing.

About Query Files

A query file must be an ASCII file as follows:

Any line breaks in the file are ignored.
Two or more blank spaces in a row are treated as a single blank space.
The file can contain any number of MDX queries (zero or more).
The queries can contain comments, but comments cannot be nested. An MDX comment has the following form:
```
/* comment here */
```
A comment may or may not be on its own line.
Use the command GO on a line by itself to execute a query. The query consists of all text from the previous GO (or the start of the file) up to, but not including, the GO command.
There must be no spaces before GO on this line.

For example:

/* First query in this file*/
SELECT MEASURES.%COUNT ON 0, 
homed.[home zip].[34577].CHILDREN 
ON 1 FROM patients
GO


/* Second query in the file*/
SELECT MEASURES.%COUNT ON 0, 
homed.[home city].MEMBERS ON 1 /*ignore this comment*/FROM patients
GO

Executing a Query File

To execute a query file, use the following class method of %DeepSee.ShellOpens in a new tab:

ClassMethod %RunQueryFile(pQueryFile As %String, pResultFile As %String = "") As %Status

Where:

pQueryFile is the name of the query file.
pResultFile is the name of the file into which to write the query statistics.
If this argument is null, the method writes the query statistics to the current device.

In all cases, the method writes the query results to the current device.

For example:

d ##class(%DeepSee.Shell).%RunQueryFile("c:\mdxtest.txt")
------------------------------------------------------
Query 1:
/* First query in this file*/SELECT MEASURES.%COUNT ON 0, homed.[home zip].[34577].CHILDREN ON 1 FROM patients
                                     Count
1 Cypress                             1,091
2 Magnolia                            1,087
3 Pine                                1,121
Query Statistics:
 Results Cache:                        1
 Computations:                         0
 Cache Hits:                           0
 Cells:                                0
 Expressions:                          0
 
 Prepare:                          0.261 ms
 Execute Axes:                     0.026 ms
 Execute Cells:                    0.000 ms
 Consolidate:                      0.000 ms
 Total Time:                       0.287 ms
 
ResultSet Statistics:
 Cells:                                3
 Parse:                            3.553 ms
 Display:                          0.361 ms
 Total Time:                       3.914 ms
------------------------------------------------------
Query 2:
/* Query 2*/SELECT MEASURES.%COUNT ON 0, homed.[home city].MEMBERS ON 1 /*ignore this comment*/FROM patients
                                     Count
1 Cedar Falls                         1,119
...

For information on query statistics, see How the Analytics Engine Works.

Performing Localization for Business Intelligence

Using the Cube Manager