Report Design: Two month lag on date

Print Page | Close Window

Two month lag on date

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=20481
Printed Date: 06 May 2024 at 5:23am

Topic: Two month lag on date

Posted By: Paula J
Subject: Two month lag on date
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

Replies:

Posted By: lockwelle
Date 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

Posted By: Paula J
Date 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

Posted By: hello
Date 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.

Posted By: lockwelle
Date 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.

Posted By: hello
Date 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.

Posted By: lockwelle
Date 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.

Posted By: Paula J
Date Posted: 24 Feb 2014 at 6:52am

Thanks for all the suggestions but none of them have worked.

-------------
Paula J

Posted By: DBlank
Date 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

Posted By: Paula J
Date 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

Posted By: DBlank
Date Posted: 26 Feb 2014 at 10:59am

sorry thought you wanted both

datediff('m',{Table.DischargeDate},currentdate) = 2

Posted By: Paula J
Date Posted: 27 Feb 2014 at 4:44am

THANKS! This worked and is just what I want.

-------------
Paula J

Print Page | Close Window