Customer Report by Ranking in Crystal reports

[funnie]

I want to create a crystal report based on the following specs:. The report contains customers assigned customer numbers and these customers have got some accumulated revenue. The customers are sorted in Descending order based on the accumulated revenue. I want to have a rank field for these customers but in cystal cant find the rank function. Then there are some customers with customers numbers but with no revenue accumulated these customers are sorted alphabetically and then assigned ranks according to their alphabetic order. The last group of customers contains customers not yet assigned customer numbers but sorted alphabetically then assigned ranks according to the alphabet. The report should rank customers with revenue accumulated and when it reaches those without revenue but with customer numbers it should start assigning new ranks from rank 1 to n. This should also be done to those customers with no customer numbers yet.

eg

Customer No: CustName Revenue Rank

A1 BBBBBB 200 1
D1 GSAHSG 150 2
C1 GSHDSJJK 100 3

D2 AAAAA 0 1
D4 BBBBB 0 2
E4 CCCCC 0 3

-- ABC 0 1
-- DEF 0 2
-- FGH 0 3

The report should produce the above results. I want to know the crystal reports formula to rank these fields as shown above.

 

[dgillz]

At what point in time does the rank formula need to reset to 1? This is not clear.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

I think you just need to insert a group (ascending) on a formula:

if isnull({table.custID}) then
3 else
if {table.revenue} = 0 then
2 else
if {table.revenue} <> 0 then
1

Then add a second group on customer name.
Add a group sort (descending) using sum of revenue at the name group level. Place the ID, name, and sum of revenue in the name group header and suppress the group #1 header. Finally, add a running total to the group header that is set up like this:

distinctcount of cust number, evaluate on change of group: customer name, reset on change of group #1.

-LB

 

[funnie]

The for rank formula will reset to 1 when the when it reaches the point when customer revenue = 0 and then it will rank the customers with customer numbers alphabetically but these customers have revenue = 0 , then lastly it will reset to 1 again when it reaches customers with no customer numbers and their revenue is = 0 , these customers are sorted alphabetically again.

 

[lbass]

I don't understand your response. What is your point?

-LB