Using maximum in Record Selection

[PinaDiodati]

Hi,

I would really appreciate help with the following;

I'm using Crystal Reports XI and a HEAT database. I need to show the percentage of Heat tickets that are resolved by desktop support within the target date & time. To do this I ask for all assignments to desktop support but there may be more than one assignment per ticket.

I need for the Report to only select the last assignment to desktop support created for each ticket (callid).

The assignment table has a HEATSEQ field (numbering each assignment), but if I use the formula {ASGNMNT.HEATSEQ}=maximum({ASGNMNT.HEATSEQ},{CALLLOG.CALLID}) in Record Selection, I get the error "This function cannot be used because it must be evaluated later".

How can I get the report to only select the last assignment for each call record? I could also determine the last assignment by assignment date & assignment time.

Thanks

 

[hilfy]

You can't do this unless you write a SQL Command. You have two options:

1. Write a command that will get you info on just the most recent assignments for all tickets then use this to link to the other tables in your report.

2. Write a command that brings in all of the data you need for your report and use this instead of using the tables.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

You could use the command option, or you could create a SQL expression (field explorer->SQL expression) if your database supports this. You would enter the following for {%maxseq}:

(
select max(`HEATSEQ`)
from ASGNMNT A
where A.`CALLID` = ASGNMNT.`CALLID`
)

...assuming there is a callID field in the asgnmnt table. You would have to change the punctuation to conform to your datasource.

Then in the selection formula you would enter:

{ASGNMNT.HEATSEQ}={%maxseq}

-LB