How to create SSRS report with multiple independent groupings

[Blog bot]

Источник: http://alexvoy.blogspot.com/2015/10/...-multiple.html
==============

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();
}
}
}
}

Источник: http://alexvoy.blogspot.com/2015/10/...-multiple.html