Displaying the month.Year within a selected period

Business Central/NAV User Forum
1

I am trying to get this report to use the date table and display all the end of month within a period. If the customer Ledger Entry posting date field does not have posting for a particular month the report should display the missing month+ year and display zero next to it. For example a customer has the following details in the cust. ledger…

Posting date Amount
01/01/09 200
0102/09 300
01/06/09 100

And supposting a filter was set to display between Jan 2009 and August 2009, i want the report to display

01/01/09 200
01/02/09 300
31/03/09 0
30/04/09 0
31/05/09 0
01/06/09 100
31/07/09 0
31/08/09 0

Even though there was no entry for the zero amounts but still the months that were skipped is being displayed on the report with entry amount of zero.

Below is the current report that i have and i want to do the modifications to reflect the above. Can someone please help me?

[code]OBJECT Report 50005 MortgageStatementfor Housing
{
OBJECT-PROPERTIES
{
Date=30/07/08;
Time=14:27:57;
Modified=Yes;
Version List=NCM/MLS;
}
PROPERTIES
{
OnPreReport=BEGIN
DateFilt:=Customer.GETFILTER(Customer.“Date Filter”);
CompanyInformation.GET;
CompanyInformation.CALCFIELDS(Picture);
END;

}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table18;
DataItemTableView=SORTING(No.)
ORDER(Ascending)
WHERE(Fin. Charge Terms Code=FILTER(<>’’));
NewPagePerRecord=Yes;
PrintOnlyIfDetail=Yes;
OnPreDataItem=BEGIN
IF “Cust. Ledger Entry”.GETFILTERS <> ‘’ THEN
FilterText := 'Filter: ’ + “Cust. Ledger Entry”.GETFILTERS;
//Customer.SETFILTER(“No.”,‘BAK/A11’);
END;

OnAfterGetRecord=BEGIN
FinChrgTerm.GET(“Fin. Charge Terms Code”);
IF NOT FinChrgTerm.GET(“Fin. Charge Terms Code”) THEN
FinChrgTerm.“Interest Rate” :=0;
END;

ReqFilterFields=No.,Date Filter;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=19650;
SectionHeight=6768;
}
CONTROLS
{
{ 1000000002;TextBox;15000;0 ;3150 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1000000003;TextBox;0 ;423 ;2850 ;423 ;Visible=No;
Enabled=No;
SourceExpr=COMPANYNAME }
{ 1000000004;TextBox;17700;423 ;450 ;423 ;CaptionML=ENU=Page;
SourceExpr=CurrReport.PAGENO }
{ 1000000005;Label ;16950;423 ;750 ;423 ;ParentControl=1000000004 }
{ 1000000042;Label ;4650 ;5499 ;9900 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=MORTGAGE STATEMENT }
{ 1000000050;Label ;6600 ;5922 ;2250 ;423 ;Visible=No;
HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=AS AT }
{ 1000000027;TextBox;8400 ;5922 ;5400 ;423 ;Visible=No;
FontSize=9;
FontBold=Yes;
SourceExpr=GETFILTER(“Date Filter”) }
{ 1000000001;PictureBox;4500;0 ;9150 ;5076 ;SourceExpr=CompanyInformation.Picture }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=19650;
SectionHeight=3384;
}
CONTROLS
{
{ 1000000008;TextBox;3150 ;0 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=“No.” }
{ 1000000009;Label ;0 ;0 ;3000 ;423 ;ParentControl=1000000008;
FontSize=8;
FontBold=Yes }
{ 1000000011;TextBox;3150 ;423 ;6150 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=Name }
{ 1000000012;Label ;0 ;423 ;3000 ;423 ;ParentControl=1000000011;
FontSize=8;
FontBold=Yes }
{ 1000000014;TextBox;12300;423 ;3000 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=“Monthly Install” }
{ 1000000015;Label ;9600 ;423 ;2250 ;423 ;ParentControl=1000000014;
FontSize=8;
FontBold=Yes }
{ 1000000017;TextBox;3150 ;1269 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=“Loan Amount” }
{ 1000000018;Label ;0 ;1269 ;3000 ;423 ;ParentControl=1000000017;
FontSize=8;
FontBold=Yes;
CaptionML=ENU=Plot Cost }
{ 1000000020;TextBox;3150 ;1692 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=“Down Payment” }
{ 1000000021;Label ;0 ;1692 ;3000 ;423 ;ParentControl=1000000020;
FontSize=8;
FontBold=Yes }
{ 1000000023;TextBox;3150 ;2115 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=“Plot No.” }
{ 1000000024;Label ;0 ;2115 ;3000 ;423 ;ParentControl=1000000023;
FontSize=8;
FontBold=Yes }
{ 1000000036;TextBox;12300;1269 ;1800 ;423 ;HorzAlign=Left;
SourceExpr=FinChrgTerm.“Interest Rate” }
{ 1000000026;Label ;9600 ;1269 ;2100 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Interest Rate }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table21;
DataItemTableView=SORTING(Customer No.,Posting Date)
WHERE(Reversed=CONST(No));
OnPreDataItem=BEGIN

//“Cust. Ledger Entry”.SETFILTER(“Posting Date”,DateFilt);

FinchargeTotal:=0;
PrincipalTotal:=0;
ArreasVarTotal:=0;
CustBal1 := 0;
TotCustBal1:=0;
NumPayments:=0;
END;

OnAfterGetRecord=BEGIN
Fincharge:=0;
Principal:=0;
ArreasVar:=0;
//NumPayments:=0;

CustBal :=CustBal+“Cust. Ledger Entry”.Amount;

TotalCustBal := “Detailed Cust. Ledg. Entry”.“Amount (LCY)”;
CustBal1 := “Cust. Ledger Entry”. Amount;

//CustBal :=CustBal + “Cust. Ledger Entry”. Amount;
TotCustBal1 += CustBal1;

FromDate := 010107D;
ToDate := TODAY;
//Amount := 0;
CustLedEntryMonth := ‘’;
MonthsArrears := 0;

//Code to Count Months
IF (FromDate <> 0D) AND (ToDate > FromDate) THEN BEGIN
Calendar.RESET;
Calendar.SETRANGE(“Period Type”,Calendar.“Period Type”::Month);
Calendar.SETRANGE(“Period Start”,FromDate,ToDate);
Months := Calendar.COUNT - 1;
END ELSE
Months := 0;

//Code to count two or more Payments as one in a particular month.
IF (“Document Type” = “Document Type”::Payment) AND
(InitMonth <> DATE2DMY(“Posting Date”,2) ) THEN BEGIN
NumPayments := NumPayments +1;
InitMonth:= DATE2DMY(“Posting Date”,2);
END;

MonOwed:= Months - NumPayments;
OtherArrears := MonOwed * -Customer.“Monthly Install”;
TotArrs := OtherArrears + ArreasVarTotal;

IF “Document Type”=0 THEN BEGIN
Amount := CustBal1-CustBal1;
END;

IF “Document Type”=“Document Type”:: Invoice THEN BEGIN
Amount := CustBal1-CustBal1;
END;

IF “Document Type”=“Document Type”:: Refund THEN BEGIN
Amount := CustBal1-CustBal1;
END;

IF “Document Type”=“Document Type”:: Refund THEN BEGIN
CustBal1 := Amount;
END;
//TotCustBal1+=CustBal1;

IF “Document Type”=“Document Type”:: “Finance Charge Memo” THEN BEGIN
// Fincharge :=-ABS(Amount);
END;
IF “Document Type” = “Document Type”::Payment THEN BEGIN
Pay := Amount;
FinCharg := 0;
END;

IF “Document Type” = “Document Type”::“Finance Charge Memo” THEN BEGIN
FinCharg := Amount;
Pay := 0;
END;

IF “Document Type”=“Document Type”:: “Finance Charge Memo” THEN BEGIN
Amount :=ABS(Amount)-ABS(Amount);
END;

IF “Document Type”=“Document Type”::Payment THEN BEGIN
Principal:= “Closed by Amount”;
IF Principal=0 THEN
Fincharge :=0
ELSE
Fincharge :=ABS(Amount)-ABS(Principal);
// FinchargeTotal:=ABS(FinchargeTotal+ ABS(Fincharge));
PrincipalTotal :=PrincipalTotal+Principal;

IF Customer.“Monthly Install” = 0 THEN BEGIN
ArreasVar:=0;
END ELSE
ArreasVar:= -Customer.“Monthly Install” - “Cust. Ledger Entry”.Amount;
ArreasVarTotal:=ArreasVarTotal + ArreasVar;
END;
//ArreasVarTot:= ArreasVarTot + ArreasVar;

“var” := ArreasVarTot;

// …MLS…
//Code that does the monthly total of arrears
IF InitializeArreasVarTot THEN
ArreasVarTot := 0;
InitializeArreasVarTot := FALSE;

CLE.RESET;
CLE.COPY(“Cust. Ledger Entry”); //copying current rec with filters
IF CLE.NEXT = 0 THEN //checking next rec’s date…starts
MonthEnds := TRUE
ELSE BEGIN
IF ((DATE2DMY(“Posting Date”,3) * 1000 + DATE2DMY(“Posting Date”,2)) =
(DATE2DMY(CLE.“Posting Date”,3) * 1000 + DATE2DMY(CLE.“Posting Date”,2))) THEN
MonthEnds := FALSE
ELSE
MonthEnds := TRUE; //checking next rec’s date…end
END;

ArreasVarTot:= ArreasVarTot + ArreasVar;

P1:= FORMAT(Pay);
&nbs

2

Hi,

Your report is structured as:

Customer

Customer Ledger Entry

You could use:

Customer

Date

Customer ledger entry

where date is filtered to type Month and the range you need. You can the filter the customer ledger entry on the date range for each month.

3

I did that but it hasnt solved my problem… Can you elaborate further…

Thanks

4

You should be inserting a “layer” of the virtual Date table. Then you can filter on the month.

5

Hi,

First of all get the date range e.g. use the request form or use the Customer date filter

You will need to add extra code:

in the OnPreDataitem use

Date.Setrange( Type, Type::Month );

Date.setfilter( “Start Date”, ReportDateRange );

Then for each record in the date dataitem filter the Cust. Ledger Entry to

“Cust. Ledger Entry”.setrange( “Posting Date”, Date.“Period Start”, Date.“Period End” );

You can then print are required

6

Layer?? What do you mean?

7

Hi,

What Erik is suggesting is to add a indented level (layer) dataitem for the Virtual table Date

8

Layer = DataItem

9

I have done that but i still havent seen how will that help me achieve what i want. Can you elaborate more on this.

thanks

10

Hi,

So the design is

Customer

Date

Cust. Ledger Entry

So the report will loop the customers and for each customer loop the date dataitem in the filtered range you give and for each date record loop the Cust. Ledger entry table

I will take a single customer as an example and a date range 01/08/09…31./12/09

Customer Record 1

Date record 1 - 01/08/09 31/08/09

Cust. Ledger Entry - loop for customer record1 and total for date range 01/08/09…31/08/09

Date record 2 - 01/09/09 30/09/09

Cust. Ledger Entry - loop for customer record1 and total for date range 01/09/09…30/09/09

Date record 3 - 01/10/09 31/10/09

Cust. Ledger Entry - loop for customer record1 and total for date range 01/10/09…31/10/09

Date record 4 - 01/11/09 30/11/09

Cust. Ledger Entry - loop for customer record1 and total for date range 01/11/09…30/11/09

Date record 5 - 01/12/09 31/12/09

Cust. Ledger Entry - loop for customer record1 and total for date range 01/12/09…31/12/09

You will need to set the filters on the dataitems and decide which section you would like to print e.g. Cust. Ledger Entry Footer

11

Thanks For the explanation. but the footer section will be dispalying posting date or start date and end date?

thanks

12

Hi,

The reason I would pick the Cust. Ledger Entry footer is beacuse you may be totalling the the cust. ledger entry dataitem and display the total at the end. Both the start and end dates are available to print - you choose.

Note: the totalling can be acheived with a record variable as well as a data item

13

Thanks. I understand wwhat you trying to say. Maybe my explaining wasnt that good. My problem is display records for a customer within a specify period base on posting date. For example a customer makes a payment in 01/01/09 but skipped all the way to 04/05/09 and then make another payment. These dates are stored in the posting date field and what i want the report to do is to list all the months+year within the period specify and go to the cust. ledger entry and pick the amounts corresponding to the months+year and display them and if no posting was done for a particular month it should just display zero for that month.

Thank you

14

Hi,

In my example, it will loop for a customer in a give date range and you can total the cust. ledger entries for that date record. It will skip the footer section if no record for the cust. ledger entry so you can do

Customer

Date

where date is filter as before and the in the OnAfterGetRecord add code:

“Cust. Ledger Entry”.setrange( “Customer No.”, Customer.“No.” );

“Cust. Ledger Entry”.setrange( “Posting Date”, Date.“Period Start”, Date.“Period End” );

“Cust. Ledger Entry”.setrange( “Document Type”, “Cust. Ledger Entry”.“Document Type”::Payment );

Totpayments := 0;

if “Cust. Ledger Entry”.find( ‘-’ ) then

repeat

“Cust. Ledger Entry”.calcfields(amount);

totpayments += “Cust. Ledger Entry”.amount; // this will be in the currency

until “Cust. Ledger Entry”.next = 0;

You can then print in the body to display line by line or in the footer to display side-by-side ( need 12 variables)

Hope this clears it up for you

15

On which data item should the above code be, customer, date or cust. ledger entry?

Thanks

16

Hi,

The new structure would be

Customer

Date

with the code in the OnAfterGetRecord of the Date dataitem.