Report Set Posting Date Filter

Hi guys,

I would like to create a report that has a posting date filter range (eg: 010111…310111) this part is fine.

The tricky part is on another column i would like to view the value past year similar time(eg: 010110… 310110)

Anyone care to share how to go about it?

This is actually fairly easy:

Therefore you have got the CALCDATE command.

First you need to extract the starting and ending date in your date filter:

StartDate := GETRANGEMIN(“Date Filter”);
EndDate := GETRANGEMAX(“Date Filter”);

Then you use the CALCDATE function:

NewStartDate := CALCDATE(’<-1Y>’, StartDate);
NewEndDate := CALCDATE(’<-1Y>’, EndDate);

Your new Date filter will be applied with a SETRANGE(“Date Filter”, NewStartDate, NewEndDate).

Hi Thomas,

Your method has been tried, not sure if I did it at the wrong location (Eg: data item, group header)

Also I would like to view by invoice only so that I can compare the earnings by salesperson for this year and last year, break down by individual customers under him/ her.

JDI

Basically, I group it by customer ledger entry ( correct me if I got it from the wrong table), under Cust. Ledger Entry Group Header I place my code as follow:

//Last Year Sales
CLE.RESET; //Cust. Ledger Entry
CLE.SETFILTER(CLE.“Customer No.”, “Customer No.”);
CLE.SETFILTER(CLE.“Salesperson Code”, “Salesperson Code”);
//CLE.SETFILTER(CLE.“Document Type”, ‘Invoice’);
CLE.SETRANGE(CLE.“Posting Date”, FilterDate1_s,FilterDate1_e);

IF CLE.FINDFIRST THEN
LastYearSales := LastYearSales + CLE.Amount;

GrandTotal := GrandTotal + Amount;
GrandTotal1 := GrandTotal1 + CLE.Amount;

The tricky part I realised that I need to group by Customer as well, as you may see customer (Eg: Thomas) on 14th Aug 07 might not be similar to customer (Nikeman77) on 14th Aug 06.

Hi,

you should not do any calculations (except setting visibility) in Group headers “OnPreSection”.

Rather consider to have an additional dataitem for your customer ledger entries.

So base your report on customer and have an indented dataitem for the customer ledger entry. Connect them through the customer no.

Then do the SETRANGE on the posting date in the “OnPreDataItem” of the cusotmer ledger entry dataitem.

Hi Thomas,

I did few changes as per suggest:

1.Data Item: Salesperson/ Purchaser (Top level)
Data Item: Cust. Ledger Entry (Second level connected with Salesperson Code)

2.On Group Header for Salesperson Purchaser I did display:

Salesperson Code Salesperson Name
10427 Marcus Loin

Customer Code Customer Name Current Amount Previous Amount

3.On Cust. Ledger Entry Group Header Presection,
I place my setrange coding.

4.Unable to retrieve amount

Correct.

You should not do calculations in the “PreSection” at all, there it is a way too late for what you want to do.

You should set your date range in the “OnPreDataItem” of the 2. dataitem.

Additionally there is no need to create the totals yourself using code like :

GrandTotal := GrandTotal + Amount;
GrandTotal1 := GrandTotal1 + CLE.Amount;

This can be handled by NAV itself easier when you are calling “CurrReport.CREATETOTALS(Amount)”.

Then NAV sums up all amounts automatically and displays the correct values in the correct section, just put a textbox in the section you want to see the amounts. Group-Totals and Grand Totals are maintained ba NAV automatically.

Thanks Thomas

Welcome,

Please mark the solution which helped you by clicking the “Verify Solution” button in that post which contains your best answer(s).

This helps the other users to see that your problem got solved and helps them to find the solution when they are looking for a similar issue.

Hi Thomas,

By the way some ‘funny’ thing happening, on my second data item, Cust. Ledge Entry> Group Footer, I manage to group all customers by Salesperson within a posting date, what happen was the sales (LCY) is not appearing even though on the Cust. Ledger Entry Data Item Pre Data Item, I inserted CurrReport.CreateTotals(Sales (LCY))

And you are sure that you have a text box in that section which has the Sales (LCY) field as source expression?

And by the way, you should mark the ANSWER which helped - not your “Thank you” post [;)]