Need "Top X" Customers in a Crosstab

building reports crystal reports
1

My users want a report with their top customers sorted from highest to lowest in terms of billings. The requirements are:

  1. Needs to be a crosstab due to how some customers have the same name
  2. Users should be able to choose whether they want the top 10, 25, or 50 customers in the output
  3. Users need to be able to choose one of four columns (i.e. total for each of three years and also customer name alphabetically) as their sort column
  4. I need to number each row after it is sorted (i.e. first row is #1 and 50th row is #50)

Any development tips here?

Thanks.

mikegbuff (BOB member since 2009-05-28)

2

Hello Mike,

You can create your cross tab using sample database that comes with CR XI. Use the customer table in the report and follow the following steps:

1.Go to Insert menu>> Cross Tab.
2.Place it in Report Header.
3.Select it and right click>>Cross Tab Expert.
4.Now select the Country filed and press > button beside rows.
5.Select Sales column and click > beside Summarized Field.
6.Click ok.
7.Preview the cross tab.
8.You will see country names and sales data.
9.Now goto page no 1 of the report.
10.Select the country field.Right click>> Format Field.
11.Click Common Tab.
12.Click the X^2 opposite to display string.
13.A formula workshop will appear.
14.write this code:
global NumberVar x;
x:= x+1;
totext(x,0) + " " + (CurrentFieldValue);
15.Click OK.
16.Preview the report.
17.You will be able to see row numbers along with country names.

Top X customers
1.Create a parameter of number type and name it “Top N”
2.Select the Cross Tab. Right Click>>Group Sort Expert.
3.Select Top N.
4.Select Sum of Customer Sales in the “based on” drop down.
5.Put 1 in the “Where N is”
6.Click X^2 and select the parameter created “Top N”.
7.Uncheck “Include Others, with the name”.
8.Click Ok.
9.Enter the value of parameter prompted.
10.Preview the report.

Let me know if you have any questions regarding this.

Thank you.
Regards
Manish Tiwari

cr_manish :india: (BOB member since 2009-07-30)