Group sort on formula

[danausten]

Hi,

CR10
SQL Server 2000

I have a report which contains a group with 2 summaries. The summaries are distinct counts. I have a formula which is located in the group footer which is an average of the 2 summaries and I want to sort the group by the average. What is the best way of doing this?

e.g.
Offices Sales Average
Sales Person 1 10 100 10
Sales Person 2 20 100 5
Sales Person 3 10 150 15
Sales Person 4 5 80 16

And what I want to see is

Offices Sales Average
Sales Person 4 5 80 16
Sales Person 3 10 150 15
Sales Person 1 10 100 10
Sales Person 2 20 100 5

Thanks in advance

 

[lbass]

I'm not sure of the syntax to get the distinct count in SQL Server, but the general approach I would take would be to use "Add Command" as a data source, and write a select statement that returns the distinctcount of office and the distinctcount of sales. Then in the report, create a formula for the average, as in {@ave}:

{command.expression1}/{command.expression2}

Next insert a maximum on this formula so that you can use topN where you would select "maximum of {@ave}" as your group sort field.

-LB