Help needed to group report

Hi, having difficulty trying to work this one out, I need to produce a report based on a modified sales line table whereby it displays all items sold sorted by month with monthly totals, so far so good, the problem arises when an item has been sold many times during that month ie/ numerous lines of the same item are displayed, how can I get the lines to show only once per item no. with the item quantity total for that month displayed on the same line. The customer does not want the month to be displayed more than once either, currently it is displaying for each item total per month if I try to group totals by item no. Any help is much appreciated - thanks!

Hi Simply add No. in the sales line table, to the key being used in this report. Add No. in the TotalFields and GroupTotalFields of report.

quote:


Originally posted by MikeB2168
Hi, having difficulty trying to work this one out, I need to produce a report based on a modified sales line table whereby it displays all items sold sorted by month with monthly totals, so far so good, the problem arises when an item has been sold many times during that month ie/ numerous lines of the same item are displayed, how can I get the lines to show only once per item no. with the item quantity total for that month displayed on the same line. The customer does not want the month to be displayed more than once either, currently it is displaying for each item total per month if I try to group totals by item no. Any help is much appreciated - thanks!


There might be many solutions, all disrupting on your report structure, I’m afraid. [:)] The first I can think of is a structure like this: Item Date DataItemTableView=SORTING(Period Type,Start Period) WHERE(Period Type=CONST(Month)) DataItem Date OnAfterGetReport: ModifiedSalesLine.SETRANGE("Order Date","Start Period","End Period"); ModifiedSalesLine.SETRANGE("No.",Item."No."); ModifiedSalesLine.SETRANGE(Type,ModifiedSalesLine.Type::Item); IF ModifiedSalesLine.FIND('-') THEN REPEAT TotQty := TotQty + ModifiedSalesLine.Quantity; UNTIL ModifiedSalesLine.NEXT = 0; Print: Item, Month, TotQty Anna

I think that you should first create a key so that the data is sorted in your way. THen use this key to group the data. If you only want to see totals of the group use the report-groupfooter-section and not the report-body-section.

Hi Guarav, Thanks for your quick response, unfortunately I already tried that solution but it forces month to repeat for each item total.[:(] Anna, is there any way of doing this using only the modified sales line table? iammicky, table is currently sorted by year,month,no., this then shows multiple lines for the same item which is what I am trying to avoid, is there a way of only displaying the last record for each item per month which will then have the correct total as I’m adding the item total after each record then resetting on a new item.

Hey Mike, use a GroupFooter to display the information. Do not forget to insert the group total fields. Fred

quote:


Originally posted by MikeB2168
Hi Guarav, Anna, is there any way of doing this using only the modified sales line table?


You might add to the table a field containing only Year+Month. It might be a text field made of the string FORMAT(DATE2DMY(“Order Date”,3)) + FORMAT(DATE2DMY(“Order Date”,2)) or a date field made of CALCDATE(D1,“Order Date”) or whatever you like, just be sure that all the table lines of the same month have the same value. Add the new field to the key and group the report on that. Sorry I didn’t think of this before! [:)] Anna

quote:


Originally posted by Anna Perotti

quote:


Originally posted by MikeB2168
Hi Guarav, Anna, is there any way of doing this using only the modified sales line table?


You might add to the table a field containing only Year+Month. It might be a text field made of the string FORMAT(DATE2DMY(“Order Date”,3)) + FORMAT(DATE2DMY(“Order Date”,2)) or a date field made of CALCDATE(D1,“Order Date”) or whatever you like, just be sure that all the table lines of the same month have the same value. Add the new field to the key and group the report on that.


Ah, of course! print the result in a GroupFooter section with CurrReport.SHOWOUTPUT := CurrReport.TOTALCAUSEDBY = nnn where nnn is the field number of the grouping field. [;)] Anna

Thanks everyone for your help, but I have finally figured a way around the problem by declaring a variable type record(MySalesline) and setting a range after get record using current filters and then finding the last record, if MySalesline = CurrRec ie/ it’s the last record then show output otherwise don’t, this way I get the correct totals for each item.[;)]