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


Overview

Hi Friends! This is fourth article of the series Getting Started With SQL Server Reporting Services (SSRS). In previous three articles we learned 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 previous articles they can read it from these 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

Now, Some times we need to display data of multiple report into one single report. To achieve this instead of creating complete new report, all datasets 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 articles of this series then you are almost used to with 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 on New Item. I have created New Report that is Report4.rdl

Step 2

First 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 Tablix control. Click on Ok Button.

Step 3

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

Now Drag and Drop SubReport Control from Toolbox into Table Control row as shown in below image. This will insert one subreport control in your Tablix row.

Step 4

To set SubReport properties Right Click on SubReport control in Table 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 Report 2. Click on 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 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 toolbax into newly added row. Right click on newly added subreport control and goto 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 on OK button.

Step 7

Now, we have completed with designing part of Main Report and SubReports. Report2 and Report3 which we have added as a subreport requires two user input parameters first one is FromRegistrationDate and second one ToRegistrationDate. First 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 on Ok button.

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

Step 9

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

This will open Subreport Properties Window. Select Parameters Tab and Click on Add button to pass main report parameters to subreport.

Step 10

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

Step 11

So, We are ready with our report designing part. To check preview of report Click on 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.

Leave a comment