Conditional grouping report

Tumblewood

Hi,

I'm having trouble with conditional grouping of a tablix in a NAV RDLC layout.

If the SalesInvoiceLine type is an Item, I want to group based on the No. If the type is something else (general ledger) I don't want to group and just display every line.

The group on expression I'm using is:

=IIf(Fields!Type_SalesInvoiceLine.Value = "Item", Fields!No_SalesInvoiceLine.Value, "")

But this isn't working. Can someone help me find the correct way?

Thanks!

Miklos_Hollender

I cannot give you the full answer, but I recommend moving as much of the logic as possible to NAV side as opposed to the RDLC side. There are two reasons for this, one good and one maybe not so good. The good is that you can see the dataset by running the report, preview, go to help/about, then running it again and going there again. This is far easier than debugging the report so it makes sense that the data should be smart and the report stupid. The second is that I understand C/AL coding well but never really understood RDLC grouping well.

So you could try to add code to the report, OnAfterGetRecord, something like IF ... THEN GroupIt := TRUE ELSE GroupIt := FALSE where GroupIt is a boolean variable also added to the dataset somewhere in the lines.

But my best guess would be that it is not going to work this way. You could perhaps experiment with adding both grouped and not grouped lines and FILTERING on this field. Maybe it works.

I think I would probably stick to doing it all on NAV side as it is more testable and easier (for me). I would probably turn the sales invoice line into a temp table and write code to fill it out conditionally, copy item lines into it 1:1 while copy other lines summed up. So then the report runs on a sales inv line temp table that is already correctly filled out and no RDLC side logic is needed.

rishi_rishabh123

I think If you make visibility of group header and footer false when you don’t want group data then it will only show detail section which is flat data...hope this idea works.

Tumblewood

I fixed it by using SWITCH in the grouping expression:

=Cstr(SWITCH(
Fields!Type_SalesInvoiceLine.Value = "Item", Fields!No_SalesInvoiceLine.Value,
Fields!Type_SalesInvoiceLine.Value = "Artikel", Fields!No_SalesInvoiceLine.Value,
1=1, Fields!LineNo_SalesInvoiceLine.Value
))

So if the SalesInvLine of type Item is (or Artikel in Dutch), the grouping is done by Item No.
If these are not true, grouping is done by LineNo.