Select some values in a pivot table by using the SelectPivotCells method. The type of the cells to select should be D (for data).
Example
The hypercube is defined as follows:
"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": ""
}
}
],
"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,
3,
2
]
}
Regarding the above definition, the hypercube has the following properties:
- Is 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).
- The cells are always fully expanded ("qAlwaysFullyExpanded": true).
- 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)).
1. Retrieve the values of the pivot table.
The client sends:
{
"jsonrpc": "2.0",
"id": 3,
"method": "GetHyperCubePivotData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 5,
"qWidth": 5
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"qDataPages": [
{
"qLeft": [
{
"qText": "Beverages",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Chartreuse verte",
"qElemNo": 2,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Alfreds Futterkiste",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Lakkalikööri",
"qElemNo": 8,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Alfreds Futterkiste",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
}
]
},
{
"qText": "Condiments",
"qElemNo": 1,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "\"Grandma's Boysenberry Spread\"",
"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": "Original Frankfurter grüne Soße",
"qElemNo": 9,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Alfreds Futterkiste",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": []
}
]
},
{
"qText": "Vegie-spread",
"qElemNo": 6,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Alfreds Futterkiste",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"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": "424,8",
"qNum": 424.79999999999995,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
],
[
{
"qText": "1416,35",
"qNum": 1416.35,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
],
[
{
"qText": "1110,96",
"qNum": 1110.96,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
],
[
{
"qText": "2027,376",
"qNum": 2027.376,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
],
[
{
"qText": "3420,005",
"qNum": 3420.005,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
]
],
"qArea": {
"qLeft": 0,
"qTop": 0,
"qWidth": 2,
"qHeight": 5
}
}
]
}
}
The value 1416,35 is located on the second row in the data matrix.
2. Select the second value which is 1416,35. The type of cells to select is D (qType). This value is located on the second row (qRow is 1) and first column (qCol is 0) of the data matrix.
The client sends:
{
"jsonrpc": "2.0",
"id": 4,
"method": "SelectPivotCells",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qType": "D",
"qRow": 1,
"qCol": 0
}
],
false
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 4,
"result": {
"qSuccess": true
},
"change": [
2
]
}
The value 1416,35 is selected.
3. Retrieve the values of the pivot table. The paging is composed of the first 5 lines (qHeight is 5) and 4 columns (qWidth is 4) starting from the top left (qTop is 0 and qLeft is 0).
The client sends:
{
"jsonrpc": "2.0",
"id": 5,
"method": "GetHyperCubePivotData",
"handle": 2,
"params": [
"/qHyperCubeDef",
[
{
"qTop": 0,
"qLeft": 0,
"qHeight": 5,
"qWidth": 4
}
]
]
}
The engine returns:
{
"jsonrpc": "2.0",
"id": 5,
"result": {
"qDataPages": [
{
"qLeft": [
{
"qText": "Beverages",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Lakkalikööri",
"qElemNo": 8,
"qValue": "NaN",
"qType": "N",
"qUp": 0,
"qDown": 0,
"qSubNodes": [
{
"qText": "Alfreds Futterkiste",
"qElemNo": 0,
"qValue": "NaN",
"qType": "N",
"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": "1416,35",
"qNum": 1416.35,
"qType": "V"
},
{
"qText": "-",
"qNum": "NaN",
"qType": "U"
}
]
]
}
]
}
}
qLeft returns the following left dimension cells: Beverages, Lakkalikööri and Alfreds Futterkiste.
qTop returns the following top dimension cell: Berlin.
qData returns the calculated data related to the product Lakkalikööri, the customer Alfreds Futterkiste and the city Berlin, which is: 1416,35.
For more information on the returned parameters, see GetHyperCubePivotData method.