HOW TO CREATE TERRITORY-STATE-CUSTOMER wise Daily-Monthly-Yearly Sales report
To generate this report we need to refer two tables as follows:
1. Customer Table ( Table no . 18)
2. Item Ledger Entry ( Table no. 32)
Step 1.
Open development environment. Select report tab.Select NEW option to create new report.
Select dataitem as shown below pic.
To generate this report we need to refer two tables as follows:
1. Customer Table ( Table no . 18)
2. Item Ledger Entry ( Table no. 32)
Step 1.
Open development environment. Select report tab.Select NEW option to create new report.
Select dataitem as shown below pic.
Now go to Item Ledger Entry data source properties and set :
Data Item Table View : SORTING(Item No.,Entry Type,Variant Code,Drop Shipment,Location Code,Posting Date) ORDER(Ascending) WHERE(Entry Type=CONST(Sale))
AND
Data Item Link : Source No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Code),Global Dimension 2 Code=FIELD(Global Dimension 2 Code)
AND
Define Variables as
Step 2
And below code in Item Ledger Entry - OnAfterGetRecord
Item Ledger Entry - OnAfterGetRecord()
TodaysSale:=0;
TodaysQty:=0;
"Item Ledger Entry".SETFILTER("Posting Date", '=%1', TODAY());
IF "Item Ledger Entry".FINDFIRST THEN
BEGIN
REPEAT
TodaysSale+="Item Ledger Entry"."Sales Amount (Actual)";
TodaysQty+="Item Ledger Entry"."Invoiced Quantity";
UNTIL "Item Ledger Entry".NEXT=0;
END;
MonthlySale:=0;
MonthlyQty:=0;
CurrentMonth:=DATE2DMY(TODAY(),2);
CurrentYear:=DATE2DMY(TODAY(),3);
MonthStartDate:=DMY2DATE(1, CurrentMonth, CurrentYear);
MonthEndDate:=DMY2DATE(DATE2DMY(TODAY(), 1), CurrentMonth, CurrentYear);
"Item Ledger Entry".SETFILTER("Posting Date", '>=%1&<=%2', MonthStartDate,MonthEndDate);
IF "Item Ledger Entry".FINDFIRST THEN
BEGIN
REPEAT
MonthlySale+="Item Ledger Entry"."Sales Amount (Actual)";
MonthlyQty+="Item Ledger Entry"."Invoiced Quantity";
UNTIL "Item Ledger Entry".NEXT=0;
END;
TillDateSale:=0;
TillDateQty:=0;
CurrentMonth:=DATE2DMY(TODAY(),2);
IF (CurrentMonth>=4) AND (CurrentMonth<=12) THEN
BEGIN
CurrentYear_Start:=DATE2DMY(TODAY(),3);
//MESSAGE('%1', CurrentYear);
END
ELSE
BEGIN
CurrentYear_Start:=DATE2DMY(TODAY(),3)-1;
//MESSAGE('%1', CurrentYear);
END;
CurrentYear_End:=DATE2DMY(TODAY(),3);
TillStartDate:=DMY2DATE(1, 4, CurrentYear_Start);
TillEndDate:=DMY2DATE(DATE2DMY(TODAY(), 1), CurrentMonth, CurrentYear_End);
"Item Ledger Entry".SETFILTER("Posting Date", '>=%1&<=%2', TillStartDate,TillEndDate);
IF "Item Ledger Entry".FINDFIRST THEN
BEGIN
REPEAT
TillDateSale+="Item Ledger Entry"."Sales Amount (Actual)";
TillDateQty+="Item Ledger Entry"."Invoiced Quantity";
UNTIL "Item Ledger Entry".NEXT=0;
END;
Step 3
Go to View---->Layout for report layout designing
report is ready
