datediff with max function crystal reports

[vancoug]

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-

 

[IanWaterman]

YOu should be able to use the formula in the Group Selection.

However, this only suppresses record set so take cr when summarising data.

Alternatively write a command and add that to report to perform this bit of filtering

Ian

 

[lbass]

Even in the group selection formula area, you might need to change your formula to remove the nested summary:

if (currenttime > Time (05,55 ,00 )
and currenttime < Time (06,05 ,00 )) then
Datediff("h", maximum({HPD_Search_Worklog.Submit Date2},{HPD_Search_Worklog.Incident Number}),currentdatetime) = 15
else Datediff("h", maximum({HPD_Search_Worklog.Submit Date2},{HPD_Search_Worklog.Incident Number}),currentdatetime) = 3

-LB