Performing Ranking in Crystal Reports 2008
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:
2) Preview the report. The report will look like this:
3) Go to the Record sort expert and sort the Sales Revenue in descending order:
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.
5) Drag that New running total field “Test” in the report and see the results. The report will look like:
6) Go to the Section Expert in the Report tab.
7) Select the details tab and edit the formula Suppress(No Drill-Down):
8) Write the formula:
{Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Sales_revenue} < {#Test}
Click Save & Close.
9) The report will look like:
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.
Thus we have a report showing only top 10 records revenue wise.
Very good document
nice work
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 🙂
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:
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
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
Hi City City,
Please post your issue as a new discussion.
-Abhilash