Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Two month lag on date Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Paula J
Groupie
Groupie
Avatar

Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
Quote Paula J Replybullet Topic: Two month lag on date
    Posted: 17 Feb 2014 at 7:02am
I have a report that is resulting based on a discharge date.
I want to schedule the report to run on the 4th of each month and only include the accounts with a discharge date range 2 months prior.
So on March 4th I want the report to result accounts with a discharge date in January and so on.  I also want this to continue on without having to worry about when the year changes to 2015.
How can I do this?
 
 
Paula J
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 17 Feb 2014 at 7:16am
enter the end date as a parameter (or you could just have it auto populate the value based on the current date).

if you are using a stored procedure you would add logic similar to this, but this code is really geared towards the record selection criteria (Reports/Select Formulas/Record)

local datetimevar strt:= DATEADD("m",-2,currentdate;
local datetimevar dne;

strt := DATE(YEAR(strt),MONTH(strt),1);
dne := DATEADD("m",1,strt);
dne := DATEADD("s", -1, dne);

{table.dateField} in strt to dne;


what I did:
1. subtract 2 months from today
2. get to the first of the month (for 2 months ago)
3. add a month to get the starting month
4. subtract a second to get last day of the prior month at 11:59:59 pm
5. check that a field in the report is in the month 2 months ago.

HTH
IP IP Logged
Paula J
Groupie
Groupie
Avatar

Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
Quote Paula J Replybullet Posted: 17 Feb 2014 at 9:03am
Thanks for the info but I have little experiance with crystal reports so I really don't understand what you are saying.
 
Paula J
IP IP Logged
hello
Groupie
Groupie
Avatar

Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Quote hello Replybullet Posted: 20 Feb 2014 at 11:28am
Try this:

After you drag the {discharge_date_range} field (or whatever it's called) to the CR canvas, you can suppress unwanted records by right clicking on that field, choosing format field, then click on the common tab, then place a check mark in the suppress box. There is a button to the right of the suppress box called X-2. Click that button and insert the following:

{discharge_date_range} <> dateadd("m",-2,currentdate)

Only records with a discharge date equal to 2 months previous of the current date should be shown.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 20 Feb 2014 at 11:30am
though as a caveat, if you run a sum or count or avg, all the records will be included...just the display was suppressed, not the records.
IP IP Logged
hello
Groupie
Groupie
Avatar

Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Quote hello Replybullet Posted: 20 Feb 2014 at 11:39am
Originally posted by lockwelle

though as a caveat, if you run a sum or count or avg, all the records will be included...just the display was suppressed, not the records.


Yes, your right lockwelle.

My instructions will only suppress the field, not the record. She wants to suppress all the record. Sorry to misguide.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Feb 2014 at 4:52am
I wasn't meaning any disrespect to the solution, as it might work out for Paula J, I was just letting her know that things might not be perfect with the solution and what might be the cause.
IP IP Logged
Paula J
Groupie
Groupie
Avatar

Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
Quote Paula J Replybullet Posted: 24 Feb 2014 at 6:52am
Thanks for all the suggestions but none of them have worked. 
Paula J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Feb 2014 at 7:47am

If you set the report to run on the 4th and use a select statement of

datediff('m',{Table.DischargeDate},currentdate) in 1 to 2
IP IP Logged
Paula J
Groupie
Groupie
Avatar

Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
Quote Paula J Replybullet Posted: 26 Feb 2014 at 10:53am
This is getting close but it pulls disharge dates for a one and two month lag vs the two month lag I need...Dec and Jan when run in Feb.  When run in Feb want it to pull only discharge dates for Dec.
Paula J
IP IP Logged
Page  of 2 Next >>
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.032 seconds.