ranking

[SherryBuffalo]

Hello,
I am doing a report that I have to rank all the sales people based on their sales for the month. Is there a funtion I can use from CR? thank you.

Sherry

 

[ShortyA]

Hi Sherry,
you could use TopN functionality. Which version of CR are you using ? How are you intending to show the data eg grouped by Sales Person or in a crosstab etc ?

If you wanted to use TopN then you would need to do a summary on the amount of sales and group by sales person. Then from the Report menu (in CR8.5 at least) there is a TopN option which will allow you to either select the top 1,2,3...x or order the sales people by their sale.

ShortyA

 

[SherryBuffalo]

Hi,
thank you for your reply. I am using version 9. I could not find anything TopN. I have Nth largest and Nth smallest.

Sherry

 

[ShortyA]

Sherry,
are you able to provide a few rows of sample data and an indication of your report layout ? I was sure that the TopN functionality worked the same in CR9 with a summarised field but I will see if I can find a machine with CR9 on.

ShortyA

 

[Naith]

Crystal 9 does have TopN. It's the little button that looks like an arrow pointing on the top of a mountain peak.

Sherry, you can rank sales by clicking the A-Z button, and selecting a descending sort on sales.

Naith

 

[SherryBuffalo]

I found the following code which should be very useful. This is what I am doing to :
1) I created a field {#RTotalBookbyRep}in the running total to summarize the total booking for each sale person.
2) total count of the sales.
3) I created RepRanking and formular is :

Local NumberVar i;
For i :=1 To {#RTotalRep} Do
(
if nthlargest(i, {#RTotalBookbyRep}) then i
)

error are: {#RTotalRep} cannot be summarized.
i has to be a number for the i in the as
if nthlargest(i, {#RTotalBookbyRep}) then i

thank you for your time..

 

[lbass]

You cannot use running totals as a basis for ranking. You need to use inserted totals. Please provide a sample of how you want your report to look. Do you plan to display the salespeople in descending order by sales?

-LB

 

[SherryBuffalo]

this is what we would like the report will look
name booking rank
John 200 4
San 500 3
Lisa 1,000 2
Sherry 5,000 1

thank you.
sherry

 

[lbass]

Do you get the correct results if you right click on {table.bookings} in the detail section and insert a summary? If so, then you can go to report->topN/group sort and select "Sum of table.bookings" as your summary, ascending. To get the rank, use this formula:

whileprintingrecords;
numbervar x;

if groupnumber = 1 then
x := distinctcount({table.salesperson}) else
x := x - 1;

-LB

 

[SherryBuffalo]

lbass,
Like you suggested, I used inserted total. It still tells me that this field cannot be summarized.. The data would be ordered by sale persons' name. Thanks
sherry

 

[lbass]

Once you have inserted the sum on the detail field, you don't need to insert any further summaries. Just locate the group sort expert and choose "sum of {table.bookings}" as your sort field.

When you say that the data would be ordered by salesperson's names, are you changing your mind about sorting in ascending order of total bookings amount?

-LB