Sorting on a Formula

[cdennis321]

I am running Crystal 8.5 on an SQL server.

I am creating a report that lists clients that are assigned to a specific provider. I want the report grouped by provider with the following setup:


GrpHdr Provider A 10 Clients

Client Last date # Days from
of Service Current date

Detail John Smith 5/23/2004 30
Bart Simpson 6/1/2004 23
Mary Brown 6/22/2004 2


The fields that I am using are {@Client}, {RECORDED_SERVICE.STARTTIME} and {@Aged} (THESE ARE LOCATED IN THE DETAILS SECTION OF THE REPORT)

{@Aged} = currentdate - {RECORDED_SERVICE.STARTTIME}

What my problem is is that in order to get the last date of service recorded, I need to sort the field {RECORDED_SERVICE.STARTTIME} in descending order, but I also need to sort all the clients based on the {@Aged} formula. If I take out the sort on {RECORDED_SERVICE.STARTTIME} then the dates are not correct which makes the formula {@Aged} incorrect also.

Could someone please tell me where I am going wrong in this set up?

Thanks

Connie D

 

[lbass]

Insert a group on provider and then a second group on client. Sort the date in descending order, and then add your formula {@Aged} to the report canvas.

Right click on {@aged}->insert summary->minimum. Then go to report->topN/group sort and for the client group tab, choose "minimum of @Aged", descending order.

Next go to report->edit selection formula->GROUP and enter:

{RECORDED_SERVICE.STARTTIME}= maximum({RECORDED_SERVICE.STARTTIME},{table.client})

This will return only the record with the most recent date and the minimum {@aged} for the client, displayed in descending order by {@aged}. Suppress the group header and footer to get your desired display.

-LB

 

[cdennis321]

Thanks! It worked!

Connie D