Collapse All Expand All

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 sample NWind.mdb data file is located in: [User Documents folder]\ComponentOne Samples\ActiveReports 8\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.

Support Forum