CrystalReport group on date

Question

I'm trying to create a report of some data. The data has be grouped in 2 sections: has already been done (enddata <= today) and has not been done yet (enddate > today).

I created a group with groupexpert and added grouping on enddate, on specific order. Added named group 'done', with formula 'is less than or equal to' but I can't set the value to 'CurrentDateTime'. It get cleared after save.

Is there a way to get this working (maybe wrong syntax or complete different function)?

Answer 1

The way I'd probably handle this is by creating a new field in my query that would return a value if the date is less than or equal to today and another if it is greater than today. Some thing like this using SQL Server:

Original Query:

select field1, field2, enddate
from table1

New Query:

select field1, field2, enddate, 
case when enddate <= GETDATE() then 'complete' else 'incomplete' end as CompleteStatus
from table1

Then when you pass the data in you can group by this new column (CompleteStatus) to separate the completed data from the incomplete data.

Another way to do the same thing if you can't change the incoming data is to create a formula field with the formula:

{Table1;1.enddate} <= today

Then you should be able to create a group on this field.

Either way should get you what you need, but the first way I'd presume to run a little faster. Hope this helps.