Create Top N Report

Top N reports are beneficial where there is a need to display only the specific number of top-most rows or records from a table or list. You can create this type of report by using the TopN operator in dataset filters or modifying the query while creating a dataset. TopN operator can be used in dataset filters for all data sources. Your data will be more meaningful if you also specify the order of rows.

Consider a scenario where the user wants to display the top ten movie records.

1. Create a new Page/RDLX report and bind the data to 'reels.db'. See Configure ActiveReports using Config file for more information. The data source connection string in the Report Data Source dialog will be as follows:

   data source = C:\Data\reels.db;

2. In the DataSet dialog, go to the Query tab and enter the following query to fetch the data ordered by user rating from highest to lowest:

```sql
SELECT * FROM Movie ORDER BY UserRating Desc
```

3. Navigate to the Filters page and fill in the following entries to fetch the top 10 movie records from the Movie table:

   Expression: =[UserRating]

   
   

   Operator: TopN

   
   

   Value: 10

4. Click OK to close the dialog.

5. Drag-drop the Table data region onto the design surface.

6. In the Report Explorer, expand the DataSet node and drag and drop the following fields inside the cells of the details row:

   [Title]

   [YearReleased]

   [UserRating]

7. Drag-drop a TextBox control for the report heading.

   
   

   
   

8. Preview the report. You'll see only 10 records in your report.

The following image illustrates Top N Report displaying top 10 movie records:

The following sections provide sample dataset queries to obtain top N data from other data sources.

Sqlite

SELECT * FROM Movie LIMIT 10

OLEDB, ODBC, SQL

SELECT Top 10 * FROM Movie

XML

//countries/country[position() <= 10]

JSON

$.Customers[:10]

See Also

Table

Data Binding in Page/RDLX Reports