Articles in this section
Category / Section

How to create master detail report using subreport in Report Designer?

Published:
Updated:

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

  1. As a first step, open the Bold Report Designer. 
    bold-reports-designer.b3c7ef1.d111060e70d3509cfdfbd2c8268d34b5.png

  2. To add parameters to the detail report, click Parameters. Then, click New parameter in the parameter panel.

  3. Now, the following wizard will be displayed. 
    parameter-wizard.31a36f0.9537b193b1c5a034b2c0b3855af8ce21.png

  4. Specify the parameter name as ProdSubCat, the prompt as ProdSubCat and set visibility to hidden.

  5. Then, click Save.

  6. Similarly, do the same to add the parameter ProdModel.

    sub-report-add-parameter.bd6740a.aebd6bc4296ae1a2652eb1fd4c1306b8.gif

  7. Connect to the data source using this documentation link.

  8. Connect to the dataset using this documentation link.

  9. The ProductCatalog dataset is created using the following query. 
    datasource-connection.2ddf5f8.bf0cee0975431fb64d7f2cae30eedc91.gif

      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
  10. Drag the table report item and configure the field as shown in the following image.
    initial-detail-report-design.775f9c8.50b7d5db809bfbe4a6714ca756f2a79a.png

You can download the created Detail report from here, Sample report.

Create a master report

  1. Open the Bold Report Designer. 
    bold-reports-designer.b3c7ef1.d111060e70d3509cfdfbd2c8268d34b5 (1).png

  2. Connect to the data source using this documentation link.

  3. Connect to the dataset using this documentation link.

  4. The ProductCatalog dataset is created using the following query.
    datasource-connection.2ddf5f8.bf0cee0975431fb64d7f2cae30eedc91 (1).gif

    ```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')
    ```
  5. Drag the table report item and configure the fields. Then, add groups in the tablix to display the data using this documentation link.

  6. The initial design of the report is displayed as shown in the following image.
    initial-master-report-design.b3c7ef1.98a05b7200a76429da17653a79ff1132.png

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.

  1. Right-click on the last row (Description) and select Insert Row, then click the Inside Group-Below option.

  2. In the newly added row, select all the cells using Ctrl Key + Mouse left click combination.

  3. After selection, right-click any selected cell, and then click Merge Cells.

  4. Right click on newly added row and select Insert, then click Subreport.
    add-sub-report-in-master.775f9c8.47a507379d64f1d54185a87c48a15b79.gif

  5. Select the sub-report item in the design surface and open the properties panel.

  6. Under the Basic Settings, copy the sub-report path and paste it in the Report field.
    link-sub-report-path.11f2004.0ab2143b5866064fb8fb3d5457472512.gif

  7. Then Click Set Parameters, followed by the Add button.

  8. Specify the Parameter Name to ProdSubCat and value to =Fields!ProdSubCat.Value.

  9. Similarly, do the same for parameter ProdModel and value as =Fields!ProdModel.Value.
    add-parameter-in-master.bd6740a.0925dd138b1cec75aa0f16a471322ccb.gif

  10. Click on the Preview at the top-right corner of the Report Designer toolbar to see the output result. 
    output-video.11f2004.fa360ac479f92614735b4840d63723aa.gif

Was this article useful?
Like
Dislike
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment