Group total fields not working in sale report

neellotus

Hi all,
I am creating customerwise itemwise sale report in Navision 4.0 SP3 from "Sales Invoice Header" and "Sales Invoice Line" tables and linked both table through dataitemlink on document no., then added "Sell-to Customer No." & "No." fields in group total fields and Quantity, Amount in Total Fields of Line table as per attached image "DataItems".

I created two Group Header on Sales Inv Line table & i placed "Sell-to Customer No.", "No." ,qty & amount field of same table on Group Footer as per attached image "Section_Designer".

But report doing group on "Document No." instead of "Sell-to Customer No." & "No." and then sum of qty & amount as per attached image "Rpt_Output" because of this one item showing two or three times in report due to invoice wise.

I also tried below code : -
Sales Invoice Line, GroupHeader (1) - OnPreSection()
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY="Sales Invoice Header".FIELDNO("No.");

But not getting desire result as per attached image "Desire Output" so please tell me where i am dosing wrong and help me to get correct & desire result.

Thanx
Kamal

Duikmeester

Sorry, I clicked thru classic report section not role tailored.

Kishorm

You can achieve what you want with a slightly differently approach...

1) Keep your data items as they are but don't print anything for them, instead in the OnAfterGetRecord for the Sales Line Invoice you populate a temporary buffer table (with Customer No & Item No as key fields). If a record does not exist then add one and set Quantity and Amount fields, otherwise just add Quantity and Amount - I.e. you are totalling up Quantity and Amount on your buffer table.

2) Create a new DataItem (top level) using Integer table - use this to go through your buffer table and print totalled Quantity and Amount values on the report.

ishwarsharma016

You have placed the controls in the footer section. Place these controls in GroupFooter section and remove them from Footer, if you want to see data grouped based on GroupTotalFields.

And change your code to,

Sales Invoice Line, GroupHeader (1) - OnPreSection()
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY="Sales Invoice Line".FIELDNO("No.");

Place your controls in this groupfooter.

Kishorm

ishwarsharma016 wrote: »

You have placed the controls in the footer section. Place these controls in GroupFooter section and remove them from Footer, if you want to see data grouped based on GroupTotalFields.

And change your code to,

Sales Invoice Line, GroupHeader (1) - OnPreSection()
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY="Sales Invoice Line".FIELDNO("No.");

Place your controls in this groupfooter.

Unfortunately because of the hierarchical nature of the dataitems as per the original post and the fact that they are linked on the Document No., this will not work. If you had 2 invoices for the same items and for the same customer, the result would not total the items correctly (they would appear twice).

However, if the user does not need to filter on any of the fields in "Sales Invoice Header" then you can delete this dataitem and move the fields to the GroupFooter section as suggested above and this should work.