Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Rolling Years

Rolling Years

Rolling Years

I have a crosstab which categorises service types (row) by year (column) and records the amount of hours.

The service date is actually a datetime and I have converted it to year by formula: Year {datetime}

I only want to report on the current year and the previous year for comparison.

Is there any way OTHER than a select Year >= 2002 that I can always ensure only the last 2 years are being reported?

I don't want to have to come back on 1/1/04 and change my select statement to >= 2003.

RE: Rolling Years

You could use a select statement like the following:

year({table.date}) in year(currentdate)-1 to year(currentdate)


RE: Rolling Years

That will work, but if you check the Database-Show SQL Query you'll see that it does not pass the SQL to the database. To do so, use something like:

{Orders.Order Date} >= cdate(year(currentdate)-1,1,1)
{Orders.Order Date} <= cdate(year(currentdate),12,31)

You'll get much better performance.


RE: Rolling Years

lbasses solution works but yes, any time there is a refresh or any activity elsewhere on the report in design mode I get prompted for a refresh - I presume thats what you mean by lack of performance?

The problem is that if I substitute syanpsevampires I get an error message "A number, boolean or currency amount is expected here" at the first "cdate"

Incidentally I'm grouping by Year (#1) and then Month (#2)

I could live with the performance issue as long as it wouldn't cause problems when scheduling....

RE: Rolling Years

That isn't what's meant when talking about performance issues. Performance issues basically refers to the amount of time you find yourself waiting for the data to come back when you refresh your report. Because you're looking at a couple years worth of data, processing such a large period of data may take several minutes. If you can ensure that this passes to the database, you could turn minutes into seconds. (You can check if it's passing or not by going to Show SQL query in the Database menu. The aim is to see the period in the SQL WHERE clause.)

This suggestion:

{Orders.Order Date} >= cdate(year(currentdate)-1,1,1)
{Orders.Order Date} <= cdate(year(currentdate),12,31)

is supposed to be placed in your record selection criteria. There's no need to alter the formula at all, as it will already work for your needs. The way you group your data has no impact on this whatsoever.


RE: Rolling Years

Kevinski: I reviewed my post and it looks OK, perhaps you have a typo.

As Naith reiterated, LB's solution will work, but it does NOT allow the database to do the work, so Crystal's inferior engine will do so, hence the bad performance.

Anytime you add to the Report->Record Selection Formula->Record, make sure that the changes are reflected in the Database->Show SQL Query


RE: Rolling Years

Sorry guys but I'm still getting the error.

Here's what I'm doing:

Cut and paste the formula into the selection criteria (as a formula) for the field I want to classify by year (this field is actually called {Sales_Order.so_processing_date} so I've substituted this for the suggested {Orders.Orderdate}

But I'm definitely getting the "Number, currency amount, boolean or string" error - and I've had it both at the first cdate and in other places - it really does seem to change as to where its detecting this error.

Incidentally the field is actually a datetime field and I've tried substituting currentdatetime for currentdate in the formula.

So I'm pretty confident its not a typo but maybe I've given you bad info as regards date/ datetime?

RE: Rolling Years

SV and Naith are correct that my suggestion would be slower. I also think you might have a typo--maybe a missing paren.  How about cutting and pasting your record selection formula here?


RE: Rolling Years

Sure, its

{Sales_Order.so_processing_date} >= cdate(year(currentdate)-1,1,1)
{Sales_Order.so_processing_date} <= cdate(year(currentdate),12,31)

Although as I say I've also tried 'currentdatetime' because {Sales_Order.so_processing_date} is actually a datetime field.

So time for another recap:
I've grouped all sales orders by year and want to display only the current and last years counts. Originally I had one formula (@Year) which was Year ({Sales_Order.so_processing_date}) and then a selection statement which said @Year >= 2002.

Now I'm keeping Year ({Sales_Order.so_processing_date}) and trying this new formula as a selection statement against {Sales_Order.so_processing_date} and displaying @Year on my report as a Group Header - or in other reports where I'm producing a crosstab I want to use @Year as a column heading.......looking at this maybe I should get rid of @Year altogether?

RE: Rolling Years

I'm stymied--it looks like you've done everything correctly. Is this your selection formula in its entirety? And it is placed in the report->edit selection formula->record area? And you have verified that your datatype is in fact datetime?

As for {@year}, you don't really need it for the crosstab.  Just enter your date field as the column, then select it, and choose "group options" and then choose "A column will be printed for each year."


RE: Rolling Years

You'll generally get this error, or derivatives of this error, when the datatypes of the fields on either side of the operand do not match.

CDate({Sales_Order.so_processing_date}) >= cdate(year(currentdate)-1,1,1)
CDate({Sales_Order.so_processing_date}) <= cdate(year(currentdate),12,31)

and see what you get.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close