Return only maximum fields for summary report

[Former Member]

I've got a challenging one here. I've been tasked with creating a summary report for all our estimates in a given time period. They want it as a summary by sales rep from highest value to lowest value. Normally this would be fairly straightforward, except that there can be multiple values of an estimate if we quote multiple quantities. I'm solving this by only using the highest value, but that creates a problem with the summary fields.

Right now, I have the report grouped by sales rep, then by estimate. In the group footer of the estimate, I have the max value of the price field. However, I can't use that calculated value as a summary field, which means I can't sort by it. I can't sort by formulas or running totals either.

Any ideas on how to sort groups by a calculated value?

Thanks.

[Former Member]

The whole sequence didn't do the trick, but I edited the SQL by hand and followed basically the same procedure to get the results. I didn't know about the 'Add Command' option. That made all the difference.

Thanks!

[former_member260594]

Hello Iain,

Try doing the grouping on the database side. The easiest way I have found to set this up is in your existing report;

1) Ensure there is a summary at the group level you want

2) Suppress the details section

3) In the Database menu select Perform Grouping on Server

4) Copy the SQL from the Show SQL Query.

Now create a new report with the same datasource and;

1) Select Add Command

2) Paste the query into it

3) Group on Sales rep

4) Place the Estimate field and the maximum price expression field into the details section.