How to create master detail report using subreport in Report Designer?
This documentation explains the step-by-step procedure to create master-detail report that displays the detail records from a sub-report.
Create and customize the detail report
As a first step, open the Bold Report Designer.
To add parameters to the detail report, click
Parameters
. Then, clickNew parameter
in the parameter panel.Now, the following wizard will be displayed.
Specify the parameter name as
ProdSubCat
, the prompt asProdSubCat
and set visibility tohidden
.Then, click
Save
.Similarly, do the same to add the parameter
ProdModel
.Connect to the data source using this documentation link.
Connect to the dataset using this documentation link.
The
ProductCatalog
dataset is created using the following query.
SELECT top 60 PS.Name AS ProdSubCat, PM.Name AS ProdModel, PC.Name AS ProdCat, PD.Description, PP.LargePhoto,P.Name AS ProdName,P.ProductNumber, P.Color, P.Size, P.Weight, P.StandardCost,P.Style,P.Class, P.ListPrice FROM Production.Product P INNER JOIN Production.ProductSubcategory PS INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN Production.ProductProductPhoto PPP ON P.ProductID = PPP.ProductID INNER JOIN Production.ProductPhoto PP ON PPP.ProductPhotoID = PP.ProductPhotoID LEFT OUTER JOIN Production.ProductDescription PD INNER JOIN Production.ProductModel PM INNER JOIN Production.ProductModelProductDescriptionCulture PMPDCL ON PM.ProductModelID = PMPDCL.ProductModelID ON PD.ProductDescriptionID = PMPDCL.ProductDescriptionID ON P.ProductModelID = PM.ProductModelID WHERE (PMPDCL.CultureID = 'en') and PS.Name = @ProdSubCat and PM.Name = @ProdModel
Drag the table report item and configure the field as shown in the following image.
You can download the created Detail report from here, Sample report.
Create a master report
Open the Bold Report Designer.
Connect to the data source using this documentation link.
Connect to the dataset using this documentation link.
The
ProductCatalog
dataset is created using the following query.
```csharp SELECT top 60 PS.Name AS ProdSubCat, PM.Name AS ProdModel, PC.Name AS ProdCat, PD.Description, PP.LargePhoto,P.Name AS ProdName,P.ProductNumber, P.Color, P.Size, P.Weight, P.StandardCost,P.Style,P.Class, P.ListPrice FROM Production.Product P INNER JOIN Production.ProductSubcategory PS INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN Production.ProductProductPhoto PPP ON P.ProductID = PPP.ProductID INNER JOIN Production.ProductPhoto PP ON PPP.ProductPhotoID = PP.ProductPhotoID LEFT OUTER JOIN Production.ProductDescription PD INNER JOIN Production.ProductModel PM INNER JOIN Production.ProductModelProductDescriptionCulture PMPDCL ON PM.ProductModelID = PMPDCL.ProductModelID ON PD.ProductDescriptionID = PMPDCL.ProductDescriptionID ON P.ProductModelID = PM.ProductModelID WHERE (PMPDCL.CultureID = 'en') ```
Drag the table report item and configure the fields. Then, add groups in the tablix to display the data using this documentation link.
The initial design of the report is displayed as shown in the following image.
You can download the previously created master report from here. Sample master report.
Link the subreport to the master report
The following steps explain how to link sub-report (product category detail records
) to a master report.
Right-click on the last row (
Description
) and selectInsert Row
, then click theInside Group-Below option
.In the newly added row, select all the cells using
Ctrl Key + Mouse left click
combination.After selection, right-click any selected cell, and then click
Merge Cells
.Right click on newly added row and select Insert, then click Subreport.
Select the sub-report item in the design surface and open the
properties panel
.Under the Basic Settings, copy the sub-report path and paste it in the Report field.
Then Click
Set Parameters
, followed by theAdd
button.Specify the Parameter Name to
ProdSubCat
and value to=Fields!ProdSubCat.Value
.Similarly, do the same for parameter
ProdModel
and value as=Fields!ProdModel.Value
.
Click on the Preview at the top-right corner of the Report Designer toolbar to see the output result.