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.
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 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.
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>