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 with Bold Reports Report Designer Part 3: Subreports
Create Impressive Master-Detail Reports with Bold Reports Report Designer Part 3: Subreports

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.

    Employee Details dataset linked to table
    Assigning the Employee Details Dataset to the Master 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.

    Master table showing employee data grouped by name
    Employee Data Grouped by Name
  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.
    Assigning department and group info to main table 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.

    Master table with department and group details
    Master Table Showing Department and Group Details

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.

Creating detail report parameters (hidden).
Creating Detail Report Parameters with Hidden Visibility

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.

Designing a detail table and populating it with data
Detail Table with Data

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.

Report with detailed information about employees
Employee Details 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.
    Adding a subreport within the master table
    Embedding a Subreport in a Master Table Row

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

    Subreport option added in master table.
    Subreport Item Added to Master Table
  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.
    Add detail report with subreport property options
    Configuring the Detail Report Using Reporting Options in the Properties Panel

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

    Detail report connected to master report
    Detail Report Linked with Master Report
  3. We need to assign parameter values to the subreport from the main report. Click Set Parameters in the subreport properties.

    Set subreport parameters in master report
    Defining Parameters for the Subreport Within the Master Report
  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.

    Detail report parameters added to master report
    Adding Detail Report Parameters to master Report
  5. Publish the report by clicking Publish at the top right corner of the designer.

    Publishing the master report
    Publishing the Master Report

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.

Master-detail report with subreport
Master-Detail Report with Details Presented as Subreport

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.

Tags:

Share this blog

Leave a Reply

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