Selection of all values belonging to a grouping from a query result

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.

Example:

WarehouseSelection = ItemSelection.Choose(QueryResultIteration.ByGroups, "Warehouse", "Company");

This example gets the values of all the warehouses available in the selected company.