Top 10 sort on running total percent 1

[jjenright]

I am using Crystal 10. I need to show the top 10 buyer percentages based on the following formula:

if {#Total ALL Item Dollars by Buyer}=0 then 0 else
({#Total Special Item Dollars by Buyer}/{#Total ALL Item Dollars by Buyer})*100

How can I show the top 10 buyers based on the percent of special items each buyer has accross all buyers ?

The two running totals sum amounts identified as special items in the numerator and the grand total in the denominator.

Thanks,
John

 

[lbass]

You cannot use running totals for group sorts. You would have to create a SQL expression {%spec} like this:

(
select sum(`Amt`)
from table A
where A.`field` = 'special item' and
A.`buyer` = table.`buyer`
)/
(
select sum(`Amt`)
from table A
where A.`buyer` = table.`buyer`
)*100

You would then place this in the detail section and insert a maximum on it (to make the group sort available), and then go to report->group sort->choose maximum of {%spec} as your group sort field.

The syntax/punctuation in the SQL expression will be specific to your datasource.

-LB