Sort the measure values in a stacked pivot table by setting the property qSortbyYValue in the definition of the hypercube to 1 or -1 for ascending and descending order respectively.
Example
The first step is to create a stacked pivot table with four dimensions and one measure.
1. Create the stacked pivot table.
The client sends:
{
"jsonrpc": "2.0",
"id": 2,
"method": "CreateSessionObject",
"handle": 1,
"params": [
{
"qInfo": {
"qId": "",
"qType": "Pivot"
},
"qHyperCubeDef": {
"qDimensions": [
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"CategoryName"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"ProductName"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"City"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
}
},
{
"qLibraryId": "",
"qDef": {
"qFieldDefs": [
"Customer"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByLoadOrder": 1
}
],
"qReverseSort": false
}
}
],
"qMeasures": [
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": "Sum(OrderTotal)"
}
}
],
"qMode": "EQ_DATA_MODE_PIVOT_STACK",
"qAlwaysFullyExpanded": false,
"qSuppressZero": true,
"qSuppressMissing": true,
"qNoOfLeftDims": -1,
"qInterColumnSortOrder": [],
"qSortbyYValue": -1
}
}
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 2,
"result": {
"qReturn": {
"qType": "GenericObject",
"qHandle": 2,
"qGenericType": "Pivot",
"qGenericId": "7a6d752a-66a2-4d57-a8b6-a11921f201bb"
}
},
"change": [
2
]
}
The stacked pivot table is created and its handle is 2.
Regarding the above definition, the hypercube has the following properties:
- Is handled as a stacked pivot table ("qMode": "EQ_DATA_MODE_PIVOT_STACK")
- Has four dimensions, defined in the following order: CategoryName, ProductName, City and Customer.
-
The measure is Sum(OrderTotal).
- The cells are not fully expanded ("qAlwaysFullyExpanded": false).
- The sorting by Y-value is set to descending order (qSortbyYValue is -1); this means that the values of the measure should be sorted by descending order.
2. Get the values of the stacked pivot table. The handle of the request is 2 because the stacked pivot table has 2 as a handle.
The client sends:
{
"jsonrpc": "2.0",
"id": 3,
"method": "GetHyperCubeStackData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 8,
"qWidth": 1
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"qDataPages": [
{
"qData": [
{
"qElemNo": 0,
"qValue": 0,
"qType": "R",
"qMaxPos": 215030477.16319269,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 0,
"qSubNodes": [
{
"qText": "Produce",
"qElemNo": 6,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 45991111.250000484,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 0,
"qSubNodes": [
{
"qText": "45991111,25",
"qElemNo": 0,
"qValue": 45991111.250000484,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 0,
"qSubNodes": []
}
]
},
{
"qText": "Dairy Products",
"qElemNo": 3,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 43272588.13275075,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 1,
"qSubNodes": [
{
"qText": "43272588,132751",
"qElemNo": 0,
"qValue": 43272588.13275075,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 1,
"qSubNodes": []
}
]
},
{
"qText": "Confections",
"qElemNo": 2,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 36315724.381800488,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 2,
"qSubNodes": [
{
"qText": "36315724,3818",
"qElemNo": 0,
"qValue": 36315724.381800488,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 2,
"qSubNodes": []
}
]
},
{
"qText": "Seafood",
"qElemNo": 7,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 28019883.027287755,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 3,
"qSubNodes": [
{
"qText": "28019883,027288",
"qElemNo": 0,
"qValue": 28019883.027287755,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 3,
"qSubNodes": []
}
]
},
{
"qText": "Beverages",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 26842276.009120665,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 4,
"qSubNodes": [
{
"qText": "26842276,009121",
"qElemNo": 0,
"qValue": 26842276.009120665,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 4,
"qSubNodes": []
}
]
},
{
"qText": "Grains/Cereals",
"qElemNo": 4,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 13686772.304500155,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 5,
"qSubNodes": [
{
"qText": "13686772,3045",
"qElemNo": 0,
"qValue": 13686772.304500155,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 5,
"qSubNodes": []
}
]
},
{
"qText": "Condiments",
"qElemNo": 1,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 13190686.128732197,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 6,
"qSubNodes": [
{
"qText": "13190686,128732",
"qElemNo": 0,
"qValue": 13190686.128732197,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 6,
"qSubNodes": []
}
]
},
{
"qText": "Meat/Poultry",
"qElemNo": 5,
"qValue": "NaN",
"qType": "N",
"qMaxPos": 7711435.9290001877,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 7,
"qSubNodes": [
{
"qText": "7711435,9290002",
"qElemNo": 0,
"qValue": 7711435.9290001877,
"qType": "V",
"qMaxPos": 0,
"qMinNeg": 0,
"qUp": 0,
"qDown": 0,
"qRow": 7,
"qSubNodes": []
}
]
}
]
}
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 1,
"qHeight": 8
}
}
]
}
}
The values of the measure are sorted by descending order.