Creating SSRS Subreports Using Bold Reports Designer

A subreport item incorporates a report within the body of a main report. As the main report executes, it will invoke the subreport and display its content in the report.

You can use a subreport item to display multiple sets of data in a data region or to manage related reports in a single report. So, you can create many simple reports and easily embed them into a main report. For example, using subreports, we can design side-by-side reports, master-details reports, briefing books, etc.

In this blog post, I’m going to use Bold Reports Designer to design a report that displays two subreports side by side. Each subreport will display sales achieved by a selected employee, providing an easier way to compare employee performance.

To complete this design, we need to create a main report and a child report. The main report will show two subreport items and the child report will show employee information. At the end, I’ll  link the child report to the subreport items in the main report. Let’s start designing.

Creating a main report

In the main report, add two subreport items to display information for different employees and create two parameters to pass employee names to the subreports. Once you link the child report to the main report, the respective employee information will be displayed in the subreport items.

First, launch the Bold Reports On-Premise server demo site, refer to the steps to create a report, and then create a new report.

Create data set

Add a data set to fetch employee names from the database, as it needs to create parameters carrying employee names to the child report. The following query runs against the Northwind database. Refer to the query builder overview blog post and data set creation documentation, then create a data set in the report.

SELECT
Employees.EmployeeID,
Employees.FirstName
FROM Employees

The above query will retrieve the EmployeeID and FirstName data from the database.

Create parameters

Refer to the documentation on creating parameters to create a parameter. Then create two parameters named Employee-A and Employee-B. Specify the parameter name and prompt text as “Employee-A” and let the other options remain with their default values.

Refer to the documentation on assigning values and assign EmployeeID as the value field and FirstName as the label field for the parameter.

Save the parameter and similarly create another parameter named “Employee-B” in the report.

Add subreport items to the report

The subreport item is listed in the item panel under the Sub Reports category.

To display employee information side by side, drag and drop two subreport items and place them beside each other on the design surface. Adjust the width and height of the items and the dimensions of the design surface as required.

Adding report header

Add a header and remove the footer from the report design by referring to the documentation article “Show or Hide Header and Footer.” Insert a rectangle and a text box within the header area. Adjust the width and height as required.

Set the report title in the text box and customize the header appearance by using the text box, line, and rectangle properties in the properties panel.

Refer to the section on publishing reports to publish the report. You can download the report from here: https://boldreports.com/wp-content/uploads/2020/05/Main-Report.zip

Creating a child report

In the child report, we are going to display employee information like name, photo, birth date, joining date, gain, and order details. First, create another report in the Bold Reports On-Premise server demo site.

Create a data set

Now we need three data sets to fetch information from the database, like employee personal details, sales order details, and employee images. The following queries run against the Northwind database. Refer to the query builder overview blog post and the data set creation documentation, then create a data set in the report.

Use this query to fetch employee sales order details.

SELECT Employees.EmployeeID,Employees.LastName,Employees.FirstName ,(DATENAME(WEEKDAY,Employees.BirthDate)+', '+ DATENAME (DAY,Employees.BirthDate)+' '+DATENAME(MONTH,Employees.BirthDate)+' '+DATENAME(YEAR,Employees.BirthDate)) as BirthDate
,(DATENAME(WEEKDAY,Employees.HireDate)+', '+DATENAME(DAY,Employees.HireDate)+' '+DATENAME(MONTH,Employees.HireDate)+' '+DATENAME(YEAR,Employees.HireDate))as HireDate ,SUM(o.Quantity * o.UnitPrice)  As TotalGain FROM
Employees,[Order Details] as O,[Orders] as Ord  WHERE o.OrderID  = ord.OrderID and Employees.EmployeeID = ord.EmployeeID and Employees.EmployeeID = @SalesPersonID
group by  Employees.EmployeeID ,Employees.LastName,Employees.FirstName ,BirthDate,HireDate

Use the below query to fetch employee sales order details information.

SELECT Top(10) o.OrderID,Cus.CompanyName,SUM(OD.UnitPrice * od.Quantity ) As ExPrice FROM [Orders] as O, [Customers] as Cus,[Order Details] as OD where  (O.EmployeeID = @SalesPersonID) and (cus.CustomerID=o.CustomerID) and od.OrderID = o.OrderID group by o.OrderID,o.CustomerID,cus.CompanyName

To display an employee image, I used the Employees image details from the sample JSON data and transformed that JSON data into rows and columns using a SQL query. You can download that specific query and create a data set using it by going to https://boldreports.com/wp-content/uploads/2020/05/EmployeeImage-Query.zip

Now, the data set created in the report will be listed in the Data panel, as shown.

Because the data set query contains the query parameter @SalesPersonID, an equivalent report parameter will be automatically created in the report.

Design report

To display employee information, design a simple report using image, text box, and table report items.

Expressions used in the text boxes

1. To display employee DOB, use the =First(Fields!BirthDate.Value,”EmployeeBasicDetails”)expression in the text box report item.
2. To display employee DOJ, use the =First(Fields!HireDate.Value,” EmployeeBasicDetails “)expression in the text box report item.
3. To display employee GAIN,use the =Sum(Fields!TotalGain.Value,” EmployeeBasicDetails “) expression in the text box report item.
4. To display employee First Nameand Last Name, use the =First(Fields!FirstName.Value, ” EmployeeBasicDetails “)+” “+” “+First(Fields!LastName.Value, ” EmployeeBasicDetails “) expression in the text box report item.
5. To present the sales details of a respective employee, the SalesDetails data set is assigned to the table report item.
6. To display an employee image in the report, assigned the =First(Fields!Photo.Value, “EmployeeImage”)expression to the image report item value property.

To publish the report, refer to the documentation on publishing a report. I named this report “EmployeeBasicDetails.” You can download it from here: https://boldreports.com/wp-content/uploads/2020/05/Child-Report.zip

Link child report to main report

Edit the main report, which I already created from the demo site homepage. You have to set the child report path and parameters in the subreport items.

Set report path

Link the child report path (EmployeeBasicDetails) to the subreport item. Select the first subreport item in the design surface and open the properties panel.

1. Under Basic Settings, click the browse button in the Report field.

   

2. A dialog will open and list the folders available in the server.

   

3. Select the already designed child report (EmployeeBasicDetails) from the respective folder and click Open.

   

Now the child report path (EmployeeBasicDetails) is linked in the subreport item on the design surface.

Similarly, set the report path to the another subreport item by following the previous steps.

Set parameter values

Next, you need to assign parameter values to the child report from the main report. Select the first subreport item from the design surface and open the properties panel.

1. Under Basic Settings, click Set Parameters, and then click the ADD.

   

2. The parameters available in the child report will be listed in the Parameters Name drop-down. Choose the parameter name.
3. Assign the =Parameters!Employee-A.Value expression in the value field and click OK.

   

Similarly, set the parameter name and assign the =Parameters!Employee-B.Value expression in the Set Parameters option of another subreport item.

Report preview

You can preview the report using the built-in report viewer. Publish the report and switch to preview mode. Choose an employee name in the Employee-A and Employee-B parameters drop-down and click View Report. The report will display information about two employees side by side.

Conclusion

I hope this article provided basic but useful information about subreport items and how to create a report using them in Bold Reports Designer. To explore further, go through our sample reports and Bold Reports documentation. I have also linked the generated .rdl files for reference.

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 support question.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official Twitter, Facebook, LinkedIn, Pinterest, and Instagram pages for announcements about upcoming releases.