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

 

[fisheromacse]

i do not have crystal in front of me at the moment, so apologize for any errors or misunderstanding i may have.

i think you should be able to create 2 formulas and then use them as your start and end date for the range.

something like this:

//{@startdate}
dateserial(year(currentdate)-1,month(currentdate)+1,day(1))

//{@enddate}
dateserial(year(currentdate),month(currentdate)+1, day(1)-1)

 

[gennaroalpha7]

Hi fisheromacse,

The report would run monthly and would need to report data from last full year, if you will. So, if I ran it today it would contain data from February 2012 through January 2013. If I ran it in March it would contain data from March 2012 through February 2013.

Here is my table...

({Calllog.RecvdDate})

Thanks.

Alpha7

 

[fisheromacse]

use the formulas in my first post, and in your select expert set your date field to be in the range {@StartDate} to {@EndDate}

If i were doing it, it would look something like this:


({Calllog.RecvdDate} >= {@StartDate})
AND
({Calllog.RecvdDate} <= {@EndDate})

 

[gennaroalpha7]

HI,

But, how are these: {@StartDate} to {@EndDate}

defined?

 

[pmax9999]

Fisheromacse's 2 formula need a liitle "tweak" to correct the syntax, then add the Record Selection as suggested in his latest post:

[Code @StartDate]
dateserial(year(currentdate)-1,month(currentdate)+1,1)
[/Code]
and
[Code @EndDate]
dateserial(year(currentdate),month(currentdate)+1,1-1)
[/Code]


Cheers
Pete

 

[fisheromacse]

DOH!

thanks Pete!!

 

[gennaroalpha7]

Hi, in Petes poste...

Where do I enter the code...sorry for the questions...I am new to Crystal Syntax.

Alpha7

 

[pmax9999]

In the Field Explorer, right click on Formula Fields and Select "New". Call the first formula StartDate, paste the code from @StartDate above and click on Save. Repeat this step for EndDate.

You wil then find that the 2 newly created formula will be available to place on the report and use in other formula, including the Record Selection.

Hope this helps

Pete.

 

[gennaroalpha7]

Hi,

When I enter:

({Calllog.RecvdDate} >= {@StartDate})
AND
({Calllog.RecvdDate} <= {@EndDate})

in the Record Selection I get...."This Name field is not Known"

and it highlights: {@StartDate})

Pete, I followed your instructions of your last poste. What did I do wrong?

Thanks.

Alpha7

 

[pmax9999]

Check that the name you gave the 2 formula (as they appear in the the Field Explorer) were StartDate and EndDate.

If you set the 2 formula up correctly, you will be able to 'pick' them from the list of available fields/formula. When referred to within formulas, the formula "StartDate" appears as "{@StartDate}".

If you are still having problems please post back explaining exactly what you have done.

Cheers
Pete

 

[gennaroalpha7]

Hello Pete,

I selected Report - Selection formulas - record and then copied and pasted...

({Calllog.RecvdDate} >= {@StartDate})
AND
({Calllog.RecvdDate} <= {@EndDate})


So it looks like this now...

date({Calllog.RecvdDate} >= {@StartDate}) AND
({Calllog.RecvdDate} <= {@EndDate}) and

{CallLog.KeyWord} in ["BOTW.COM", "Mobile Banking", "OBS", "OBS BOTW"]

Thanks Pete.

 

[gennaroalpha7]

Hi Pete,

I went to Report - Select Expert - Record and then set the RecvdDate to:

@StartDate to @EndDate

I think that's what you guys meant me to do.

I ran the report but it produced Zero results.

Did I go wrong somewhere?

Thanks for you help!

Alpha7

 

[pmax9999]

Please Copy and Paste the entire Record Selection formula into the post so we can see exactly how you have implemented the suggestions.

Also, as a test to see whether it is the "Date" or "KeyWord" components causing the issue, please try removing "and {CallLog.KeyWord} in ["BOTW.COM", "Mobile Banking", "OBS", "OBS BOTW"]" and post the results.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete,

Here's the (in the Record Selection Formula editor) Record Selection formula...

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


Thanks!

Alpha7

 

[gennaroalpha7]

Pete,

Also, under the Select Expert - tab "CallLog.RecvdDate"

I have "is between" - "@StartDate and @EndDate"

Hence, the formula in the above post...


Also, I did remove what you suggested in your last post and there were no results.

Thanks Pete.

P.S. Rest asured I am learning. Thanks to people like you.

 

[pmax9999]

There is something strange going on here. If you had created the 2 formulas correctly and used them in the record selection formula as intended they would show up as {@StartDate} and {@EndDate} respectively.

And the record selection formula line you have used "{CallLog.RecvdDate} in "@StartDate" to "@EndDate" " should return an error if {CallLog.RecvdDate} is a date or date/time field because your code is matching the field against 2 strings rather than 2 dates.

Please:

1. Confirm that {CallLog.RecvdDate} is a date (or date/time field) - place the field on the report canvas (if it isn't already there) and hover the mouse over the field - the data type will be shown in brackets beside the field name;

2. Paste some sample {CallLog.RecvdDate} data into a post;

3. Copy and paste the contents of the two date formulas into a post;

4. Confirm you can see the two formulas in the Field Explorer under Formula Fields;

5. Try replacing the line: {CallLog.RecvdDate} in "@StartDate" to "@EndDate" with {CallLog.RecvdDate} in {@StartDate} to {@EndDate}. Do this via Report >> Selection Formulas >> Record and post back with the results.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete,

1. This is on my canvas as a field... {CallLog.RecvdDate}, when I hover over it, it shows this "CallLog.RecvdDate(String)". But this field returns a hard date if I enter static dates in the Select Expert - Record - CallLog.RecvdDate tab, as in "1/01/2013 and 1/31/2012"

2. In the "Select Expert - Record - CallLog.RecvdDate" tab I entered hard dates "1/01/2013 and 1/31/2012" and recieved results. In the field "CallLog.RecvdDate" I received hard dates 1/8/2013, 1/7/2013 and others - three pages of data to be exact.

3. Here's the formula for EndDate...dateserial(year(currentdate),month(currentdate)+1,1-1) and StartDate...dateserial(year(currentdate)-1,month(currentdate)+1,1) In the Select Expert - Record I typed @StartDate and @EndDate. In the "Field Explorer - formula field" does it matter which is on the top...right now they are EndDate - StartDate in that order.

4. Yes, I can see the 2 formulas, "EndDate and StartDate" in the Field Explorer - formula field

5. When I do this it higlights "{@StartDate} to {@EndDate}" and says a "String is required here"

Note: I have Crystal Reports 8

Thanks Pete.

Alpha7

 

[pmax9999]

OK, that makes more sense.

I don't have access to CR to validate the syntax, so I hope this is correct. Amend the two formulas as follows:

ToText(dateserial(year(currentdate)-1,month(currentdate)+1,1), "MM/dd/YYYY")

ToText((dateserial(year(currentdate),month(currentdate)+1,1)-1), 'MM/dd/YYYY')

Then, copy and paste the following code into the record selection formula - so as to replace the existing code:

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

Let us know if there is any further problems.

Regards
Pete

 

[pmax9999]

Just had the opportunity to check my syntax and the uppercase YYYY should be lowercase yyyy in both formulas, ie:

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

and

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

Cheers
Pete