Creating Master-Detail Reports with SubReports

Using SubReports in the Web Report Designer allows you to embed reports within other reports for creating master-detail hierarchies and reusable report components.

The following example demonstrates how to create a master-detail report using SubReports. The master report (CategoriesProducts.trdp) contains a table with Northwind Categories data. The SubReport (ProductsReport.trdp) displays Northwind Products records filtered by the respective CategoryID.

To follow along with the steps below, you need access to an instance of the Northwind database for Microsoft SQL Server.

The next video demonstrates how to create master-detail reports by using SubReports in the Telerik Web Report Designer.

Step 1: Building the Child Report

1. Start with a blank report (ProductsReport.trdp) that will serve as the child report. Select the header and footer sections and delete them.

   

2. Add a new SQL Data Source populated with the Northwind.Products table:

   Here is the SQL query that selects the respective data:

   SQL

   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]

3. Set the DataSource of the report using the created SQLDataSource:

   

4. From the Components tab, drag two TextBox report items to the Detail section:

   4.1. Select each of the TextBoxes.

   4.2. Bind the Value property of the TextBoxes to the ProductName and UnitPrice fields respectively.

   4.3. Use the Expression dialog to set the desired field.

   

5. Preview the report to verify that all Products are listed.

6. Add an integer report parameter called ProductCategoryID that you will use to filter by product:

   

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

   SQL

   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). You will use it as a child report.

Step 2: Building the Master Report

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

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

   SQL

   SELECT
       [dbo].[Categories].[CategoryID], 
       [dbo].[Categories].[CategoryName], 
       [dbo].[Categories].[Description], 
       [dbo].[Categories].[Picture]
   FROM [dbo].[Categories]

3. Select the report's Detail section and use the Table Wizard to add a table bound to the just added SQLDataSource:

   

4. Select the Picture header and change its text to Products.

5. Select the data cell bound to the Picture field ([=Fields.Picture]) and add a SubReport item by selecting it from the Components tab:

   

6. Specify the Inner Report for the SubReport item and select the Uri option that refers to the previously created ProductsReport.trdp:

   

7. Set up the relation between the parent and child reports:

   7.1. Adjust the parameters.

   7.2. Link the SubReport parameter (ProductCategoryID) with the master report's CategoryID field.

   

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

   

See Also

• Web Report Designer
• Data Sources