Skip to Content
Author's profile photo Former Member
Former Member
Posted on August 29, 2012 2 minute read

Performing Ranking in Crystal Reports 2008

Follow RSS feed
4 Likes 6,679 Views 6 Comments

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.

Assigned tags

Related Blog Posts

    Related Questions

      /
      1.JPG
      2.JPG
      3.JPG
      4.JPG
      5.JPG
      6.JPG
      7.JPG
      8.JPG
      9.JPG
      10.JPG
      6 Comments
      You must be Logged on to comment or reply to a post.
      • Author's profile photo Praveen Guntuka
        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 🙂

        • Author's profile photo Former Member
          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

        • Author's profile photo Former Member
          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