Create Impressive Master-Detail Reports with Bold Reports Report Designer Part 3: Subreports

Welcome back to our blog series exploring master-detail reports. In our previous editions, we covered creating master-detail reports using tables and lists to provide high-level summaries alongside detailed information. While these methods excel at presenting well-structured data, they can be limiting when dealing with complex scenarios.

This is where subreports come in! They are the game-changer you need to enhance the depth of your master-detail reports effortlessly.

Understanding subreports

Imagine creating a report that summarizes your employees’ data. Traditionally, you might list everyone’s department and group information. But what if you want to delve deeper and see individual employee details within each department or group? Subreports offer the solution you need!

A subreport is a small report placed inside a bigger one. It helps you show more detailed information linked to specific parts of the main report. Imagine it as organizing books in a library: you open the main report (the library) and find a smaller report (a specific book) neatly stored inside, offering even more detailed information.

Benefits of subreports

Break down for clarity: Subreports allow you to break down complex reports into smaller, more manageable sections. Each subreport can focus on a specific aspect of the data, making it easier to design, maintain, and update.

Reuse and save: Designing a subreport for reuse across multiple reports saves time and effort, particularly when dealing with similar data structures or reporting needs in various applications.

Handle big data: Subreports handle big data better by breaking it into smaller pieces. This makes it easier for users to understand and speeds up report performance.

Master-detail reports with subreports in Bold Reports Designer

Now, let’s dive in and explore how to create a master-detail report with subreports in Bold Reports Report Designer. We’ll proceed under the assumption that you’re already familiar with the basic functionalities of the Report Designer.

Create a master report

To create a master report, we need to create a data source and dataset first. For this demonstration, we are going to use the AdventureWorks database. Following the Bold Reports documentation, we create a data source and generate a dataset for the master report by using the following query:

Employee Details query

SELECT D.Name, D.GroupName FROM HumanResources.Department D

Create master table

1. Drag and drop a Tablix element from the item panel onto the report designer surface. Assign the previously created Employee Details dataset to the table.

   

2. Click the Details group and then select Set Groups in the Properties panel. In the Grouping dialog box, modify the name and include Name as a Group by value for grouping.

   

3. Add new rows to the master table by right-clicking inside it and selecting Insert Row > Inside Group – Below. Then assign the Department and Group Name fields to the new rows.

   

   Mapping Department and Group Names to the Master Table

   The master table is created with department and group names as shown in the following image.

   

**Create detail report

**

Next, let’s create a new detail report to display employee details with parameters. This will become our subreport later.

Create parameters: The main report passes data to the detail report through parameters. So, we need to create parameters for Department Name and Department Group Name. Follow this guide to create the parameters, and then set their visibility to Hidden.

Detail report data setup

To create the detail report, we need to create a data source and dataset for it, following the same procedure we used for the master report. After setting them up, create a dataset query to fetch employee details based on the Department Name and GroupName parameters. The Department Name and GroupName parameters should be added to the query with their parameter names prefixed with @, which I have included in the following query:

SELECT E.EmployeeID, E.Title,  E.ContactID, E.VacationHours, E.SickLeaveHours  
FROM HumanResources.Department D INNER JOIN HumanResources.EmployeeDepartmentHistory EDH ON D.DepartmentID = EDH.DepartmentID INNER JOIN HumanResources.Employee E ON EDH.EmployeeID = E.EmployeeID  
Where D.Name = @DepartmentName and D.Group Name = @DepartmentGroupName

Create detail table

Add a table to the report surface. Assign the data to the table from the previously created dataset.

Assign the employee details data to the table with fields for ID, title, contact ID, vacation hours, and sick leave hours, and then save the report.

Embed the subreport

Now we are going to embed the detail report inside the master report using the subreport report item.

1. Add another row to the master table, and then right-click the new row and select Insert \> Subreport.

   

   The subreport will be included in the master table, as shown in the following image:

   

2. Within the subreport’s Properties panel, click Browse under the Report options. Select the employee detail report that we designed previously, and then click Open.

   

   The selected report path is linked in the Report text box, and the subreport is added as shown in the following figure:

   

3. We need to assign parameter values to the subreport from the main report. Click Set Parameters in the subreport properties.

   

4. Select the DepartmentName and DepartmentGroupName parameter names from the dropdown menu and assign their corresponding field expression values. Click OK to save the parameters.

   

5. Publish the report by clicking Publish at the top right corner of the designer.

   

Preview the report

We can preview the report by switching from Design to Preview in the top panel. The master report lists the department and group names, and the subreport displays the detailed employee data.

Conclusion

This blog series has explored several ways to create effective master-detail reports. We began with tables, followed with lists, and now we’ve reached subreports. Subreports offer unmatched flexibility, enhancing the user experience with complex data presentations that are easily comprehensible. Next time you face data overload, consider utilizing subreports to build effective reports.

Ready to improve your reporting skills? Download our free master-detail report template with subreports and stay tuned for more advanced reporting techniques in our next post.