How to create SSRS report with multiple independent groupings

Let's say we need to print for selected sales orders all their items with sales prices and barcodes. It means our report must look like this.

One page by SalesId, some kind of a card with all items presented in the related SalesLine table. Then for each ItemId we group all its sales prices, first, and all its barcodes, second.

As suggested by Best Practice, we can create one flat temporary table (regular in my case just to ease debugging) to merge all data into one data source for our report.

Additional GroupType field is populated with different values for each table: SalesLine, Barcode, or SalesLine (could be done as enum). They will be used in report design for filtering tablix elements.

As to design we would need the main TablixSalesId with grouping by SalesId and breaking page for each instance.

Then nested TablixItemId grouped by ItemId.

Now we need to add two new ones - TablixBarcode and TablixSalesPrice without any additional grouping but with filters as follows.

This approach with adding "artificial" field to a flat table let to distinguish data and easily group and filter them.

Feel free to download the full project here.

This is the code of two principal methods of  Report data provider class.

[SysEntryPointAttribute(false)]
public void processReport()
{
    QueryRun                    qr;
    SalesTable                  salesTable;

    tmpItemSalesPriceAndBarcode.setConnection(this.parmUserConnection());
    barcode = Barcode::construct(BarcodeType::EAN128);
    // Run the query
    qr = new QueryRun(this.parmQuery());
    while(qr.next())
    {
        if(qr.changed(tableNum(SalesTable)))
        {
            salesTable   = qr.get(tableNum(SalesTable));
            this.settmpItemSalesPriceAndBarcode(salesTable);
        }
    }
}

private void settmpItemSalesPriceAndBarcode(SalesTable _salesTable)
{
    #define.TypeSalesLine('SalesLine')
    #define.TypeBarcode('Barcode')
    #define.TypePurchPrice('SalesPrice')

    SalesLine                       salesLine;
    PriceDiscTable                  priceDiscTable;
    InventTable                     inventTable;
    InventItemBarcode               itemBarcode;
    BarCodeString                   barCodeString;

    if(_salesTable.SalesId)
    {
        // all sales line items
        while select ItemId from SalesLine
            group by ItemId
            where salesLine.SalesId == _salesTable.SalesId
        {
            tmpItemSalesPriceAndBarcode.clear();
            tmpItemSalesPriceAndBarcode.SalesId                     = _salesTable.SalesId;
            tmpItemSalesPriceAndBarcode.ItemId                      = salesLine.ItemId;
            tmpItemSalesPriceAndBarcode.GroupType                   = #TypeSalesLine;
            tmpItemSalesPriceAndBarcode.insert();
            // now for each item
            // barcodes
            while select ItemBarcode, RetailVariantId from itemBarcode
                where itemBarcode.ItemId == salesLine.ItemId
            {
                tmpItemSalesPriceAndBarcode.clear();
                tmpItemSalesPriceAndBarcode.SalesId                     = _salesTable.SalesId;
                tmpItemSalesPriceAndBarcode.ItemId                      = salesLine.ItemId;
                tmpItemSalesPriceAndBarcode.GroupType                   = #TypeBarcode;
                tmpItemSalesPriceAndBarcode.ItemBarcode                 = barcode.encodeString(itemBarcode.ItemBarcode);
                tmpItemSalesPriceAndBarcode.RetailVariantId             = itemBarcode.RetailVariantId;
                tmpItemSalesPriceAndBarcode.insert();
            }

            // purch prices
            while select Amount, FromDate, ToDate, UnitId, Currency from priceDiscTable
                where priceDiscTable.ItemRelation   == salesLine.ItemId
                &&    priceDiscTable.ItemCode       == TableGroupAll::Table
                &&    priceDiscTable.Module         == ModuleInventCustVend::Cust
            {
                tmpItemSalesPriceAndBarcode.clear();
                tmpItemSalesPriceAndBarcode.SalesId                     = _salesTable.SalesId;
                tmpItemSalesPriceAndBarcode.ItemId                      = salesLine.ItemId;
                tmpItemSalesPriceAndBarcode.GroupType                   = #TypePurchPrice;
                tmpItemSalesPriceAndBarcode.Amount                      = priceDiscTable.Amount;
                tmpItemSalesPriceAndBarcode.ToDate                      = priceDiscTable.ToDate;
                tmpItemSalesPriceAndBarcode.FromDate                    = priceDiscTable.FromDate;
                tmpItemSalesPriceAndBarcode.UnitId                      = priceDiscTable.UnitId;
                tmpItemSalesPriceAndBarcode.Currency                    = priceDiscTable.Currency;
                tmpItemSalesPriceAndBarcode.insert();
            }
        }
    }
}