Top N Sort by calculated percentage

[staceyn]

Hello,
I am using CR XI Rel 2 with an informix 10 database.
I am creating a "Hot sellers report".
The main report show qty sold by color and style.The report is grouped by vendor, style, color. There is a subreport that shows On hand quantities by color and style.
I have a variable formula to bring in the on hand data from the subreport.

@TotalOH
whileprintingrecords;
shared numbervar TotalOH;
TotalOH:=Sum ({@OH}, {inv_detail.id_color})

There is a formula in the main report to show total sold quantity:
@QtySold
{salessum.ss_size1}+{salessum.ss_size2}+{salessum.ss_size3}+{salessum.ss_size4}+{salessum.ss_size5}+{salessum.ss_size6}+{salessum.ss_size7}+{salessum.ss_size8}+{salessum.ss_size9}+
{salessum.ss_size10}+{salessum.ss_size11}+{salessum.ss_size12}+{salessum.ss_size13}+ {salessum.ss_size14}+{salessum.ss_size15}+{salessum.ss_size16}+{salessum.ss_size17}+
{salessum.ss_size18}+{salessum.ss_size19}+{salessum.ss_size20}+{salessum.ss_size21}+
{salessum.ss_size22}+{salessum.ss_size23}+{salessum.ss_size24}

Then there is a calculation for CO%.

@CO
@qtysold+TotalOH

@CO%
if @CO <> 0
then
@qtysold/@CO*100

All of this is working great and I am getting the correct figures.
Here is the problem. I need to be able to allow the user to select a CO% > (% entered by user via a parameter prompt)
I also need to be able to sort the report by highest to lowest CO% in both the vendor and color groups.
Since the values in this calculation aren't summarized, I am unable to use the value in selection or sorting criteria.
Is this going to be possible to do in this report?
Any ideas on how to get there?

Thanks in advance for any assistance,
Stacey

 

[staceyn]

Anyone have any ideas on this issue?

Thanks,
Stacey

 

[lbass]

Doing a topN sort based on calculated percentages is difficult in and of itself. However, the fact that you are using a shared variable as part of the calculation makes this a virtual impossibility, since you can't sort by fields passed from subreports. You would have to somehow eliminate the subreport.

-LB