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": [
"ProductName"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 0,
"qSortByAscii": 1
}
],
"qReverseSort": false
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"City"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 0,
"qSortByAscii": 1
}
],
"qReverseSort": false
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"Customer"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 0,
"qSortByAscii": 1
}
],
"qReverseSort": false
}
}
],
"qMeasures": [
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": "Sum(OrderTotal)"
}
},
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": "Count(OrderTotal)"
}
}
],
"qMode": "EQ_DATA_MODE_PIVOT",
"qAlwaysFullyExpanded": true,
"qInitialDataFetch": [
{
"qTop": 0,
"qLeft": 0,
"qHeight": 100,
"qWidth": 100
}
],
"qSuppressZero": true,
"qSuppressMissing": true,
"qNoOfLeftDims": 3,
"qInterColumnSortOrder": [
0,
-1,
1,
2,
3
]
}
Regarding the above definition, the hypercube has the following properties:
- Is handled as a pivot table ("qMode": "EQ_DATA_MODE_PIVOT")
- Has four dimensions defined in the following order: CategoryName, ProductName, City and Customer.
-
The measures are Sum(OrderTotal) and Count(OrderTotal).
- Contains 3 left dimensions ("qNoOfLeftDims": 3) and one pseudo dimension (since the pivot table contains two measures).
- The cells are always fully expanded ("qAlwaysFullyExpanded": true).
-
The inter column sort order is [0, -1, 1, 2, 3] which means that the pseudo dimension is at position 1.
- CategoryName, ProductName and the pseudo dimension are left dimensions.
- City and Customer are top dimensions.
1. Get the values of the pivot table. In this example, the handle of the pivot table is 2 and the paging is composed of the first 5 lines (qHeight is 5) and 4 columns (qWidth is 4) starting from the top left (qTop and qLeft are 0).
The client sends:
{
"jsonrpc": "2.0",
"id": 3,
"method": "GetHyperCubePivotData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 5,
"qWidth": 4
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"qDataPages": [
{
"qLeft": [
{
"qText": "Beverages",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 19,
"qSubNodes": [
{
"qText": "Sum(OrderTotal)",
"qElemNo": 0,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 7,
"qSubNodes": [
{
"qText": "Chai",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Chang",
"qElemNo": 1,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Chartreuse verte",
"qElemNo": 37,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Côte de Blaye",
"qElemNo": 36,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Guaraná Fantástica",
"qElemNo": 22,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
}
]
}
],
"qTop": [
{
"qText": "Aachen",
"qElemNo": 2,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Drachenblut Delikatessen",
"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": "Alfreds Futterkiste",
"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": "Königlich Essen",
"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": "QUICK-Stop",
"qElemNo": 8,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
}
],
"qData": [
[
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "903412,665",
"qNum": 903412.66499999876,
"qType": "V"
}
],
[
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "5210703,507",
"qNum": 5210703.50699998,
"qType": "V"
}
],
[
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "83998,68",
"qNum": 83998.679999999949,
"qType": "V"
},
{
"qText": "428588,02",
"qNum": 428588.01999999955,
"qType": "V"
},
{
"qText": "218975,5575",
"qNum": 218975.55749999997,
"qType": "V"
}
],
[
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "235155,06",
"qNum": 235155.06000000006,
"qType": "V"
},
{
"qText": "2001569,64",
"qNum": 2001569.6400000076,
"qType": "V"
}
],
[
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
},
{
"qText": "435941,856",
"qNum": 435941.85599999951,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
]
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 4,
"qHeight": 5
}
}
]
}
}
qLeft returns the following left dimension values: Beverages, Chai, Chang, Chartreuse verte, Côte de Blaye and Guaraná Fantástica. The type (qType) of these values is N since they are dimension values. Sum(OrderTotal) is a pseudo dimension (qType is P).
qTop returns the following top dimension values: Aachen, Drachenblut Delikatessen, Berlin, Alfreds Futterkiste, Brandenburg, Königlich Essen, Cunewalde, QUICK-Stop. The type (qType) of these values is N since they are dimension values.
qData returns the calculated data related to the top and left dimensions. The type of a calculated value (qType) is V. If the value is Null, the type is U.
For Beverages, qUp is set to 0 since the page starts from the top. qDown is set to 19 since there are 24 rows in Beverages (24-5).
For Sum(OrderTotal), qUp is set to 0 since the page starts from the top. qDown is set to 7 since there are 12 rows in Sum(OrderTotal)(12-5).
For more information on the returned parameters, see GetHyperCubePivotData method.
