Help Ranking From a SubReport

[etseel]

I know I'm doing something dumb here, someone please help me. Using CR9 pro, SQL database.

I have a main report for my sales people (group level 1) where I want to display their Ranking in terms of YTD sales. For several reasons, I can't re-order my main report by their YTD sales volumes, and nobody has been able to help me write a formula to rank them in the main report, so I have chosen to pull that value from a subreport and display it on my main report. Each record in the table has details of a single transaction. Essentially we're only dealing with 2 fields here: SalesPerson and Commission.

Main Rpt - grouped by sales person, sorted alphabetically, with various calculated values displayed in GF1. I want to show their ranking against all other sales people here in this section of GF1 (Ranked most to least in terms of total commission $).

Sub Rpt - Grouped by the same sales person field, placed in GH1. Sum function totals commission $ placed in GH1. GH1 is suppresed. TopN sort All by Decreasing Commission $ Total. Formula @SalesRank = Groupcount, displayed in GF1 (the only field that is not suppressed. Their Rank shows up perfectly on this report, these are the values I want to display in my other report.

Then I insert subreport into my main report, linking by Sales Person field.

Problem: Rank is always = 1 for EACH sales person in the main report! What do I do? Thanks for any help.

 

[-cush-]

It always shows 1, because after linking the reports, the subreport is run for every person, and only pulls records for that person.

Why don't you want to do a topN sort on your main report by commision totals?

 

[lbass]

First, remove the subreport link on {table.salesperson}, but leave the subreport in the salesperson group footer. This will cause all ranks to repeat for each salesperson. Next, in the main report, create a formula:

whileprintingrecords;
shared stringvar name := {table.salesperson}

Place this in a section above where the subreport is located. In the subreport, go to format->section->section in which you have the ranks displayed->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar name;
name <> {table.salesperson}

This will suppress all but the appropriate rank for salesperson.

-LB

 

[etseel]

LB, YOU ARE THE ALL KNOWING ONE YET AGAIN! Thank you. Your solution worked great. Your posts have solved my problems on at least a dozen occasions, I am indebted.

kcushing, I am not able to use a TopN on that particular subreport due to the fact that managers have asked for a great deal of data in one place, spanning multiple years of transaction history. Because I need to define the period of the transactions, I'm forced to use either a formula or a running total instead of the Summary function to calculate YTD Commissions. Therefore I can't TopN.

Thanks to all who pondered a solution...

 

[etseel]

SAD ENDING TO THIS STORY

The good news: All of the subreports look great! All the shared variables pass wonderfully, and show the appropriate value for each sales person.

The bad news: When I put all of the pieces together in the master report, several of the variables aren't passed because they're nested 2 subs deep from the main report. Subreports can't have subreports ---> Only one layer deep is allowed.

Solution?: I am going to try and place some of my 2nd layer subreport calculations into the master level report and supressing them, then pass the variables into the 1st layer subreports. This will only work for 1 or 2 of the calculations, so I'll probably end up moving a couple my results from the subreport into the master report That won't match the management's instructions; but it will get them the all the #'s they want...

If that doesn't work, I guess I'll start a new thread on nested subreports....

 

[-cush-]

Please excuse me if I am not understanding, but you can group by and do a top N sort on a formula.

 

[lbass]

kcushing,

My interpretation of:

"I can't re-order my main report by their YTD sales volumes, and nobody has been able to help me write a formula to rank them in the main report,..."

...was that etseel was not supposed to change the order of the salespeople and that he/she couldn't figure out a way to add ranks by sales volume in the main report without using a topN, but maybe I misinterpreted...

-LB