Sorting on a Subreport Field / Multiple Sorts

[donagins]

I need to sort, summarize, and filter data, then sort again on a different criteria. I want to join two tables, showing only the most recently dated record for each customer. I then want those records sorted by date.

I group the result set first by customer, and have a subreport that finds the single most recent record for each customer.

The roadblock I've hit is that I can't get the overall date sort to work. I can't sort by the result of the subreport. I've also tried Formulas, but no luck there either. When I go into the sort wizard, I can't override the group sorts with the date sort.

Am I overstepping the capabilities of Crystal?

 

[lbass]

There are other ways to limit the report to only the most recent date, but the approach depends upon your version of CR, so please share what it is.

-LB

 

[donagins]

We have 10.0.0.533

Thanks!
Don

 

[lbass]

One approach would be to create a commnad. Go to database->database expert->your datasource->add command and enter something like:

Select max(table.`date`) as maxdate, table.`customer`
From `table` table
Group by table.`customer`

Then link {command.customer} to {table.customer} and{command.maxdate} to {table.date}. Select the join and choose "enforce both". I know you're working with more than one table, but this is an approach you should be able to adapt. The syntax/punctuation in the command will depend upon your datasource and connectivity. If in doubt, check a similar report->database->show SQL query and observe how the tables/fields are punctuated there.

This will limit your report to the most recent date within the customer group and then you can group on the date as you normally would.

-LB