To drill up a hierarchy and get related information, you need to:
- Have at least one object that has a drill-down group as a dimension
- Use the DrillUp method
Example
In this example, the first step is to create a transient chart with a drill-down group. The drill-down group is as below:
- Highest level: Country
- Intermediate level: Region
- Low level: City
1. The first step is to create a chart with a drill-down group as dimension and one measure.
The drill-down group is made of the fields Country, Region and City.
The initial data fetch is composed of the first 5 rows (qHeight is 5) and the first two columns (qWidth is 2).
qGrouping is set to H because the chart contains a drill-down group.
qFieldDefs contains the fields Country, Region and City.
The chart contains one measure: qDef is set to Count([Golf Course]).
The client sends:
{
"jsonrpc": "2.0",
"id": 1,
"method": "CreateSessionObject",
"handle": 1,
"params": [
{
"qInfo": {
"qId": "",
"qType": "Chart"
},
"qHyperCubeDef": {
"qStateName": "$",
"qDimensions": [
{
"qLibraryId": "",
"qNullSuppression": false,
"qDef": {
"qGrouping": "H",
"qFieldDefs": [
"Country",
"Region",
"City"
],
"qFieldLabels": [
"Country label",
"Region label",
"City label"
]
}
}
],
"qMeasures": [
{
"qLibraryId": "",
"qSortBy": {
"qSortByState": 0,
"qSortByFrequency": 0,
"qSortByNumeric": 0,
"qSortByAscii": 0,
"qSortByLoadOrder": 1,
"qSortByExpression": 0,
"qExpression": {
"qv": ""
}
},
"qDef": {
"qLabel": "",
"qDescription": "",
"qTags": [
"tags"
],
"qGrouping": "N",
"qDef": "Count([Golf Course])"
}
}
],
"qInitialDataFetch": [
{
"qTop": 0,
"qLeft": 0,
"qHeight": 5,
"qWidth": 2
},
{
"qTop": 0,
"qLeft": 0,
"qHeight": 0,
"qWidth": 0
}
],
"qSuppressZero": true,
"qSuppressMissing": true
}
}
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 1,
"result": {
"qReturn": {
"qType": "GenericObject",
"qHandle": 2
}
},
"change": [
2
]
}
The transient chart is created and has 2 as a handle.
2. Select the country United States in order to drill down one level. The first value (qValues is 0 ) of the field Country (qDimNo is 0) is selected.
The client sends:
{
"jsonrpc": "2.0",
"id": 2,
"method": "SelectHyperCubeValues",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
[
0
],
false
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 2,
"result": {
"qSuccess": true
},
"change": [
2
]
}
The country United States is successfully selected.
3. Get the layout of the chart. The regions in the United States should be returned.
The client sends:
{
"jsonrpc": "2.0",
"id": 3,
"method": "GetLayout",
"handle": 2,
"params": []
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"qLayout": {
"qInfo": {
"qId": "fde3e2ca-c691-496b-87a7-5a20ecd0f42a",
"qType": "Chart"
},
"qSelectionInfo": {},
"qHyperCube": {
"qStateName": "$",
"qSize": {
"qcx": 2,
"qcy": 44
},
"qDimensionInfo": [
{
"qFallbackTitle": "Region label",
"qApprMaxGlyphCount": 22,
"qCardinal": 240,
"qSortIndicator": "N",
"qGroupFallbackTitles": [
"Country label",
"Region label",
"City label"
],
"qGroupPos": 1,
"qStateCounts": {
"qLocked": 0,
"qSelected": 0,
"qOption": 44,
"qDeselected": 0,
"qAlternative": 0,
"qExcluded": 196,
"qSelectedExcluded": 0,
"qLockedExcluded": 0
},
"qTags": [
"$text"
],
"qDimensionType": "D",
"qGrouping": "H",
"qNumFormat": {
"qType": "R",
"qnDec": 14,
"qUseThou": 1,
"qFmt": "##############",
"qDec": ".",
"qThou": ","
},
"qIsAutoFormat": true,
"qGroupFieldDefs": [
"Country",
"Region",
"City"
]
}
],
"qMeasureInfo": [
{
"qFallbackTitle": "Count([Golf Course])",
"qApprMaxGlyphCount": 5,
"qCardinal": 0,
"qSortIndicator": "A",
"qNumFormat": {
"qType": "I",
"qnDec": 0,
"qUseThou": 1,
"qFmt": "###0",
"qDec": "."
},
"qMin": 20,
"qMax": 1310,
"qIsAutoFormat": true,
"qAttrExprInfo": []
}
],
"qEffectiveInterColumnSortOrder": [
0,
1
],
"qGrandTotalRow": [
{
"qText": "14521",
"qNum": 14521,
"qElemNumber": -1,
"qState": "X",
"qIsTotalCell": true
}
],
"qDataPages": [
{
"qMatrix": [
[
{
"qText": "Illinois",
"qNum": "NaN",
"qElemNumber": 0,
"qState": "O"
},
{
"qText": "734",
"qNum": 734,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Pennsylvania",
"qNum": "NaN",
"qElemNumber": 1,
"qState": "O"
},
{
"qText": "757",
"qNum": 757,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "California",
"qNum": "NaN",
"qElemNumber": 2,
"qState": "O"
},
{
"qText": "1072",
"qNum": 1072,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Alabama",
"qNum": "NaN",
"qElemNumber": 3,
"qState": "O"
},
{
"qText": "290",
"qNum": 290,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Florida",
"qNum": "NaN",
"qElemNumber": 4,
"qState": "O"
},
{
"qText": "1310",
"qNum": 1310,
"qElemNumber": 0,
"qState": "L"
}
]
],
"qTails": [
{
"qUp": 0,
"qDown": 0
}
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 5
}
},
{
"qMatrix": [],
"qTails": [],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 0,
"qHeight": 0
}
}
],
"qPivotDataPages": [],
"qStackedDataPages": [],
"qMode": "S",
"qNoOfLeftDims": -1
}
}
}
}
The first five regions are displayed (the first five values are returned because qHeight was set to 5 in the initial data fetch when creating the chart).
4. Select the first region Illinois in order to drill down one level. The first value (qValues is 0) of the field Region (qDimNo is 0) is selected.
The client sends:
{
"jsonrpc": "2.0",
"id": 4,
"method": "SelectHyperCubeValues",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
[
0
],
false
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 4,
"result": {
"qSuccess": true
},
"change": [
2
]
}
The region Illinois is successfully selected.
5. Get the layout of the chart. The cities in the United States should be returned.
The client sends:
{
"jsonrpc": "2.0",
"id": 5,
"method": "GetLayout",
"handle": 2,
"params": []
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 6,
"result": {
"qLayout": {
"qInfo": {
"qId": "fde3e2ca-c691-496b-87a7-5a20ecd0f42a",
"qType": "Chart"
},
"qSelectionInfo": {},
"qHyperCube": {
"qStateName": "$",
"qSize": {
"qcx": 2,
"qcy": 387
},
"qDimensionInfo": [
{
"qFallbackTitle": "City label",
"qApprMaxGlyphCount": 30,
"qCardinal": 7532,
"qSortIndicator": "N",
"qGroupFallbackTitles": [
"Country label",
"Region label",
"City label"
],
"qGroupPos": 2,
"qStateCounts": {
"qLocked": 0,
"qSelected": 0,
"qOption": 387,
"qDeselected": 0,
"qAlternative": 0,
"qExcluded": 7145,
"qSelectedExcluded": 0,
"qLockedExcluded": 0
},
"qTags": [
"$text"
],
"qDimensionType": "D",
"qGrouping": "H",
"qNumFormat": {
"qType": "R",
"qnDec": 14,
"qUseThou": 1,
"qFmt": "##############",
"qDec": ".",
"qThou": ","
},
"qIsAutoFormat": true,
"qGroupFieldDefs": [
"Country",
"Region",
"City"
]
}
],
"qMeasureInfo": [
{
"qFallbackTitle": "Count([Golf Course])",
"qApprMaxGlyphCount": 3,
"qCardinal": 0,
"qSortIndicator": "A",
"qNumFormat": {
"qType": "I",
"qnDec": 0,
"qUseThou": 1,
"qFmt": "###0",
"qDec": "."
},
"qMin": 1,
"qMax": 14,
"qIsAutoFormat": true,
"qAttrExprInfo": []
}
],
"qEffectiveInterColumnSortOrder": [
0,
1
],
"qGrandTotalRow": [
{
"qText": "734",
"qNum": 734,
"qElemNumber": -1,
"qState": "X",
"qIsTotalCell": true
}
],
"qDataPages": [
{
"qMatrix": [
[
{
"qText": "Addison",
"qNum": "NaN",
"qElemNumber": 0,
"qState": "O"
},
{
"qText": "2",
"qNum": 2,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Chicago",
"qNum": "NaN",
"qElemNumber": 27,
"qState": "O"
},
{
"qText": "14",
"qNum": 14,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Winfield",
"qNum": "NaN",
"qElemNumber": 30,
"qState": "O"
},
{
"qText": "1",
"qNum": 1,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Raymond",
"qNum": "NaN",
"qElemNumber": 61,
"qState": "O"
},
{
"qText": "1",
"qNum": 1,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Monticello",
"qNum": "NaN",
"qElemNumber": 119,
"qState": "O"
},
{
"qText": "1",
"qNum": 1,
"qElemNumber": 0,
"qState": "L"
}
]
],
"qTails": [
{
"qUp": 0,
"qDown": 0
}
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 5
}
},
{
"qMatrix": [],
"qTails": [],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 0,
"qHeight": 0
}
}
],
"qPivotDataPages": [],
"qStackedDataPages": [],
"qMode": "S",
"qNoOfLeftDims": -1
}
}
}
}
The first five cities are displayed (the first five values are returned because qHeight was set to 5 in the initial data fetch when creating the chart).
6. Select the city Addison in order to drill-down one level. The first value (qValues is 0) of the field City (qDimNo is 0) is selected.
The client sends:
{
"jsonrpc": "2.0",
"id": 6,
"method": "SelectHyperCubeValues",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
[
0
],
false
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 6,
"result": {
"qSuccess": true
},
"change": [
2
]
}
The city Addison is successfully selected.
7. Get the layout of the chart.
The client sends:
{
"jsonrpc": "2.0",
"id": 7,
"method": "GetLayout",
"handle": 2,
"params": []
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 7,
"result": {
"qLayout": {
"qInfo": {
"qId": "fde3e2ca-c691-496b-87a7-5a20ecd0f42a",
"qType": "Chart"
},
"qSelectionInfo": {},
"qHyperCube": {
"qStateName": "$",
"qSize": {
"qcx": 2,
"qcy": 1
},
"qDimensionInfo": [
{
"qFallbackTitle": "City label",
"qApprMaxGlyphCount": 30,
"qCardinal": 7532,
"qSortIndicator": "N",
"qGroupFallbackTitles": [
"Country label",
"Region label",
"City label"
],
"qGroupPos": 2,
"qStateCounts": {
"qLocked": 0,
"qSelected": 1,
"qOption": 0,
"qDeselected": 0,
"qAlternative": 386,
"qExcluded": 7145,
"qSelectedExcluded": 0,
"qLockedExcluded": 0
},
"qTags": [
"$text"
],
"qDimensionType": "D",
"qGrouping": "H",
"qNumFormat": {
"qType": "R",
"qnDec": 14,
"qUseThou": 1,
"qFmt": "##############",
"qDec": ".",
"qThou": ","
},
"qIsAutoFormat": true,
"qGroupFieldDefs": [
"Country",
"Region",
"City"
]
}
],
"qMeasureInfo": [
{
"qFallbackTitle": "Count([Golf Course])",
"qApprMaxGlyphCount": 1,
"qCardinal": 0,
"qSortIndicator": "A",
"qNumFormat": {
"qType": "I",
"qnDec": 0,
"qUseThou": 1,
"qFmt": "###0",
"qDec": "."
},
"qMin": 2,
"qMax": 2,
"qIsAutoFormat": true,
"qAttrExprInfo": []
}
],
"qEffectiveInterColumnSortOrder": [
0,
1
],
"qGrandTotalRow": [
{
"qText": "2",
"qNum": 2,
"qElemNumber": -1,
"qState": "X",
"qIsTotalCell": true
}
],
"qDataPages": [
{
"qMatrix": [
[
{
"qText": "Addison",
"qNum": "NaN",
"qElemNumber": 0,
"qState": "S"
},
{
"qText": "2",
"qNum": 2,
"qElemNumber": 0,
"qState": "L"
}
]
],
"qTails": [
{
"qUp": 0,
"qDown": 0
}
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 1
}
},
{
"qMatrix": [],
"qTails": [],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 0,
"qHeight": 0
}
}
],
"qPivotDataPages": [],
"qStackedDataPages": [],
"qMode": "S",
"qNoOfLeftDims": -1
}
}
}
}
The city Addison is returned.
8. Drill up one level (qNbrSteps is 1) in the first dimension of the chart (qDimNo is 0).
The client sends:
{
"jsonrpc": "2.0",
"id": 8,
"method": "DrillUp",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
1
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 8,
"result": {},
"change": [
2
]
}
9. Get the layout of the chart. The regions in the United States should be returned.
The client sends:
{
"jsonrpc": "2.0",
"id": 9,
"method": "GetLayout",
"handle": 2,
"params": []
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 9,
"result": {
"qLayout": {
"qInfo": {
"qId": "fde3e2ca-c691-496b-87a7-5a20ecd0f42a",
"qType": "Chart"
},
"qSelectionInfo": {},
"qHyperCube": {
"qStateName": "$",
"qSize": {
"qcx": 2,
"qcy": 44
},
"qDimensionInfo": [
{
"qFallbackTitle": "Region label",
"qApprMaxGlyphCount": 22,
"qCardinal": 240,
"qSortIndicator": "N",
"qGroupFallbackTitles": [
"Country label",
"Region label",
"City label"
],
"qGroupPos": 1,
"qStateCounts": {
"qLocked": 0,
"qSelected": 0,
"qOption": 44,
"qDeselected": 0,
"qAlternative": 0,
"qExcluded": 196,
"qSelectedExcluded": 0,
"qLockedExcluded": 0
},
"qTags": [
"$text"
],
"qDimensionType": "D",
"qGrouping": "H",
"qNumFormat": {
"qType": "R",
"qnDec": 14,
"qUseThou": 1,
"qFmt": "##############",
"qDec": ".",
"qThou": ","
},
"qIsAutoFormat": true,
"qGroupFieldDefs": [
"Country",
"Region",
"City"
]
}
],
"qMeasureInfo": [
{
"qFallbackTitle": "Count([Golf Course])",
"qApprMaxGlyphCount": 5,
"qCardinal": 0,
"qSortIndicator": "A",
"qNumFormat": {
"qType": "I",
"qnDec": 0,
"qUseThou": 1,
"qFmt": "###0",
"qDec": "."
},
"qMin": 20,
"qMax": 1310,
"qIsAutoFormat": true,
"qAttrExprInfo": []
}
],
"qEffectiveInterColumnSortOrder": [
0,
1
],
"qGrandTotalRow": [
{
"qText": "14521",
"qNum": 14521,
"qElemNumber": -1,
"qState": "X",
"qIsTotalCell": true
}
],
"qDataPages": [
{
"qMatrix": [
[
{
"qText": "Illinois",
"qNum": "NaN",
"qElemNumber": 0,
"qState": "O"
},
{
"qText": "734",
"qNum": 734,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Pennsylvania",
"qNum": "NaN",
"qElemNumber": 1,
"qState": "O"
},
{
"qText": "757",
"qNum": 757,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "California",
"qNum": "NaN",
"qElemNumber": 2,
"qState": "O"
},
{
"qText": "1072",
"qNum": 1072,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Alabama",
"qNum": "NaN",
"qElemNumber": 3,
"qState": "O"
},
{
"qText": "290",
"qNum": 290,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Florida",
"qNum": "NaN",
"qElemNumber": 4,
"qState": "O"
},
{
"qText": "1310",
"qNum": 1310,
"qElemNumber": 0,
"qState": "L"
}
]
],
"qTails": [
{
"qUp": 0,
"qDown": 0
}
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 5
}
},
{
"qMatrix": [],
"qTails": [],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 0,
"qHeight": 0
}
}
],
"qPivotDataPages": [],
"qStackedDataPages": [],
"qMode": "S",
"qNoOfLeftDims": -1
}
}
}
}
The first five regions are displayed (the first five values are returned because qHeight was set to 5 in the initial data fetch when creating the chart).
10. Drill up four levels (qNbrSteps is 4).
The client sends:
{
"jsonrpc": "2.0",
"id": 10,
"method": "DrillUp",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
4
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 10,
"result": {},
"change": [
2
]
}
The number of steps to drill-up is above the number of available levels. Therefore, the drill-up will point to the first level of the hierarchy (which is Country in this drill-down group).
11. Get the layout of the chart. The list of countries in the app should be returned.
The client sends:
{
"jsonrpc": "2.0",
"id": 11,
"method": "GetLayout",
"handle": 2,
"params": []
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 11,
"result": {
"qLayout": {
"qInfo": {
"qId": "fde3e2ca-c691-496b-87a7-5a20ecd0f42a",
"qType": "Chart"
},
"qSelectionInfo": {},
"qHyperCube": {
"qStateName": "$",
"qSize": {
"qcx": 2,
"qcy": 22
},
"qDimensionInfo": [
{
"qFallbackTitle": "Country label",
"qApprMaxGlyphCount": 14,
"qCardinal": 22,
"qSortIndicator": "N",
"qGroupFallbackTitles": [
"Country label",
"Region label",
"City label"
],
"qGroupPos": 0,
"qStateCounts": {
"qLocked": 0,
"qSelected": 0,
"qOption": 22,
"qDeselected": 0,
"qAlternative": 0,
"qExcluded": 0,
"qSelectedExcluded": 0,
"qLockedExcluded": 0
},
"qTags": [
"$ascii",
"$text"
],
"qDimensionType": "D",
"qGrouping": "H",
"qNumFormat": {
"qType": "R",
"qnDec": 14,
"qUseThou": 1,
"qFmt": "##############",
"qDec": ".",
"qThou": ","
},
"qIsAutoFormat": true,
"qGroupFieldDefs": [
"Country",
"Region",
"City"
]
}
],
"qMeasureInfo": [
{
"qFallbackTitle": "Count([Golf Course])",
"qApprMaxGlyphCount": 5,
"qCardinal": 0,
"qSortIndicator": "A",
"qNumFormat": {
"qType": "I",
"qnDec": 0,
"qUseThou": 1,
"qFmt": "###0",
"qDec": "."
},
"qMin": 2,
"qMax": 14521,
"qIsAutoFormat": true,
"qAttrExprInfo": []
}
],
"qEffectiveInterColumnSortOrder": [
0,
1
],
"qGrandTotalRow": [
{
"qText": "17891",
"qNum": 17891,
"qElemNumber": -1,
"qState": "X",
"qIsTotalCell": true
}
],
"qDataPages": [
{
"qMatrix": [
[
{
"qText": "United States",
"qNum": "NaN",
"qElemNumber": 0,
"qState": "O"
},
{
"qText": "14521",
"qNum": 14521,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Denmark",
"qNum": "NaN",
"qElemNumber": 1,
"qState": "O"
},
{
"qText": "29",
"qNum": 29,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Spain",
"qNum": "NaN",
"qElemNumber": 2,
"qState": "O"
},
{
"qText": "130",
"qNum": 130,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Canada",
"qNum": "NaN",
"qElemNumber": 3,
"qState": "O"
},
{
"qText": "2070",
"qNum": 2070,
"qElemNumber": 0,
"qState": "L"
}
],
[
{
"qText": "Netherlands",
"qNum": "NaN",
"qElemNumber": 4,
"qState": "O"
},
{
"qText": "50",
"qNum": 50,
"qElemNumber": 0,
"qState": "L"
}
]
],
"qTails": [
{
"qUp": 0,
"qDown": 0
}
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 5
}
},
{
"qMatrix": [],
"qTails": [],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 0,
"qHeight": 0
}
}
],
"qPivotDataPages": [],
"qStackedDataPages": [],
"qMode": "S",
"qNoOfLeftDims": -1
}
}
}
}
Data at the country's level are displayed.
The number of steps (4) was more than the levels available. The engine has returned to the first level of the drill down group.