use datediff with maximum function

[Former Member]

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

then

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.

Thanks-

[Former Member · Accepted solution]

use an sql expression to get your @maxSubmitDate then you would be able to use the maximum function.

[Former Member]

Can you try finding max submit date using sub report. use shared variable to use it in main report.

Hope this will help

Jyothi