Record Selection on the Maximum of a date field 1

[Bennie47250]

Using Crystal Version 7.0 on a MSSQL 7.0 database.

Needing help determining how to select only the records I want.

The report I working with is joining together 4 tables. One of the tables, among other things, logs the date and status of the ticket. A ticket can have several different statuses and the same status can be used several different times.

Example of the status table

Ticket # Date Status
258 1/2/2005 Open
258 1/2/2005 Pending
258 1/15/2005 Pending
258 1/20/2005 Holding
258 2/5/2005 Pending
258 2/10/2005 Closed

For this report, we want to show calls that the Last occurrence of pending was on a specific date or in a given month.

What I have done is created a formula that is in the detail section of the report that is “if {Status Table.Status} = 'Pending' THEN{ Status Table.Date} This will give me the all the dates of only the statuses of Pending. This formula is named “PendingDates”

I then created another formula to only give me the maximum date of the above formula. This formula is “Maximum ({@PendingDates}, {TicketLog.Ticket#})) This formula is named “MaxPendingDate” and it is in the Group Footer

As I was building these formulas, it was my intent to select records based off the formula MaxPendingDate. However when I try to do this, I receive the error message “This formula can not be used because it must be evaluated later”

Thru my working with the report I have found that if I right click on the formula “PendingDates” and select Insert Summary and select the Maximum of @PendingDates, it puts in a field titled “Max of @ PendingDates” in the Group Footer. I can now select records based off this field; however I’m only able to select “group selection” off this field.

When I ran the report using this as the “group selection”, the report evaluated over 86,000 records (the number in the lower right hand corner of the report when it is finished running and over 500 tickets shown in the group tree) but it only showed the 11 records that were at a status of pending for 3/28/05.

I’m looking for better ways to select the records.

Thanks

 

[lbass]

Assuming you have a group on ticket#, then the group selection formula should be:

{table.date} = maximum({@pending},{table.ticketno})

If you had limited the record selection formula to pending status, then you could have dispensed with the {@pending} formula.

I don't quite know why you would want to evaluate the last pending status for a ticket no if the current status is closed, but assuming you really want to do that, you could also (alternatively) try a SQL expression like the following {%maxpending}:

(select max(AKA.`date`) from Table AKA where
AKA.`ticketno` = Table.`ticketno` and
AKA.`status` = 'Pending')

Replace "Table" with your table name and "date","ticketno", and "status" with your actual field names. Leave "AKA" as is, since it is an alias table name.

Then you could go the RECORD selection formula and enter:

{table.date} = {%maxpending}

...which should return only those records you are looking for.

-LB

 

[Bennie47250]

LB, You are correct, we do want to report on the last instance of pending even if the ticket is closed.

So I guess the only way to tackle this issue is to do a group selection rather than a record selection, other than the SQL approach.

The group selection will take longer for the report to run as it is looking at a greater number of records, but if that is the best approach, so be it.

Thanks for the review and suggestion.

 

[lbass]

If you can use a SQL expression that should be even faster.

-LB