Sort the values of the first measure in a 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 pivot table with four dimensions and two measures.
1. Create the 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)"
}
},
{
"qLibraryId": "",
"qDef": {
"qLabel": "",
"qDef": "Count(OrderTotal)"
}
}
],
"qMode": "EQ_DATA_MODE_PIVOT",
"qAlwaysFullyExpanded": false,
"qInitialDataFetch": [],
"qSuppressZero": true,
"qSuppressMissing": true,
"qNoOfLeftDims": 4,
"qInterColumnSortOrder": [],
"qSortbyYValue": -1
}
}
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 2,
"result": {
"qReturn": {
"qType": "GenericObject",
"qHandle": 2,
"qGenericType": "Pivot",
"qGenericId": "b1c7020f-740f-44fb-8b0c-7e26a8585583"
}
},
"change": [
2
]
}
The pivot table is created and its handle is 2.
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 four left dimensions ("qNoOfLeftDims": 4) and one pseudo-dimension (because the pivot table contains two measures).
- The cells are not always fully expanded ("qAlwaysFullyExpanded": false).
- CategoryName, ProductName , City and Customer are left dimensions.
- The pseudo-dimension is a top dimension.
- The sorting by Y value is set to descending order (qSortbyYValue is -1); this means that the values of the first measure (Sum(OrderTotal)) should be sorted by descending order.
2. Get the layout of the pivot table. The handle of the request is 2 because the pivot table has 2 as a handle.
The client sends:
{
"jsonrpc": "2.0",
"id": 3,
"method": "GetHyperCubePivotData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 8,
"qWidth": 3
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"qDataPages": [
{
"qLeft": [
{
"qText": "Produce",
"qElemNo": 6,
"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": "Confections",
"qElemNo": 2,
"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": []
},
{
"qText": "Beverages",
"qElemNo": 0,
"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": "Condiments",
"qElemNo": 1,
"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": []
}
],
"qTop": [
{
"qText": "Sum(OrderTotal)",
"qElemNo": 0,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
},
{
"qText": "Count(OrderTotal)",
"qElemNo": 1,
"qValue": "NaN",
"qType": "P",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
],
"qData": [
[
{
"qText": "45991111,25",
"qNum": 45991111.250000484,
"qType": "V"
},
{
"qText": "8961",
"qNum": 8961,
"qType": "V"
}
],
[
{
"qText": "43272588,132751",
"qNum": 43272588.13275075,
"qType": "V"
},
{
"qText": "30217",
"qNum": 30217,
"qType": "V"
}
],
[
{
"qText": "36315724,3818",
"qNum": 36315724.381800488,
"qType": "V"
},
{
"qText": "26047",
"qNum": 26047,
"qType": "V"
}
],
[
{
"qText": "28019883,027288",
"qNum": 28019883.027287755,
"qType": "V"
},
{
"qText": "25121",
"qNum": 25121,
"qType": "V"
}
],
[
{
"qText": "26842276,009121",
"qNum": 26842276.009120665,
"qType": "V"
},
{
"qText": "28188",
"qNum": 28188,
"qType": "V"
}
],
[
{
"qText": "13686772,3045",
"qNum": 13686772.304500155,
"qType": "V"
},
{
"qText": "13109",
"qNum": 13109,
"qType": "V"
}
],
[
{
"qText": "13190686,128732",
"qNum": 13190686.128732197,
"qType": "V"
},
{
"qText": "13298",
"qNum": 13298,
"qType": "V"
}
],
[
{
"qText": "7711435,9290002",
"qNum": 7711435.9290001877,
"qType": "V"
},
{
"qText": "8011",
"qNum": 8011,
"qType": "V"
}
]
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 8
}
}
]
}
}
The values of the first measure are sorted by descending values.