When you iterate through a query result, you may often need to get all grouping values within another grouping. For example, you might need this for displaying a pivot table in a report. You can implement this using the third parameter of the Choose() function of the QueryResultSelection object.
Let us consider an example. Suppose you need to generate a pivot table of product balances in multiple warehouses. The products should be displayed in rows while warehouses should be represented by columns. A query that gets the balances has the following format:
SELECT ItemAccountingBalance.Items AS Items, ItemAccountingBalance.Items.Presentation, ItemAccountingBalance.Warehouse AS Warehouse, ItemAccountingBalance.Warehouse.Presentation, ItemAccountingBalance.QuantityBalance AS QuantityBalance FROM AccumulationRegister.ItemAccounting.Balance AS ItemAccountingBalance TOTALS SUM(QuantityBalance) BY OVERALL, Items, Warehouse
To iterate through products, use the selection from the query result:
ItemSelection = Result.Choose(QueryResultIteration.ByGroups, "Items"); While ItemSelection.Next() Do ItemsArea.Parameters.Fill(ItemSelection); SpreadsheetDoc.Put(ItemsArea);
To iterate through the products in all warehouses available in the query result, get a nested selection based on the product selection and specify All for the third parameter:
WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Warehouse", "All"); While WarehouseSelection.Next() Do WarehouseArea.Parameters.Fill(WarehouseSelection); Spreadsheet.Join(WarehouseArea); EndDo;
If you need to generate multiple independent pivot tables for each grouping value, in the third selection parameter specify the grouping name, so that the query retrieves values belonging to the grouping.
WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Warehouse", "Company");
This example gets the values of all the warehouses available in the selected company.Next page: Sorting in nested queries