Rolling 12 month Report

[gennaroalpha7]

Hi,

In Crystal syntax there is no Date Range function for the last full year e.g., lastfullweek.

My question is what is the easiest Crystal syntax formula/method to run my (last full year) rolling 12 month report with data from 1 year past i.e., the report as of end of January 2013 would contain all the records from February 2012 through January 2013.

Thanks for your help.

G

 

[gennaroalpha7]

Thanks Pete, I'll make these changes today and let you know.

Have a good one.

Alpha7

 

[gennaroalpha7]

Hi Pete,

I have made the changes and it's still not kick starting the report. Could it be that in the Selection Formulas - Record we are not calling the StartDate and EndDate?


I have a similiar report and in the Selection Formulas - Record the formula is as follows...

{@crDate} in LastFullWeek and
{Subset.DeptID} in ["10154", "10067", "00068"]

Thanks.

Alpha7

 

[fisheromacse]

instead of using 'is between', try using is greater than startdate and less than enddate.

something like:

{CallLog.KeyWord} in ["BOTW.COM", "Mobile Banking", "OBS", "OBS BOTW"] and
({CallLog.RecvdDate} > {@StartDate} and
{CallLog.RecvdDate} < {@EndDate})

 

[gennaroalpha7]

Hi fisheromacse,

Thanks for the suggestion, I tried it and produced no results..."missed it by that much"

Thanks

Alpha7

 

[pmax9999]

Please try amending the last part of the 2 formulas from 'MM/dd/yyyy' to 'M/dd/yyyy'.

Also, as a test, please place the two formulas onto the report and check the resultant format.

Cheers
Pete.

 

[BettyJ]

From your post on Feb 27 16:43 it says,
5. When I do this it higlights "{@StartDate} to {@EndDate}" and says a "String is required here"

I am not sure if this will work but try
[highlight #FCE94F]date([/highlight]{CallLog.RecvdDate}[highlight #FCE94F])[/highlight] in [{@StartDate} to {@EndDate}]

if date doesn't work try with [highlight #FCE94F]cdate[/highlight] also

 

[gennaroalpha7]

Hi Pete,

Your last post was successful! I can't believe that little tweak did it - I didn't think it was that sensitive - the report ran well, but it ran back 2 years instead of 1 year.

Can you please help me one more time to teak it for 1 year back from todays date.

Thanks.

Alpha7

P.S. BettyJ, I tried your suggestion but no cigar. Thanks for giving it a shot.

 

[pmax9999]

Good news. The reason BettyJ's approach was not going to work is because we were converting the date range to a string to match the database field, so then converting the database date strings to real dates could not help.

As for the report going back 2 years instead of 1, please check to make sure the StartDate formula uses the code I posted. If so, please put the StartDate formula on the report and tell me what it displays.

Pete.

 

[gennaroalpha7]

Hi Pete,

Here's the code you posted - with the changes....ToText(dateserial(year(currentdate)-1,month(currentdate)+1,1), 'M/dd/yyyy')

I inserted the StartDate formula onto the report and it produced this date (3/1/2012)

The EndDate produced...2/28/2013

Thanks.

Alpha7

 

[gennaroalpha7]

Hi Pete,

To be more specific, there are two date fields in the report...CallLog.RecvdDate and CallLog.ClosedDate. These dates are the opening and closing of a service desk ticket.

Don't know if the effects the rolling 12 Month formulas...

Thanks.

Alpha7

 

[pmax9999]

OK. If the 2 formulas are generating the correct dates and have been used in the Recod Selection formula correctly the report could not he returning 2 years data. Please check the Record Selection formula again.


Cheers
Pete

 

[pmax9999]

There has been no previous reference to anything other than the Received Date, and the code provided will return records that were received in the past 12 months.

How you want to deal with the Closed Date is something that we can't really guide you on. Once you determine exactly what you want the report to show, I or someone else should be able to assist with changes formulas etc.

Hope this has helped.

Regards
Pete.

 

[gennaroalpha7]

Hi,

Here's what I currently have in the Selection - Formulas - Record

{CallLog.RecvdDate} in [{@StartDate} to {@EndDate}] and
{CallLog.KeyWord} in ["BOTW.COM", "Mobile Banking", "OBS", "OBS BOTW"]

When I run the report...in the CallLog.RecvdDate field...the latest date it produces is 8/27/2010 and in the CallLog.ClosedDate field...the latest date it produces is 3/14/2011

In the CallLog.RecvdDate field...the earliest date it produces is 2/11/2013

Thanks.

 

[gennaroalpha7]

That's okay. I am concearned when the ticket was opened in CallLog.RecvdDate field. But, for some reason its not going back 1 year. As in my above post it's going back to 8/27/2010

We are almost there Pete.

Thanks.

Alpha7

 

[pmax9999]

To be honest I am out of ideas. There is no logical reason (that I can see at least) why a rec'd date earlier than March last year could appear in the report based on the code I provided and what you say you implemented. I think we will need to see if any one else has any smart ideas.

The only other thing I can think of to check is the SQL code it is passing to the database, but I don't recall how to do that in CR 8 which is an old version.

Pete.