1534771 - How to create a Crystal Report that displays top n records in a group (where n is any user defined value)

SAP Knowledge Base Article - Public

1534771 - How to create a Crystal Report that displays top n records in a group (where n is any user defined value)

Symptom

How to create a Crystal Report that displays top n records in a group (where n is any user defined value)

Environment

Crystal Reports XI R2 (All Patch levels)

Crystal Reports 2008 (All Patch levels)

Resolution

1. Create a sample Crystal Report based off the Xtreme Sample Database

2. In the details section drag the following fields: Customer Name, Country, Last Year Sales

3. Create a Group on Country (Report->Group Expert)

4. Click on ‘Record Sort Expert’ (Report-> Record Sort Expert) and add Customer.Last Year Sales in the ‘Sort Fields’

5. The ‘Sort Direction’ for this field should be ‘Descending’

6. Create a formula - ‘Counter Increment’ as follows:

    WhilePrintingRecords;

    numberVar x := x + 1;

7. Place the above formula on the Details Section

8. Create another formula – ‘Counter Reset’ as follows:

    WhilePrintingRecords;

    numberVar x := 0;

 9. Place the above formula on the Group Header #1

10. Create a Number Parameter called ‘Sort’; this Parameter would prompt the user for the value ‘n’ which would be used to display the top n records

11. Go to ‘Section Expert’ (Report->Section Expert)

12. Write a formula in the ‘Suppress (No Drill-Down)’ attribute for the Details section as follows :

       WhilePrintingRecords;

       numberVar x;

       x > {?Sort};

13. Refresh the report and enter any value at the Parameter Entry Screen (For eg: 10)

14. The Report will display Top 10 records based on the Last Year Sales for each country

 

Keywords

Top n records, Crystal Reports , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports XI R2