The definition of the hypercube is the following:
"qHyperCubeDef": {
"qDimensions": [
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"CategoryName"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 0,
"qSortByAscii": 1
}
],
"qReverseSort": false
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"City"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 0,
"qSortByAscii": 1
}
],
"qReverseSort": false
}
}
],
"qMeasures": [
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": "Count(OrderTotal)"
}
}
],
"qMode": "EQ_DATA_MODE_PIVOT",
"qAlwaysFullyExpanded": false,
"qSuppressZero": true,
"qSuppressMissing": true,
"qNoOfLeftDims": -1,
"qInterColumnSortOrder": []
}
Regarding the above definition, the hypercube has the following properties:
- Is handled as a pivot table ("qMode": "EQ_DATA_MODE_PIVOT")
- The cells are not always fully expanded ("qAlwaysFullyExpanded": false).
- CategoryName and City are left dimensions (qNoOfLeftDims is -1).
- There is one measure: Count(OrderTotal).
1. Retrieve the data from the pivot table. In this example, the handle of the pivot table is 2 and the paging is composed of the first 100 lines (qHeight) and 100 columns (qWidth) starting from the top left (qTop and qLeft set to 0).
The client sends:
{
"jsonrpc": "2.0",
"id": 3,
"method": "GetHyperCubePivotData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 100,
"qWidth": 100
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"qDataPages": [
{
"qLeft": [
{
"qText": "Beverages",
"qElemNo": 0,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Condiments",
"qElemNo": 1,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Confections",
"qElemNo": 2,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Dairy Products",
"qElemNo": 3,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Grains/Cereals",
"qElemNo": 4,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Meat/Poultry",
"qElemNo": 5,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Produce",
"qElemNo": 6,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Seafood",
"qElemNo": 7,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
],
"qTop": [
{
"qText": "Count(OrderTotal)",
"qElemNo": 0,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
],
"qData": [
[
{
"qText": "28188",
"qNum": 28188,
"qType": "V"
}
],
[
{
"qText": "13298",
"qNum": 13298,
"qType": "V"
}
],
[
{
"qText": "26047",
"qNum": 26047,
"qType": "V"
}
],
[
{
"qText": "30217",
"qNum": 30217,
"qType": "V"
}
],
[
{
"qText": "13109",
"qNum": 13109,
"qType": "V"
}
],
[
{
"qText": "8011",
"qNum": 8011,
"qType": "V"
}
],
[
{
"qText": "8961",
"qNum": 8961,
"qType": "V"
}
],
[
{
"qText": "25121",
"qNum": 25121,
"qType": "V"
}
]
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 1,
"qHeight": 8
}
}
]
}
}
In qLeft are returned the following left dimension values: Beverages, Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce and Seefood. The cells are not expanded (qCanExpand is set to true). For each CategoryName value, some sub nodes exist.
qTop returns the measure Count(OrderTotal) because there is only one measure and no top dimension in the pivot table.
qData returns the calculated data.
For more information on the returned parameters, see GetHyperCubePivotData method.
2. Expand the cell Beverages. Beverages is the first category name in the list (qRow is 0). CategoryName is the first left dimension (qCol is 0).
The client sends:
{
"jsonrpc": "2.0",
"id": 4,
"method": "ExpandLeft",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
0,
false
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 4,
"result": {},
"change": [
2
]
}
The cell Beverages is expanded.
3. Retrieve the values of the pivot table. The paging is composed of the first 100 lines (qHeight) and 100 columns (qWidth) starting from the top left (qTop and qLeft set to 0).
The client sends:
{
"jsonrpc": "2.0",
"id": 5,
"method": "GetHyperCubePivotData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 100,
"qWidth": 100
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 5,
"result": {
"qDataPages": [
{
"qLeft": [
{
"qText": "Beverages",
"qElemNo": 0,
"qValue": "NaN",
"qCanCollapse": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Aachen",
"qElemNo": 2,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Berlin",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Brandenburg",
"qElemNo": 4,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Cunewalde",
"qElemNo": 8,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Frankfurt a.M.",
"qElemNo": 5,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Köln",
"qElemNo": 7,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Leipzig",
"qElemNo": 6,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Mannheim",
"qElemNo": 1,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "München",
"qElemNo": 3,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Münster",
"qElemNo": 9,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Stuttgart",
"qElemNo": 10,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Condiments",
"qElemNo": 1,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Confections",
"qElemNo": 2,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Dairy Products",
"qElemNo": 3,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Grains/Cereals",
"qElemNo": 4,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Meat/Poultry",
"qElemNo": 5,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Produce",
"qElemNo": 6,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Seafood",
"qElemNo": 7,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qElemNo": -4,
"qValue": "NaN",
"qType": "E",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
}
],
"qTop": [
{
"qText": "Count(OrderTotal)",
"qElemNo": 0,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
],
"qData": [
[
{
"qText": "2690",
"qNum": 2690,
"qType": "V"
}
],
[
{
"qText": "1144",
"qNum": 1144,
"qType": "V"
}
],
[
{
"qText": "2112",
"qNum": 2112,
"qType": "V"
}
],
[
{
"qText": "8473",
"qNum": 8473,
"qType": "V"
}
],
[
{
"qText": "3885",
"qNum": 3885,
"qType": "V"
}
],
[
{
"qText": "799",
"qNum": 799,
"qType": "V"
}
],
[
{
"qText": "1165",
"qNum": 1165,
"qType": "V"
}
],
[
{
"qText": "1759",
"qNum": 1759,
"qType": "V"
}
],
[
{
"qText": "2464",
"qNum": 2464,
"qType": "V"
}
],
[
{
"qText": "1484",
"qNum": 1484,
"qType": "V"
}
],
[
{
"qText": "2213",
"qNum": 2213,
"qType": "V"
}
],
[
{
"qText": "13298",
"qNum": 13298,
"qType": "V"
}
],
[
{
"qText": "26047",
"qNum": 26047,
"qType": "V"
}
],
[
{
"qText": "30217",
"qNum": 30217,
"qType": "V"
}
],
[
{
"qText": "13109",
"qNum": 13109,
"qType": "V"
}
],
[
{
"qText": "8011",
"qNum": 8011,
"qType": "V"
}
],
[
{
"qText": "8961",
"qNum": 8961,
"qType": "V"
}
],
[
{
"qText": "25121",
"qNum": 25121,
"qType": "V"
}
]
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 1,
"qHeight": 18
}
}
]
}
}
In qLeft are returned the sub nodes related to the left dimension value Beverages: Berlin, Mannheim, Aachen, München, Brandenburg, Frankfurt a.M., Leipzig, Köln, Cunewalde, Münster and Stuttgart .
The dimension values Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce and Seefood are still collapsed (qCanExpand is true).
qCanCollapse is set to true for Beverages since this dimension value can be collapsed.
qTop returns the measure Count(OrderTotal) because there is only one measure and no top dimension in the pivot table.
qData returns the calculated data.
For more information on the returned parameters, see GetHyperCubePivotData method.
4. Collapse the cell Beverages.
The client sends:
{
"jsonrpc": "2.0",
"id": 5,
"method": "CollapseLeft",
"handle": 2,
"params": [
"/qHyperCubeDef",
0,
0,
false
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 5,
"result": {},
"change": [
2
]
}
The cell Beverages is collapsed.