Group Data in NAV 2009 reports using C/AL

Hello all,

How can I group data in NAV 2009 reports, using C/AL code?

I need to group sales based on “Extenal Document No.” field, from T. VAT Entry, for each day.So, the report should be grouped based on first character of External Document No. field on daily bases.

How can I do this?

BR.

Hello

There is no easy fix for this. You can group by a full field content example group by “External Document No.” but not only on one or a few characters in a field.

The best solution for this is actually to create a temporary table where you add the data that you want to get printed, and the base your report on this temporary table with the correct sorting key.

I did this:

VATEntry.SETCURRENTKEY(“Posting Date”, “External Document No.”);

VATEntry.SETRANGE(“Posting Date”,“Posting Date”);
VATEntry.SETRANGE(“External Document No.”, “External Document No.”);

Where : VATEntry - record - t. VAT Entry and put it as temporary?

Don’t know - You just have to make sure that you have all your records and then insert those records into a temporary table where you have the First character from External Document No copied to a new field and have that new field as part of a sorting key.

It might be possible to add that field to the VATEntry table and fill it out when creating the Record and finally create a new sorting key with that field.

Ok, I will try this. Thank you

Which kind of report is it? Classic or RDLC?

If RDLC, then an easy fix would be to copy the External Document No. in the dataset. Then change the source expression to COPYSTR(“Exernal Document No.”,1,2). Then you can do all the grouping very easily over in RDLC (one of the few things it’s very good at).

Is Classic :frowning:

Then I would go with [mention:74275055534c437ab13054c27a1ed5fb:e9ed411860ed4f2ba0265705b8793d05]'s suggestion to copy it to a temp table.

As “External Document No” field is unic, I will not be able to group them so I thought to copy just the 2 first digits from “External Document No.” field in a new one called POS No.

Thank to PALLE Arentoft’s help , how to copy the field I did as below:

I wrote in T. VAT Entry in function: CopyFromGenJnlLine () the below code to copy the field but when I save and run it, the field POS No. is not filled.

CopyFromGenJnlLine(GenJnlLine : Record “Gen. Journal Line”;VATEntry : Record “VAT Entry”)

“POS No.” := COPYSTR(“External Document No.”,1,2);

It works now: I create a new codeunit:

I loop through all VATEntry and findset(true)

VATEntry.“POS No.” := COPYSTR(VATEntry.“External Document No.”,1,2);
VATEntry.MODIFY;

and it works fine: The new field is filled.