Print Page | Close Window

Current Month

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20206
Printed Date: 26 May 2024 at 3:51am


Topic: Current Month
Posted By: Logically
Subject: Current Month
Date Posted: 08 Nov 2013 at 1:22am
I am stuck on how to proceed to meet my clients requirements, all help and suggestions very much welcome!.
 
I am trying to create a Customer Statement report:
 
Date field  : Invoice Due date - datatype - DateTime.
 
The approach I am taking to be have all open Invoice listed in the main body of the reports and in the report footer, I am inserting subreports to summary the status of the customers account : ie Current, 1 Month, 2 Month
The sub-reports are displayed within the report footer.
 
1st Subreport : Current, meaning within the current Month
 
Current =
If {OINV.DocDueDate} >= MonthToDate  then
"Current"
This part is working fine.
 
 
2nd Subreport  : 1 Month, meaning docduedate is within 1 month prior to current month
 
I tried using the following :-
If {OINV.DocDueDate} in Aged0To30Days then
"1 MONTH"
 
Unfortunately I have a duplicated in my results:
 Records with a date between 1-7 Nov are appearing in subreports 1 &2
 
Suggestions please!
 
 


-------------
ONLY learning


Replies:
Posted By: DBlank
Date Posted: 08 Nov 2013 at 4:29am
If {OINV.DocDueDate} in lastfullmonth

Posted By: lockwelle
Date Posted: 08 Nov 2013 at 4:41am
ok, just to get the time spans straight, if the invoice is due in November, then they are current, but if it is in October they are 1 month?

Just looking at the boundaries...if the invoice is due Nov 1 and it is Nov 28, they are still current, and due Oct 31 and it is Nov 1 they are last month.

Or is it, if the bill is due after today they are current, and if the bill was due within the last month they are 1 month?

Well let's see what we come up with for both situations:
scenario 1:
report 1
if month({table.dueDate}) >= month(today) and year({table.dueDate}) >= year(today) then "current"

report 2:
local datevar lastmonth:=dateadd("month",-1, today);
local datevar due = dateadd("month",-1,{table.dueDate});

if month(due) >= month(lastmonth) and year(due) >= year(lastmonth) then "1 month"


scenario 2:
report 1
if {table.dueDate} >= today then "current"

report 2
if dateadd("month", -1, today)< {table.dueDate} and {table.dueDate} < today then "1 month"


I might be off on the syntax for dateadd, but these should give you some ideas...hopefully

Posted By: Logically
Date Posted: 11 Nov 2013 at 2:29am
Hi All,
Thank for all your responses. I had a rethink and discussion with my client, and the following approach has now being taken.
 
The statement printed date, which will equal current date is used as a reference point to calculate the aging.
 
Each sub-report has the below formula.
CurrentDate -{OINV.DocDueDate}
 
Each sub-report has the required aging within the selection critieria:
 
{OINV.DocStatus} = "O" and
mailto:%7b@substraction - {@substraction } in 31.00 to 60.00 and
{OINV.CardCode} = {?Pm-OCRD.CardCode}
 
All working now and client happy with the output etc of the Customer statement reports


-------------
ONLY learning


Print Page | Close Window