In this example,the definition of the hypercube is the following:
"qHyperCubeDef": {
"qDimensions": [
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"CategoryName"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
},
"qOtherTotalSpec": {
"qOtherMode": "OTHER_COUNTED",
"qOtherCounted": {
"qv": "2"
},
"qOtherLimit": {
"qv": "0.05"
},
"qOtherLimitMode": "OTHER_GT_LIMIT",
"qSuppressOther": false,
"qForceBadValueKeeping": true,
"qApplyEvenWhenPossiblyWrongResult": true,
"qGlobalOtherGrouping": false,
"qOtherCollapseInnerDimensions": false,
"qOtherSortMode": "OTHER_SORT_DESCENDING",
"qTotalMode": "TOTAL_OFF",
"qReferencedExpression": {
"qv": ""
}
},
"qOtherLabel": "Others",
"qTotalLabel": ""
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"ProductName"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
},
"qOtherTotalSpec": {
"qOtherMode": "OTHER_OFF",
"qTotalMode": "TOTAL_OFF"
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"City"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
},
"qOtherTotalSpec": {
"qOtherMode": "OTHER_OFF",
"qTotalMode": "TOTAL_OFF"
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"Customer"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
},
"qOtherTotalSpec": {
"qOtherMode": "OTHER_OFF",
"qTotalMode": "TOTAL_OFF"
}
}
],
"qMeasures": [
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": "Sum(OrderTotal)"
}
},
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": ""
}
}
],
"qMode": "EQ_DATA_MODE_PIVOT",
"qAlwaysFullyExpanded": false,
"qInitialDataFetch": [],
"qSuppressZero": true,
"qSuppressMissing": true,
"qNoOfLeftDims": 3,
"qInterColumnSortOrder": [
0,
1,
3,
2
],
"qSortPivotByYValue": []
}
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 measure is Sum(OrderTotal).
- The inter column sort order is [0,1, 3, 2].
- Contains 3 left dimensions ("qNoOfLeftDims": 3).
- CategoryName, ProductName and Customer are left dimensions (according to the inter column sort order and the number of left dimensions).
- City is a top dimension (according to the inter column sort order (qInterColumnSortOrder ) and the number of left dimensions (qNoOfLeftDims)).
- In the first dimension, qOtherTotalSpec/qOtherMode is set to OTHER_COUNTED so that the values are limited to a fixed number.
-
In the first dimension, qOtherTotalSpec/qOtherCounted is set to 2 to limit the number of values to 2.
- In the first dimension, qOtherTotalSpec/qOtherSortMode is set to OTHER_SORT_DESCENDING so that the order of the values is descending.
- In the first dimension, qOtherLabel is set to Others so that all values except the biggest value are aggregated in the group Others.
The hypercube contains the following data, as shown in the representation below:
Example data
| Field: CategoryName |
City: Berlin |
| Beverages |
1841,15 |
| Condiments |
6558,341 |
| Dairy Products |
3116,484 |
| Produce |
1606,3 |
| Seafood |
6005,1675 |
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": "Condiments",
"qElemNo": 1,
"qValue": "NaN",
"qCanExpand": true,
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Others",
"qElemNo": -3,
"qValue": "NaN",
"qCanExpand": true,
"qType": "O",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
],
"qTop": [
{
"qText": "Berlin",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Sum(OrderTotal)",
"qElemNo": 0,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qElemNo": 1,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
}
],
"qData": [
[
{
"qText": "6558,341",
"qNum": 6558.3410000000013,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
],
[
{
"qText": "12569,1015",
"qNum": 12569.1015,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
]
]
}
]
}
}
Only the biggest value 6558,341 that corresponds to the category Condiments and the value associated to the Others group are returned in qData.
For more information on the returned parameters, see GetHyperCubePivotData method.
