In a , 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 , click the DataSource Icon in the section band to open the Report Data Source dialog.
![](images/SectionReport_DataSourceIcon.png)
- 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 12\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
|
- 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 , to view the result.
A report with the Top 10 companies' data similar to the following will appear in the preview.
See Also