Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Current Month Post Reply Post New Topic
Author Message
Logically
Newbie
Newbie
Avatar

Joined: 12 Jun 2013
Location: Ireland
Online Status: Offline
Posts: 5
Quote Logically Replybullet Topic: Current Month
    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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Nov 2013 at 4:29am
If {OINV.DocDueDate} in lastfullmonth
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
Logically
Newbie
Newbie
Avatar

Joined: 12 Jun 2013
Location: Ireland
Online Status: Offline
Posts: 5
Quote Logically Replybullet 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
{@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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.033 seconds.