Author |
Message |
Paula J
Groupie
Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
Paula J
Groupie
Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
|
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 Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
Paula J
Groupie
Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 24 Feb 2014 at 6:52am |
Thanks for all the suggestions but none of them have worked.
|
Paula J
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Paula J
Groupie
Joined: 22 Aug 2011
Location: United States
Online Status: Offline
Posts: 51
|
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 Logged |
|
|