SSRS report grouping (AX 2012)

Question

I'm modifying the sales invoice report. What I want to do is to group lines by LineHeader. i.e. if invoice has 6 lines and 3 salesid, report should be like:

LineHeader {2 lines below}, LineHeader {2 lines below}, LineHeader {2 lines below}.

How to group to achieve it?

Answer 1

The sales invoice report has two datasets, one for the overall invoice header, and one for the lines. The way I understand your question, you would like a sub header within the invoice details.

If you look in the report design in Visual Studio, you will find a tablix that is associated with the SalesInvoiceDS dataset, which comes from the SalesInvoiceTmp temp table. if you highlight the tablix, you can see the groups defined within its dataset:

Right clicking on a group (table16_Details_group in the picture) and pressing "Group Properties..." brings up the Group Properties dialog box. from here you can add a group expression and set it to the whatever field you want to group by:

Note you will need to add the field to the salesInvoiceTmp table and modify the report data provider to populate it if it does not already exist. This is outside the scope of this answer, but there are numerous resources on the web and in books that show you how to do this (I recommend this book, I've found it very helpful with regard to the Report Data Provider framework.)

Now viewing the tablix with a field highlighted, you can see an orange bracket-like line to the left of each group. Everything within this bracket is repeated for every "Group" that the group by statement defines. You would then right click the Row group again, and press Add Group > Child Group, and set its group by statement to ItemId.

The bracket to the left will now have a smaller bracket within it. The outer bracket correlates to the salesId group, and the inner to the items within that group. The line header row would be inside the larger bracket but outside the smaller bracket, and will repeat for each SalesId, while the item details will be within the smaller, and repeat for each item. This is a bit difficult to describe in text, but you can always highlight a field, and the corresponding group will highlight as well.

Note that adding these groups to semi-complex tablices tends to mess them up, so it may be better to create one from scratch and define the groups before putting your data into it.