Skip to Content
Former Member
Jul 30, 2009 at 10:45 PM

use datediff with maximum function


I am using Crystal Reports XI with odbc driver to pull data from a remedy data source. 99% of our reports go against oracle and I can use the command object to build sql queries- which gives way more flexibility.

The report I need help with (going against remedy with ODBC) can't use the command object feature, so I am kinda stuck.

Since the tickets I am reporting on can have multiple submit dates and I only need the most recent I have a formula of

maximum({HPD_Search_Worklog.Submit Date2},{HPD_Search_Worklog.Incident Number}) called '@maxSubmitDate

I inserted it into a datediff function in the record selection

if (currenttime > Time (05,55 ,00 )

and currenttime < Time (06,05 ,00 ))


Datediff("h",{@maxSubmitDate},currentdatetime) = 15

else Datediff("h",{@maxSubmitDate},currentdatetime) = 3

And get this error: "This formula cannot be used because it must be evaluated later", which makes sense, but is vexing because it could easily be solved with sql in command object.

Basiclly, I need to have the report ran at 6,9,12,3 and only pull the max submit date for each ticket after the last report ran. I think it would work if I didn't have multiple submit days and have to use maximum function in crystal.

Can this be done with variables- something I have no experience with- Newbie to crystal- I played around with evaluation times (whileprintingrecords), but no luck.

Any insight would be greatly appreciated. I apologize if I did not provide enough info.