Select some values in a pivot table

Overview

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 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": "" } } ], "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 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).
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)).

The hypercube contains the following data, as shown in the representation below:

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here