Select some dimension cells in a pivot table

Select some dimension cells of a pivot table by using the SelectPivotCells method. The type of the cells to select should be either L (for left dimensions cells) or T (for top dimension cells).

Examples

Example 1:

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": "Count(OrderTotal)" } } ], "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, 1, 2, 3 ] }

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 3 left dimensions ("qNoOfLeftDims": 3) and one pseudo dimension (since the pivot table contains two measures).
The cells are always fully expanded ("qAlwaysFullyExpanded": true).
The inter column sort order is [0, -1, 1, 2, 3] which means that the pseudo dimension is at position 1.
CategoryName, ProductName and the pseudo dimension are left dimensions.
City and Customer are top dimensions.

1. Select the product Chang. Chang is a left dimension value (qType is L). qCol is set to 2 to select the left dimension ProductName. qRow is set to 1 to select the second product name value.

The client sends:

{ "jsonrpc": "2.0", "id": 4, "method": "SelectPivotCells", "handle": 2, "params": [ "/qHyperCubeDef", [ { "qType": "L", "qRow": 1, "qCol": 2 } ], false ] }

The engine returns:

{ "jsonrpc": "2.0", "id": 4, "result": { "qSuccess": true }, "change": [ 2 ] }

The product Chang is selected.

2. Retrieve the values of the pivot table. The paging is composed of the first 5 lines (qHeight is 5) and 5 columns (qWidth is 5) 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": 5 } ] ] }

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": "Sum(OrderTotal)", "qElemNo": 0, "qValue": "NaN", "qType": "P", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Chang", "qElemNo": 1, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] }, { "qText": "Count(OrderTotal)", "qElemNo": 1, "qValue": "NaN", "qType": "P", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Chang", "qElemNo": 1, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] } ] } ], "qTop": [ { "qText": "Cunewalde", "qElemNo": 8, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "QUICK-Stop", "qElemNo": 8, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] }, { "qText": "Frankfurt a.M.", "qElemNo": 5, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Lehmanns Marktstand", "qElemNo": 5, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] }, { "qText": "Köln", "qElemNo": 7, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Ottilies Käseladen", "qElemNo": 7, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] }, { "qText": "München", "qElemNo": 3, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Frankenversand", "qElemNo": 3, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] }, { "qText": "Stuttgart", "qElemNo": 10, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Die Wandernde Kuh", "qElemNo": 10, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] } ], "qData": [ [ { "qText": "5210703,507", "qNum": 5210703.50699998, "qType": "V" }, { "qText": "502119,80999999", "qNum": 502119.80999999418, "qType": "V" }, { "qText": "403288,2", "qNum": 403288.20000000007, "qType": "V" }, { "qText": "120674,505", "qNum": 120674.50500000012, "qType": "V" }, { "qText": "599513,0625", "qNum": 599513.06250000058, "qType": "V" } ], [ { "qText": "2197", "qNum": 2197, "qType": "V" }, { "qText": "837", "qNum": 837, "qType": "V" }, { "qText": "317", "qNum": 317, "qType": "V" }, { "qText": "190", "qNum": 190, "qType": "V" }, { "qText": "976", "qNum": 976, "qType": "V" } ] ], "qArea": { "qLeft": 0, "qTop": 0, "qWidth": 5, "qHeight": 2 } } ] } }

qLeft returns the following left dimension cells: Beverages, Sum(OrderTotal), Chang and Beverages, Count(OrderTotal), Chang.

qTop returns the following top dimension cells: München, Frankenversand, Frankfurt a.M., Lehmanns Marktstand, Köln, Ottilies Käseladen, München,Frankenversand, Cunewalde and QUICK-Stop.

qData returns the calculated data related to the product Chang.

For more information on the returned parameters, see GetHyperCubePivotData method.

3.Select the customer Ottilies Käseladen. Ottilies Käseladen is a top dimension value (qType is T). qRow is set to 1 to select the top dimension Customer. qCol is set to 2 to select the third customer value in the data matrix.

The client sends:

{ "jsonrpc": "2.0", "id": 7, "method": "SelectPivotCells", "handle": 2, "params": [ "/qHyperCubeDef", [ { "qType": "T", "qRow": 1, "qCol": 2 } ], false ] }

The engine returns:

{ "jsonrpc": "2.0", "id": 7, "result": { "qSuccess": true }, "change": [ 2 ] }

The customer Ottilies Käseladen is selected.

4. Retrieve the values of the pivot table. The paging is composed of the first 2 lines (qHeight is 2) and 4 columns (qWidth is 4) starting from the top left (qTop and qLeft).

The client sends:

{ "jsonrpc": "2.0", "id": 8, "method": "GetHyperCubePivotData", "handle": 2, "params": [ "/qHyperCubeDef", [ { "qTop": 0, "qLeft": 0, "qHeight": 2, "qWidth": 4 } ] ] }

The engine returns:

{ "jsonrpc": "2.0", "id": 8, "result": { "qDataPages": [ { "qLeft": [ { "qText": "Beverages", "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": [ { "qText": "Chang", "qElemNo": 1, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] }, { "qText": "Count(OrderTotal)", "qElemNo": 1, "qValue": "NaN", "qType": "P", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Chang", "qElemNo": 1, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] } ] } ], "qTop": [ { "qText": "Köln", "qElemNo": 7, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [ { "qText": "Ottilies Käseladen", "qElemNo": 7, "qValue": "NaN", "qType": "N", "qUp": 0, "qDown": 0, "qSubNodes": [] } ] } ], "qData": [ [ { "qText": "403288,2", "qNum": 403288.20000000007, "qType": "V" } ], [ { "qText": "317", "qNum": 317, "qType": "V" } ] ], "qArea": { "qLeft": 0, "qTop": 0, "qWidth": 1, "qHeight": 2 } } ] } }

qLeft returns the following left dimension cells: Beverages, Sum(OrderTotal), Beverages and Count(OrderTotal).

qTop returns the following top dimension cells: Köln and Ottilies Käseladen. The type (qType) of these values is N since they are dimension values.

qData returns the calculated data related to the customer Ottilies Käseladen. The type qType of the calculated values is V.

For more information on the returned parameters, see GetHyperCubePivotData method.

Example 2:

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. Select the product Lakkalikööri. Lakkalikööri is a left dimension cell (qType is L).qCol is set to 1 to select the left dimension ProductName; qRow is set to 1 to select the second product name value.

The client sends:

{ "jsonrpc": "2.0", "id": 4, "method": "SelectPivotCells", "handle": 2, "params": [ "/qHyperCubeDef", [ { "qType": "L", "qRow": 1, "qCol": 1 } ], false ] }

The engine returns:

{ "jsonrpc": "2.0", "id": 4, "result": { "qSuccess": true }, "change": [ 2 ] }

The product Lakkalikööri is selected.

2. 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:

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.

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