Trouble with RANKING using arrays

[etseel]

Can anyone help me here? Thanks for any help you can give!!! I am using CR9 Pro, hitting a SQL database with a single table that has all of the sales data over a period of years.

I have a single grouping by salesperson (FA). I have a running total formula in the group1 footer that totals the year-to-date sales for each FA called #YTD$.

My goal is to assign a ranking to each FA and display that next to their YTD total - the rank indicating 1 for the FA with the largest #YTD$, to N where N=Count(FA). So the guy with the 10th biggest sales would be ranked #10, etc.

The other key instructions are that I want to contain the formulas in this report and NOT IN A SUBREPORT because it is already part of a large report, and I can't afford to slow down processing time any further with another subreport. Ideally, I'd like to place them in the same group1 footer as the #YTD$.

I started by trying to use the formulas on p.13 of the BO document "Advanced Reporting Techniques Using Arrays". However the sample formulas only returned results of TRUE. That turned into a big mess, and I'm interested in starting from scratch rather than trying to salvage the formula I already have; but here it is anyway if someone would like to offer edits.
//First we grab the YTD total for each FA
whileprintingrecords;
numbervar array FATotal;
numbervar counter;
(Counter := counter +1;
if counter <=1000 then (Redim Preserve FATotal[counter];
FATotal[counter] := {#YTD$}));
//Now we examine the $total and rank them
numbervar array FARank;
numbervar counter1;
numbervar counter2;
numbervar temp;
numbervar array_size := count(FARank);

for counter1 := array_size to 1 step -1 do
(
for counter2 := 1 to counter1 - 1 do
(
if FATotal[counter2] >
FATotal[counter2 + 1] then
(
temp := FARank[counter2];
FARank[counter2] :=
FARank[counter2 + 1];
FARank[counter2 + 1] := temp;
)
);
);
FATotal[counter2]

 

[synapsevampire]

I would think that you could use a Top N report, and then for the ranking create a formula which simply does a count at the group level.

-k

 

[etseel]

TopN won't suffice in this case since the managers want the report in alphabetical order. Also this is actually a subreport within another main report, and I can't change the order of the data or it won't match the order in all the other pieces of the master report.

By the way I noticed that the last line of the report is clearly the wrong variable to display. What I really want to display is probably either FARank[counter1] or FARank[counter2], but I keep getting out of range errors.

Any other ideas?