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 below 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 image “Section_Designer” attached below :-
But report doing group on “Document No.” instead of “Sell-to Customer No.” & “No.” and then sum of qty & amount
because of this one item showing two or three times in report as per image “Rpt_Output” attached below:-
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 image “Desire Output” attached below :-
so please tell me where i am doing wrong and help me to get correct & desire result.
Thanx
Kamal
Hi Kamal,
To achieve the desired output, please move the textboxes from Footer to GroupFooter and add “CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Header”.FIELDNO(“No.”);” in this GroupFooter.
Please try this and let us know if it helps.
Hello Ishwar,
Thanks for ur reply…
I tried your solution as below image but not getting desire result :-
and if i don’t use CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Header”.FIELDNO(“No.”);
on Sales Invoice Line, GroupFooter (3) - OnPreSection() then report displaying duplicate records as below:-
Please correct the code. It shouldn’t be CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Header”.FIELDNO(“No.”);
It must be CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice line”.FIELDNO(“No.”); in group footer(3) pre section.
Put Sales invoice line in place of sales invoice header. And then check.
P.S I didn’t pay attention to this small mistake in my last message. Sorry!
I added one more GroupFooter(4) and code on both groupfooter :-
Sales Invoice Line, GroupFooter (3) - OnPreSection()
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Line”.FIELDNO(“Sell-to Customer No.”);
Sales Invoice Line, GroupFooter (4) - OnPreSection()
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Line”.FIELDNO(“Sell-to Customer No.”);
i changed from :-
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Line”.FIELDNO(“Sell-to Customer No.”);
To :- CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Line”.FIELDNO(“No.”);
but report displaying duplicate records…
You may remove (1), (2) and (4) to avoid confusion.
Just add “CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice line”.FIELDNO(“No.”);” in group footer(3) pre section.
It should work and if it doesn’t then please check onaftergetrecord and other sections of your report and find if there is any code which is sorting Sales Invoice Line on another key.
The sorting must be on “Sell-to Customer No.”,No." on Sales Invoice Line only.
i removed all codes from all section except “CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice line”.FIELDNO(“No.”);” in group footer(3) pre section.
And sorting already on “Sell-to Customer No.”, “No.” on Sales Invoice Line only as per below image :-
But report displaying dupl items as below :-
Is there any specific reason to keep Sales Invoice Header as a parent Dataitem? Removing it will group the data in the way you want it. I have tried it.
No issue, I can remove it… But report should filter on the basis of “posting date” of sales invoice header table, so that user can execute report by putting same date.
I am pretty sure you can add a filter on Sales Invoice Line for posting date. The data will be same.
i already tried through setrange and setfilter Sales Invoice Line - OnAfterGetRecord() but report not working …
can you help me for the same…
Setrange isn’t required. Add posting date in ReqFilterFields in Sales Invoice Line dataitem.
posting date doesn’t exist in Sales Invoice Line dataitem.
If that’s the case. Declare StartDate & EndDate as Date global variables and SalesInvHdr as record global variable and add the code below on OnAfterGetRecord trigger of Sales Invoice Line Dataitem.
SalesInvHdr.RESET;
SalesInvHdr.SETRANGE(“No.”,“Sales Invoice Line”.“Document No.”);
SalesInvHdr.FINDFIRST;
IF (SalesInvHdr.“Posting Date” < StartDate) OR (SalesInvHdr.“Posting Date” > EndDate) THEN
CurrReport.SKIP;
Hi Neel
Best way you add the field Posting date in Sales Line and update that field as in newer version MS already added this field in Sales Line table
Thanx to ishwar & Amol…
I added “Posting Date” field in sales line table and updated from sales header table and then created report & getting desire output…
But while exporting in excel it wrongly exporting sum of qty & amount as below :-
Code of Sales Invoice Line, GroupFooter (2):-
Sales Invoice Line, GroupFooter (2) - OnPreSection()
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Line”.FIELDNO(“No.”);
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY=“Sales Invoice Line”.FIELDNO(“Unit Price”);
IF Customer.GET(“Sales Invoice Line”.“Sell-to Customer No.”) THEN
Cust := FORMAT(Customer.Name) ;
IF “Export To Excel” THEN
BEGIN
RowNo += 1;
EnterCell(RowNo,1,FORMAT(“Sell-to Customer No.”),FALSE,FALSE);
EnterCell(RowNo,2,FORMAT(Cust),FALSE,FALSE);
EnterCell(RowNo,3,FORMAT(“No.”),FALSE,FALSE);
EnterCell(RowNo,4,FORMAT(“Unit Price”),FALSE,FALSE);
EnterCell(RowNo,5,FORMAT(Quantity),FALSE,FALSE);
EnterCell(RowNo,6,FORMAT(Amount),FALSE,FALSE);
END
