Chat Icon
Webinar A Deep Dive into Report Parts for Accelerated and Reusable Reporting - Thursday, February 29, 10:00 A.M. ET SIGN UP NOW
Created with Sketch.
Login-icon
Create Impressive Master-Detail Reports in Bold Reports Report Designer Part 1: Table
Master-Detail Reports with Tables

Create Impressive Master-Detail Reports in Bold Reports Report Designer Part 1: Table

Master-detail reports are the visual storytellers of the data world. They seamlessly connect high-level summaries with in-depth details, providing a complete view to help users make informed decisions. Crafting beautiful and insightful master-detail reports can be a challenge, but fear not! Bold Reports is here to help you create impressive master-detail reports using tables through its user-friendly Report Designer.

Before you start creating a master-detail report, let’s see how data looks without the master-detail concept. The following sample report displays data as it is retrieved from a query:

Sample report
Sample report

As you can see, some of the data is repeated in the table. Would grouping fix this problem? Let’s try it and find out. Refer to the following figure:

Sample report with grouping
Sample report with grouping

Even though we can see the data better in this table, the grouped data and the detailed data are shown in the same table, which still makes the report slightly difficult to read.

Now let’s see how the master-detail concept makes a report much easier to comprehend. A master-detail report uses two tables: one for the main information (the master table) and another for the details (detail table). A complete master-detail report is shown in the following figure:

Master Detail table
Master Detail table

We will explain how to create this master-detail report using the Bold Reports Report Designer. Let’s assume you are already using the Report Designer from the Report Server or the standalone Report Designer and have created a new report.

Create the data source

We are going to use the AdventureWorks database to create this report. Here are the steps to connect with the database:

  1. Click Data in the configuration panel, select the Datasource tab, and then click New Datasource.

    Data source Configuration Panel
    Data source Configuration Panel
  2. Select the type of data source. We’re going to connect to Microsoft SQL Server.

    Data Source Connection
    Data Source Connection
  3. Provide the database information to connect with database and then save the data source. We are using the AdventureWorks database and Windows authentication type.

    Update Data Base Credential
    Update Data Base Credential

After creating the data source, we need to create the dataset.

Create the dataset

Once the data source is created, we need to create the dataset to be able to retrieve the necessary data from the database and then add it in the master-detail table.

  1. Click Data in the configuration panel, switch to the Dataset tab, and click New Data.
  2. Choose the Existing option and then select the data source we just created.

    Dataset Configuration Panel
    Dataset Configuration Panel
  3. Open the Dataset Designer (also known as the Query Designer) by clicking the Connect DataSource.
  4. Switch to code mode in the Dataset Designer by enabling the Code option at the top of the editor and insert 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')

    Dataset Query Designer
    Dataset Query Designer
  1. Click Finish. The dataset will be created with the name ProductCatalog.

    Dataset Created
    Dataset Created

We’ve finished the initial data preparation for the report. We can now move on to designing the master-detail table structure.

Add a master table

  1. Drag and drop a Tablix element from the item panel to the report designer surface. Remove the unwanted columns and rows from the table and then assign the ProductCatalog dataset to it.
    Master Table Dataset
    Master Table Dataset

    The table that we are creating will act as master data, so do not use fields that will be used in the detail table.

  2. Select the master table. This will open the Row Groups section at the bottom of the designer. Click the (Details) group and then click Set Groups in the Properties panel.
Set Groups
Set Groups
  1. Change the name and add Product Subcategory as a Group by Click OK to update the group.

    Update Grouped by Value
    Update Grouped by Value
  2. Right-click the master table and select Insert Row > Inside Group – Below. Next, assign the Product Model data to the new row. Add another row and assign the Description data in a similar manner.

    Row Group
    Row Group

The master table is created as shown in the following image.

Master Table
Master Table

Add a details table

  1. Right-click the Description row in the master table and select Insert Row > Inside Group – Below.

    New Row Update Inside Group Below
    New Row Update Inside Group Below
  2. Right-click the new row in the master table and select Insert > Table.

    Details Table Insertion
    Details Table Insertion
  3. The details table will be added to the bottom of the master table, as shown in the following figure:

    Details Table Added
    Details Table Added
  4. Assign the product details data to the table, with fields for Number, Name, Size, Weight, and Cost.

    Details Table
    Details Table

Preview the report

We can now preview the report by This allows us to see the master-detail report with product details such as name, number, size, weight, cost, grouped by category, and grouped by subcategory.

Preview Report
Preview Report

We can export the report in PDF, Excel, Word, HTML, PowerPoint, XML, and CSV formats by using the Export option in the toolbar.

Export options
Export options

Publish the report

Now we are going to publish our master-detail report. We can do this publicly or privately.

Publish Report
Publish Report
  1. Click the Publish The Publish As Report window will open.
  2. Fill in the fields Category, Name, and Description. Decide whether you want to make the report public or private by using the Mark As Public
  3. Click Publish to publish the report.

    Report Publish
    Report Publish

Conclusion

Bold Reports Report Designer makes it incredibly easy to craft vibrant master-detail reports. By following the steps in this article, you can present your data in an engaging and insightful way. Leverage our user-friendly features and start crafting reports that make a lasting impression!

To learn more, check out our sample reports and documentation. You can access a demonstration report RDL file here for your use.

If you have any questions, please post them in the comments section below. You can also contact us through our contact page, or if you already have an account, you can log in to submit your question.

Bold Reports offers a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports. Be sure to let us know what you think!

For announcements about new releases, follow us on our X, Facebook, and LinkedIn pages.

Keep an eye out for Part 2, where we will check out a different way to display the details in your master-detail reports!

Tags:

Share this blog

Leave a Reply

Your email address will not be published. Required fields are marked *