Rolling Report...Help! 2

[gennaroalpha7]

Hi,

In Crystal Syntax there is no Date Range function for the last full year.

I want to run my (last full year) report with data from 1 year past i.e., the report as of end of February 2013 would contain all the records from February 2012 through February 2013. If I run the report today 3/5/2013, it would contain data from today to 3/5/2012. If I run it 2 months from now, 5/10/2013, it would contain data from 5/10/2013 to 5/10/2013...

This is what I have so far...And, for some reason my "CallLog.RecvdDate" field is pulling data from 2 years back. How can I correct it to pull data from only one year back?


1. Report - Selection Formulas - Record

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


2. Field Explorer - Formula Fields

StartDate - ToText(dateserial(year(currentdate)-1,month(currentdate)+1,1), 'M/dd/yyyy')

EndDate - ToText((dateserial(year(currentdate),month(currentdate)+1,1)-1), 'M/dd/yyyy')


Thanks in advance for your help.


Alpha7

 

[CoSpringsGuy]

mixed promgramming language .. sorry

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

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

This is a continuation of a previous thread by by gennaroalpha7 and essentially going over ground previously covered and resolved.

[link ]

http://www.tek-tips.com/viewthread.cfm?qid=1705012

[/url]

Based on that previous thread, the {CallLog.RecvdDate} is actually a string, hence my solution to convert the start and end dates to strings so as to match the database.

The outstanding issue seems to be the fact that it returns data prior to the {CallLog.RecvdDate}.

 

[pmax9999]

Sorry, that last line should read:

"The outstanding issue seems to be the fact that it returns data prior to the {@StartDate}.

 

[CoSpringsGuy]

Got it .. I wasnt aware of another thread .. However, could converting the formula to a string then using a > or < potentially be the reason it is returning errant information? Thats why I suggested converting the date field from the database to a date field. Obviously that will only work if the field looks like a date.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

Doh - Thats it. Converting DB field to date is going to be quite ineficient, but it is the solution.

Apologies for the poor advice in the original thread.

 

[CoSpringsGuy]

It will be very inefficient but only if its a large database will it be notice I think.
Reading through the referenced thread, it was suggested but seemed to be ignored.

date({CallLog.RecvdDate}) in [{@StartDate} to {@EndDate}]

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Dell and Pete,

Thank you so much for your expertise. You two are truely are amazing - just following the last few posts from you two.

Here's what I have now...

In "Report - Selection Formulas - Record"...I have

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

and my "Field Explorer - Formula Fields" read...

StartDate:
date(year(currentdate)-1,month(currentdate)+1,1)
EndDate:
date(year(currentdate),month(currentdate)+1,1)-1

I ran the report with these changes and it seems to be working.

Should I add BettyJ code input?

Thanks.

Alpha7

 

[CoSpringsGuy]

good job Dell and Pete

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Sorry, I meant CoSpringsGuy and Pete (pmax9999)...and hilfy (Dell)

You guys have a good one and thank you very much.


Alpha7