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

On the design surface, click the DataSource Icon in the detail section band to open the Report Data Source dialog.
On the OLE DB tab of the Report Data Source dialog, next to Connection String, click the Build button.
In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.

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]\GrapeCity Samples\ActiveReports 9\Data\NWind.mdb

Click OK to close the window and fill in the Connection String field.

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

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

Click OK to return to the report design surface.

To add controls to display the Top N data

In the Report Explorer, expand the Fields node, then the Bound node.

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

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.