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.

How to get rid of hyper links in SSRS report

As we remember AX 2012 allows us to jump directly to the details based on EDT or table relations. Sometimes this weight a report that supposed to be saved in Excel, for example.

It is easy to suppress hyper links in Visual Studio by changing field property in design.

Happy DAXing!

How to set properties for the Reference Group form control from code

There is a small issue in AX 2012 with getting access to the siblings' properties of Reference Group form control - they are unavailable in AOT.

However, you still can get access to it from code during run time.

Let's say you have placed on your form a field named FilterCategory, which is a reference group, and you want to set its sibling FilterCategory_Name width to Column width value. As you can see there is no way to do that in AOT.

So you just create a method supposed to be called in the form init():

void setColumnWidthForFilterCategory()
{
    int                                 i;
    Object                              childControl;

    for (i = 1; i <= FilterCategory.controlCount(); i++) // FilterCategory is of FormReferenceGroupControl type
    {
        childControl = FilterCategory.controlNum( i );
        childControl.width( 0, FormWidth::ColumnWidth );
    }
}

And you get it!