Master-Detail Reports Using Lists

Create Impressive Master-Detail Reports in Bold Reports Report Designer Part 2: Lists

Welcome to the second part of our blog series on creating master-detail reports in Bold Reports Report Designer. In Part 1, we covered setting up a master-detail report using tables. In this blog, we’ll explore how to design a master-detail report using a free-form layout with the list report item in Bold Reports.

While tables offer a structured presentation, lists provide flexibility to arrange data in a more fluid and expressive manner. We’ll show you how to unleash your creativity by organizing master data in a way that not only conveys information effectively, but also captivates your audience with a visually engaging presentation.

What is a master-detail list?

Think about creating a report that shows a list of products, like items you’d find in a cycling store. The first part of the report shows different product categories, like caps and helmets. This is the master part. The second part lists the specifics of each item in the categories. This is the detail part.

Presenting information in this format is effective for organizing items that have numerous details.

Mater detail sample report

Creating a master-detail list in Bold Reports

Let’s see how to create a master-detail report like this using lists in Bold Reports. Let’s assume that 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

To create this report, we are going to use the AdventureWorks database. Here are the steps to connect to the database:

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

Datasource Configuration panel

2.Select the type of data source. We are going to connect to Microsoft SQL Server for this report.

Datasource Connection

3.Provide the database information to establish a connection, and then save the data source. We are using the AdventureWorks database and Windows authentication type.

Datasource Credential

After creating the data source, the next step is to create the dataset.

Create the dataset

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

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

Dataset Connection

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 turning on the Code option in the toolbar. This allows us to enter SQL queries.

5.Add the following query to retrieve all the product details from the database:

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

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

We’ve finished the initial data preparation for the report. Next, let’s design the structure of the master-detail list.

Add a master list

1. Drag and drop a List report item from them item panel onto the report layout. We will use this to display the master data.
2. Assign the already created ProductCatalog dataset to the master list.

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

3.Select the list. This will open the Row Groups section at the bottom of the designer. Click the and then click Set Groups in the Properties panel, which opens the Grouping dialog for setting group information.

4.Add a Group by value for Product Subcategory to group the values based on the product subcategory data in the list. Click OK to update the group.

5.Right-click on the list and select Insert > TextBox.

6.In the added text box, enter the label text Product Subcategory:.

7.In the same way, add another text box. Right-click on this new text box and select Expression to open the Expression dialog.

8.In the Expression dialog, select the Fields (Product Catalog) dataset in the Data Then, double-click Product Subcategory in the list below it. The expression will be added in the text box at the top of the Expression dialog. Click OK.

9.A text box with an expression for Product Subcategory has been added to the report.

10.Follow the same process to add text boxes and expressions for Model and Description. The resulting master list is shown in the following image:

Next, we need to create a details table inside the master list.

Implementing a details table

1. Right-click on the list, select Insert, and then choose Table.

2.The details table will be added in the master list.

3.Add fields for product number, name, size, weight, and cost to the table.

4.We have finished creating the master-detail report with a list. The report is ready for preview.

Preview the report

Preview the report by toggling from Design to Preview in the top panel. This lets us view the master-detail report with product details like product number, name, and cost grouped by subcategory, model, and description in the report.

Publish Report

Let’s publish our master-details report. We can publish it publicly or privately.

1. Click the Publish.

2.In the Publish As Report window, fill in the fields for 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

Using lists is a great way to make master-detail reports clear, organized, and visually appealing.

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

If you have any questions, please post them in the comments section. 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. Happy reporting!