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

 

[hilfy]

Why are you converting the dates to strings? Also, based on your comment "If I run the report today 3/5/2013, it would contain data from today to 3/5/2012", I would do something different to calculate the start and end dates:

StartDate: DateAdd('m', -12, CurrentDate)

EndDate: CurrentDate

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gennaroalpha7]

Thank you Dell.

I will try that and let you know how it works.

Alpha7

 

[gennaroalpha7]

Hi Dell,

When I follow your suggestions...

In the Report - Selection Formulas - Record

It's asking for "A String is required here", in the {@EndDate}

Thanks.

Alpha7

 

[hilfy]

Is {CallLog.RecvdDate} a date field or a string field?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gennaroalpha7]

A Date field...

 

[hilfy]

Is your {@StartDate} formula returning a date or a string?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gennaroalpha7]

StartDate and EndDate are both returning dates...

Currently, StartDate returns 4/1/2012 and EndDate returns 3/31/2013. When I hover over both of them it indicates string...e.g., @StartDate (String) and @EndDate (String)


Thanks.

 

[gennaroalpha7]

But, when I actually run the report the {CallLog.RecvdDate} field gives a date that is approx 2 years in the past. I need a year only.

Thank you.

 

[hilfy]

I suspect the "In" is causing the problem. Change your selection formula to something like this:

{CallLog.RecvdDate} >= {@StartDate} and
{CallLog.RecvdDate} < {@EndDate} and ...

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gennaroalpha7]

Dell, when I change to this...the report produces no data...

Thanks.

 

[CoSpringsGuy]

When I hover over both of them it indicates string...e.g., @StartDate (String) and @EndDate (String)

I think you should check your formula.. can you show the formula for startdate?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Formulas for...

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.

 

[CoSpringsGuy]

startdate
date(year(currentdate)-1,month(currentdate)+1,1)
enddate
date(year(currentdate),month(currentdate)+1,1)-1

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

Hilfy gave you the answer earlier and I read over it soor Hilfy...

you cant change it to a text field then compare to a date field...

StartDate: DateAdd('m', -12, CurrentDate)

EndDate: CurrentDate

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

CoSpringsGuy,

After I make your changes...

startdate
date(year(currentdate)-1,month(currentdate)+1,1)
enddate
date(year(currentdate),month(currentdate)+1,1)-1


In the Report - Selection Formulas - Record

It's asking for a "A Date is Required Here", specifically in the {@EndDate}

Thanks.

Alpha7

 

[CoSpringsGuy]

then your db field is not a date field
try
todate({CallLog.RecvdDate}) >= {@StartDate} and
todate({CallLog.RecvdDate}) < {@EndDate}

using the formulas I gave you for start and end date

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

In Section Expert I had this formula...I since removed it.

{CallLog.CallID}=previous({CallLog.CallID})

Maybe this was causing a problem...


thanks.

Alpha7

 

[gennaroalpha7]

what is db field?

 

[gennaroalpha7]

CoSpringsGuy,

When I make all your (Post:5 Mar 13 14:45) changes in the Report - Selection Formulas - Record...and to StartDate and EndDate formulas..

In the Report - Selection Formulas - Record It highlights all the formula and indicates "The remaining test does not appear to be part of the formula"

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


Thanks.