ActiveReports 14
ActiveReports 14 User Guide / How To / Section Report How To / Section Report Scenarios / Create Top N Reports
In This Topic
    Create Top N Reports
    In This Topic

    In a section report, in order to display only the top N number of details on a report, you can manipulate the data pulled by your SQL query.

    To set an access data source to pull Top N data

    1. On the design surface, click the DataSource Icon in the detail section band to open the Report Data Source dialog.
    2. On the OLE DB tab of the Report Data Source dialog, next to Connection String, click the Build button.
    3. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
    4. Click the ellipsis (...) button to browse to the NWind database. Click Open once you have selected the appropriate access path.
      Note: The NWIND.mdb file can be downloaded from GitHub: ..\Samples14\Data\NWIND.mdb.
    5. Click OK to close the window and fill in the Connection String field.
    6. Back in Report Data Source dialog, paste the following SQL query in the Query field to fetch Top 10 records from the database.
      SQL Query
      Copy Code
      SELECT TOP 10 Customers.CompanyName, Sum([UnitPrice]*[Quantity]) 
      AS Sales 
      FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
      INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
      GROUP BY Customers.CompanyName 
      ORDER BY Sum([UnitPrice]*[Quantity]) 
      DESC
      
    7. Click OK to return to the report design surface.

    To add controls to display the Top N data

    1. In the Report Explorer, expand the Fields node, then the Bound node.
    2. Drag and drop the following fields onto the detail section and set the properties of each textbox as indicated.
      Field Text Location Miscellaneous
      CompanyName Company Name 0.5, 0
      Sales Sales 5, 0 OutputFormat = Currency
    3. Go to Preview tab, to view the result.

    A report with the Top 10 companies' data similar to the following will appear in the preview.

    See Also