Rolling Years 1

[Kevinski]

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.

 

[lbass]

You could use a select statement like the following:

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

-LB

 

[synapsevampire]

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)
and
{Orders.Order Date} <= cdate(year(currentdate),12,31)

You'll get much better performance.

-k

 

[Kevinski]

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 &quot;A number, boolean or currency amount is expected here&quot; at the first &quot;cdate&quot;

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....

 

[Naith]

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)
and
{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.

Naith

 

[synapsevampire]

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

-k

 

[Kevinski]

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 &quot;Number, currency amount, boolean or string&quot; 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?

 

[lbass]

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?

-LB

 

[Kevinski]

Sure, its

{Sales_Order.so_processing_date} >= cdate(year(currentdate)-1,1,1)
and
{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?

 

[lbass]

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 &quot;group options&quot; and then choose &quot;A column will be printed for each year.&quot;

-LB

 

[Naith]

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.

Try

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

and see what you get.

Naith