Ranking of data compared to other group totals, and report totals 1

[LLBean123]

Hi,

I have a report with a hidden detail section, which contains sales information for each salesperson. The Group Footer shows total revenue per salesperson, total profit per salesperson, and total number of orders per salesperson.

The Report Footer shows total revenue, profit and number of order for all salespeople.

I would like to figure out a way I can show, in the Group Footer, each person's Ranking, compared to everyone else.

For example (simplified):

Person 1

$1,000​

Ranking would be 3 of 3 (worst)​

Person 2

$1,500​

Ranking would be 2 of 3​

Person 3

$3,000​

Ranking would be 1 of 3 (best)​

TOTAL

$5,500​

Any help would be appreciated. Thanks!

 

[pmax9999]

One way would be to use the Group Sort expert, but that would mean you the salespeople would be listed in the order of their revenue results. Assuming you want them listed in some other order but still showing the ranking, I achieved it doing the following.

1. Create a sub-report ("S/R")with the same record selection, that groups by the salesperson and totals the revenue;
2. In the "S/R", use the Group Sort expert to rank the salespeople on total revenue;
3. Create the following formula and place it in the Group Header:

WhilePrintingRecords;

Shared StringVar Rank1;
Shared StringVar Rank2;
Shared StringVar Rank3;
Shared StringVar Rank4;

Select GroupNumber
Case    1 : Rank1 := GroupName ({Table.Salesperson})
Case    2 : Rank2 := GroupName ({Table.Salesperson})
Case    3 : Rank3 := GroupName ({Table.Salesperson})
Case    4 : Rank4 := GroupName ({Table.Salesperson})

4. Place the SR in the Report Header of the main report, Hide all of its sections, format it to "Hide Blank Sub report";

Create the following formula in the main report and place in the GH/GF for the salesperson group:

WhilePrintingRecords;

Shared StringVar Rank1;
Shared StringVar Rank2;
Shared StringVar Rank3;
Shared StringVar Rank4;

If      GroupName ({Table.Salesperson}) = Rank1
Then    "Ranking: 1"
Else
If      GroupName ({Table.Salesperson}) = Rank2
Then    "Ranking: 2"
Else
If      GroupName ({Table.Salesperson}) = Rank3
Then    "Ranking: 3"
Else
If      GroupName ({Table.Salesperson}) = Rank4
Then    "Ranking: 4"

Does this give you the result you are looking for?

Hope it helps.

Cheers
Pete

 

[LLBean123]

You solved it, Pete! Thank you SO much!