SUB REPORT

Hello,

The SubReport item enables you to display reports within reports and serves as a container, similar to the report sections, growing in size depending on its children. Please refer to the following help articles demonstrating how to setup a sub-report: 

SubReport Item Overview.

Create Master-Detail Reports with SubReports

I have prepared a sample .trdp report to achieve master-detail report (CategoriesProductsSubreport.trdp) using a SubReport Item (ProductsReport.trdp). The master report contains table filled with Northwind.Categories table. The SubReport item shows the Northwind.Products records filtered by the respective CategoryID:

The report is achieved by using the following steps:

1. Start with a blank report:

2. Select the header/footer section and delete them by using the context menu:

3. Select the Data tab and add a new SQL Data Source populated with the Northwind.Products table:

Default SQL query:

SELECT
	[dbo].[Products].[ProductID], 
	[dbo].[Products].[ProductName], 
	[dbo].[Products].[Discontinued], 
	[dbo].[Products].[SupplierID], 
	[dbo].[Products].[CategoryID], 
	[dbo].[Products].[QuantityPerUnit], 
	[dbo].[Products].[UnitPrice], 
	[dbo].[Products].[UnitsInStock], 
	[dbo].[Products].[UnitsOnOrder], 
	[dbo].[Products].[ReorderLevel]
FROM [dbo].[Products]

4. From the Data Explorer (on the right side), drag the ProductName and UnitPrice fields to the report's detailSection:

5. Preview the report to see that all Products are listed:

6. Add an integer report parameter ProductCategoryID which will be used for filtering the products:

7. Update the SELECT query of the added SQLDataSource and add a WHERE clause using a SQL parameter ( which is mapped to the previously created report parameter):

SELECT
	[dbo].[Products].[ProductID], 
	[dbo].[Products].[ProductName], 
	[dbo].[Products].[Discontinued], 
	[dbo].[Products].[SupplierID], 
	[dbo].[Products].[CategoryID], 
	[dbo].[Products].[QuantityPerUnit], 
	[dbo].[Products].[UnitPrice], 
	[dbo].[Products].[UnitsInStock], 
	[dbo].[Products].[UnitsOnOrder], 
	[dbo].[Products].[ReorderLevel]
FROM [dbo].[Products]
WHERE [dbo].[Products].[CategoryID]=@sqlParamCategoryId

8. Save the report (ProductsReport.trdp). This would be used as our sub-report

9. Create a new blank report (CategoriesProducts.trdp)

10. Add a new SQL Data Source filled with the Northwind.Categories table:

11. Select the report's detailSection and use the Table Wizard to add a table bound to the just added SQLDataSource:

12. Select the "Picture" header and change its text to "Products":

13. Select the data row >> the cell bound to the Picture field and add a SubReport item:

14. Right-click the SubReport item and select the report Source option:

15. Select the URL option and select the previously created ProductsReport.trdp:

16. Click the Edit Parameters button and link the SubReport parameter (ProductCategoryID) with the master report's CategoryID field:

17. Save the changes and preview the master report. You will see all categories listed with the respective products for each category:

I hope you find this information helpful. Please, let me know if there is anything else I can assist you with.

Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik