How to group lines in a report

Michaellee

Hi there,

I'm sure this is a very newbie question so please bear with me on this.

How are you able to group lines into one line?

i.e Sales Invoices

If I sell Item X and they have variants BlackSmall, BlackMedium and BlackLarge, the lines shown on the Invoice line would look something like this:

Item Variant Qty
X BlackSmall 10
X BlackMedium 10
X BlackLarge 10

How can I group by Colour and produce something like this?

Item Colour Qty
X Black 30

I need to do this on all types of reports due to the amount of variants that the items have.

Any help much appreciated.

Thanks,

Mike

Alisha

You can use GroupTotalFields property, create a key by:

Document Type, Document No,Type,No,Colour (filter by Type=Item)

Use this key on the Sales Invoice Line dataitem, and the property GroupTotalFields = Colour.

I assume you are showing the items per document no, if not remove document type and document no. from the key.

On the sections, create a Groupfooter, this will only show the items grouped by colour. For the total quantity set the property TotalFields = Quantity.

Hope this helps..

pizza666

Hi,

I came across the same question (sort of) for printing invoices with multiple lines with the same description and in some other areas (exporting purch. inv. lines). My solution is as follows:

Within OnAfterGetrecord call a function
Not_Skip_record := Grouplines(doc.no., line No., description, VAR Line Amount) (add extra fields if nessesary)

In appropraite section: CurrReport.Showoutput := CurrReport.Showoutput AND Not_Skip_record;

Now the fun part:
The function does this. SElect all invoice lines with this Docno and Description (Or part of it in your case).
If the first lineNo equals the Line no. in functionCall THen this is the first line and alle Line amounts can be added together and returned,
In all other cases return Not_skip_record := FALSE and set line amount to 0.
(extra: don't do this For empty description lines, they are often used for readability)

The best part is you don't have to change anything else on the report (total fields etc.).
If you want I can post some example code (but takes some time, not my Nav pc)

Good Luck.

bonk

Hi pizza666,

If I understand correctly, your function creates grouptotals without the need for defining a key and without the need for sorting? If so, I would be very interested in some example code.

Tnx in advance..