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:

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:

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:

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.

   

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

   

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.

   

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.

   

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')
   

   

5. Click Finish. The dataset will be created with the name ProductCatalog.

   

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.

   

   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.

1. Change the name and add Product Subcategory as a Group by Click OK to update the group.

   

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.

   

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

Add a details table

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

   

2. Right-click the new row in the master table and select Insert > Table.

   

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

   

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

   

Preview the report

We can now preview the report by toggling from Design to Preview in the top panel. 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.

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

Publish the report

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

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.

   

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!