Getting Started With SQL Server Reporting Services (SSRS) - Part 4

Overview

 

This is the fourth article of the series Getting Started With SQL Server Reporting Services (SSRS). In previous three articles we learned the following:

• How to create Simple Report using SQL Server Reporting Services?
• How to show data in Table Control from database?
• How to create parameterized reports?
• How to set visibility for report controls?
• How to show chart in reports?

For those who have not read the previous articles they can read it from the following links:

• Getting Started With SQL Server Reporting Services (SSRS) - Part 1 
• Getting Started With SQL Server Reporting Services (SSRS) - Part 2 
• Getting Started With SQL Server Reporting Services (SSRS) - Part 3  

You can also read this article on my Personal Blog.

 

Now, sometimes we need to display data of multiple report into one single report. To achieve this instead of creating complete new report, all datasets, etc we can use SubReport control of SSRS to add multiple sub reports in a main single report. In this article we will learn how to use SubReport control in our report to reduce our designing and development work. I hope if you are reading all the articles of this series, then you are almost used to my Report Project 1 Solution.

 

So, let's start with our previous Report Server Project 1 Solution.

 

Step 1

 

Open Report Server Project Solution. We will add a new report to it, so just right click on the Reports folder in Solution Explorer. Select Add and then click New Item. I have created New Report that is Report4.rdl

 

 

 

Step 2

 

Firstly, add Table Control from Toolbox in report by double clicking on it or simple drag and drop. This will open Dataset Properties Window for Table Control. Enter Dataset Name TestData. Select Use a dataset embedded in my report. Select Data Source DataSource1. In query type select Text and enter your report query in Query TextBox. We can use this Dataset to set visibility of Table control. Click OK Button.

 

 

Step 3

 

Now we are ready with our Tablix Control. We will insert subreport in Table row.

 

 

 

Now drag and drop SubReport control from the Toolbox into Tablix Control row as in the following image. This will insert one subreport control in your Tablix row.

 

 

 

Step 4

 

To set SubReport properties right click on SubReport control in Tablix Row and select Subreport Properties. This will open new Subreport Properties window.

 

 

 

Under General Tab, Enter Name for your sub report. For Use this report as a subreport, select report from the available drop down list which you want to display as a sub report. Here I am selecting Report2. Click OK button.

 

 

 

Step 5

 

We will add one more subreport in our main report. For that we will add new row in our Tablix Control. Right click on the left corner of the first tablix row. Click on Insert Row and then select Below. This will add new row below first row in our tablix control.

 

 

 

Now again drag and drop sub report control from the toolbox into newly added row. Right click on newly added subreport control and go to Subreport Properties.

 

 

 

Step 6

 

In Subreport Properties window enter your Subreport Name and select report from the available drop down list as we did to add first subreport. Click OK button.

 

 

 

Step 7

 

Now, we have completed with the designing part of Main Report and SubReports. Report2 and Report3 which we have added as a subreport requires two user input parameters, first one FromRegistrationDate and second one ToRegistrationDate. Firstly, we will add this parameter in our main report. In Report Data Viewer right click on Parameters folder and select Add Parameter.

 

 

 

Step 8

 

This will open Report Parameter Properties window. In General Tab, Enter Name as FromRegistrationDate, prompt as from Registration Date and select Data Type Date/Time and click OK button.

 

 

 

To add second required parameter again right click on Parameters folder and select Add Parameter. Enter Name ToRegistrationDate, prompt To Registration Date and select Data Type as Date/Time. Click Ok button.

 

 

Step 9

 

We have added these two parameters in our main report. Now we will pass these parameter values to our subreports. For that right  lick on subreport and select Subreport Properties.

 

 

 

This will open Subreport Properties window. Select Parameters Tab and click Add button to pass main report parameters to subreport.

 

 

 

Step 10

 

In Name Column enter the parameter name like FromRegistrationDate and in Value column enter [@FromRegistrationDate]. Do the same for second parameter and click OK Button.

 

 

 

Step 11

 

So, we are ready with our report designing part. To check preview of report click Preview Tab.

 

 

Step 12

 

Enter values for from Registration Date and to Registration Date and Click on View Report Button.

 

 

 

In this article we learned how to use SubReport Control in SSRS. I hope you enjoyed reading this article. We will learn more about SSRS in my next article. So keep reading and don't forget to post your valuable feedback.