Skip to Content

Performing Ranking in Crystal Reports 2008

August 29, 2012 | 1,401 Views |
 crystal reportcrystal reportssap business objectssap crystal reportsap crystal reports
Follow

Ranking functionality is not as straight forward when it comes to Crystal Reports. In webi we can directly use the Ranking option present in the toolbar to perform ranking. I had a requirement in my project to select top 10 records based on some measure value. There might be many solutions available, but I came up with the following one:

I have used the e-Fashion universe for demonstration purpose.

Steps:

1) Create a simple crystal report using the fields as shown below:

1.JPG

2) Preview the report. The report will look like this:

2.JPG

3) Go to the Record sort expert and sort the Sales Revenue in descending order:

3.JPG

4) Create a new running total field “Test” with the following parameters:

·                               Field to summarize: Sales Revenue

·                               Type of Summary: Nth Largest

·                               N Is: 10 (as per requirement)

             Click Ok.

4.JPG

5) Drag that New running total field “Test” in the report and see the results. The report will look like:

5.JPG

6) Go to the Section Expert in the Report tab.

6.JPG

7) Select the details tab and edit the formula Suppress(No Drill-Down):

7.JPG

8) Write the formula:

           {Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Sales_revenue} < {#Test}

Click Save & Close.

8.JPG

9) The report will look like:

9.JPG

10) Remove the running total field from the report as we don’t require it. Add the Record Number field from the Special fields & name it as Rank.

10.JPG

Thus we have a report showing only top 10 records revenue wise.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  3. Praveen Guntuka

    Hi All,

    We can achieve this by doing the following steps also,

    1.       1.       Sort the sales revenue by Descending order.

    2.       2.       Place the Record Number field form the Special Fields.

    3.       3.       Go to the Section Expert in the Report tab

    4.       4.       Select the details tab and edit the formula Suppress(No Drill-Down

                 5.       Write the below formula:

                                  if RecordNumber>10 then true

                 6.       Click Save & Close 

                 7.       It will show the top 10 records.

    –Praveen G 🙂

    (0) 
    1. Former Member Post author

      Hi Praveen,

      Thanks for your inputs. 🙂

      Your method works fine when there is simple report as the one shown in my document. But when you have grouping done in the report then it will not work. This is because record number will not reset when the group changes. For that you need to do following steps in the running total field:

      1. Click the first arrow button to add the field to the Field to summarize box.
      2. In the Evaluate section of the dialog box, click For each record.
      3. In the Reset section of the dialog box, click On change of group and enter the group name you have created.
      4. Click OK to save the running total field.

      Now use that running total field in the section expert. It will give top ten records for each group.

      I think I should have included this in my document.  🙂 🙂 🙂

      Regards,

      Neeraj Sharma

      (0) 
    2. Former Member

      Hi Praveen,

      I tried the steps you mentioned just that I have to show Top 10000 records (Silly req) but its a kind of data dump used for other report. Mine is CR 2013 SP5 & I am new to CR reporting

      1.      1. Sort the sales revenue by Descending order – Done

      2.       2.       Place the Record Number field form the Special Fields – Placed Record Number field to Details section from special fields

      3.       3.       Go to the Section Expert in the Report tab – Done

      4.       4.       Select the details tab and edit the formula Suppress(No Drill-Down – Done & checked Suppress (NO DRILL DOWN CHECKBOX)

                   5.       Write the below formula:

                                    if RecordNumber>10 then true – I mentioned 10000 instead of 10

                   6.       Click Save & Close – Its showing me 13000 records now


      It



      (0) 

Leave a Reply