SubReport in SSRS

So far, we have placed report items within report items and data regions within data regions. Now in this article, we will put whole report inside another report. This is done using the sub report item. The sub report item is simply a placeholder in a report. Any report can be used as a sub report.

1. First of all open Visual Studio 2010 and open your SSRS project. Then add report named as DemoSubReport in your project.Now create Dataset. In dataset we used following query.

?

1 2 3

SELECT  DEPARTMENTID ,NAME      FROM    HUMANRESOURCES.DEPARTMENT WHERE   DEPARTMENTID = @DepartmentID

@DepartmentID is our parameter.

2. Then right click on report area and goto Insert–>Table.

3. Now select data fields as a column in the table. After that your Report Design will look like below.

4. Similarly add another report named as SubReport. In that report we used following query:

?

1 2 3 4 5 6

SELECT      E.BUSINESSENTITYID,FIRSTNAME + ' ' + LASTNAME AS NAME FROM        HUMANRESOURCES.EMPLOYEE E INNER JOIN  PERSON.PERSON P ON E.BUSINESSENTITYID = P.BUSINESSENTITYID INNER JOIN  HUMANRESOURCES.EMPLOYEEDEPARTMENTHISTORY EDH ON         E.BUSINESSENTITYID = EDH.BUSINESSENTITYID WHERE       EDH.DEPARTMENTID = @DepartnentID ORDER BY    FIRSTNAME

@DepartmentID is our parameter.

also add table as described in Step 2 & 3.

5. Now again goto our first report DemoSubReport. Then right click on report area and goto Insert –> SubReport.

6. Now your report design look like below:

7. Now right click on Sub Report Item and select Subreport Properties.

8. A Subreport Properties window pops up. In the Use this report as a subreport select SubReport which we generated earlier.

9. Then click on Parameters in left pane. Then Click on Add. A new row is added to the parameter grid.

In the Name text box, type the name of a parameter in the subreport. This name must match a report parameter in the subreport. If the subreport is in the same project, you can choose the parameter from the drop-down list.

Then click on OK button.

10. So we have done with Sub Report. To see the preview click on Preview tab. Give the value of DepartmentID and click on View Report Button.

Congratulations! We successfully completed SubReport in SSRS.

1.     Background

The aim of the article is to describe a way of creating SubReport using SQL Server Reporting services. Sub Report is very useful when we need to show the detail data with respect to summary data from any report. Sub Report is also very useful when we need to show some of the reports into a single report.

2.     What is Sub Report?

• A SubReport is a report item that displays another report inside the body of a main report.
• It is basically used to embed a report within a report.
• Any report can be used as a SubReport.
• We can pass the parameter into SubReport from main report.
• We can place a SubReport in the main body of the report, or in a data region.
• If you place a SubReport in a data region, the SubReport will repeat with each instance of the group or row in the data region.

3.     Scenario for creating Sub Report:

         I. Here we are going to create report and its sub report based on the state wise population and city wise population data of India:

• Main Report: It will show state wise aggregated (summary) Population details.
• Sub Report: It will show city wise population based on state as input parameter from Main Report.
• Sub Report: Chart Report that shows city wise Population based on state as input.

Below is the table data that we are going to use in all our reports:

SELECT        

     [State],

     City,

     [City Population],

     Male,

     Female,

     Sexratio,

     Literacy

FROM [Population]

         II. Below is the dataset used for the main Report:

It is a simple T-SQL SELECT statement

Below is the data output from dataset query:

           III.            Main report will looks like below one:

4.     Steps to create Sub Report in the form of Drill through

     I. Here we are going to create a sub report which shows city wise population details based on state detail passed from main Report. So we need a input parameter for State name in the sub report:

                     II.  Below is the dataset details which shows that we are showing we are using state as input parameter :

                   III.  Below is the sample report based on CityPopulation dataset:

                IV.  To add “City-wise” report as SubReport to main report (state-wise) , go to main report, right click on the Population text box and select Textbox properties:

              V. Go to Action then ‘Select to report’ option and specify the report shows city population. Add an input parameter for State:

Click Ok.

                   VI.  Preview the main report:

 VII. Once we click on the Population data on main report(e.g for Karnataka state), It will redirect to the SubReport that will show city wise details based on input state (Karnataka):

                     

Now we are able to redirect a report from a main report.

5.     Steps to create SubReport as Embedded Report within Main Report

            I. Here we will use another report(CityPopulationChart) as Sub report that is Chart report which shows top 5 most population area. 

                  

             II. For embedding a report in a main report, Right clcik on the report layout of Main Report and select SubReport:

           III.  It will add the data region for Sub report in the main report:

           IV.  Right click on the Sub Report region and select SubReport Properties:

             V.  Select the report that need to embed into the main report:

           VI.  Now main report layout looks like below one:

         VII. Preview the report

Now we can see the chart report in the main report itself

6.     Conclusion

By using the above steps, we can add SubReport to main report.

----------------------------------------------------End of Document---------------------------------------------------

Subreports in SSRS 2014

2015-08-04 by suresh

SSRS Subreports

In SQL Server Reporting Services, Subreports allows us to add one or more related reports to the Data region. Following are some of the advantages of subreports:

• Allows us to add Multiple related reports in one Report.
• Allows us to Nest or Insert One report inside the other.
• Allows us to pass parameters (Parameter values) from main report to subreport
• If you place the subreport inside the table or matrix then subreport will be repeated for each row otherwise, it will be displayed once.

Subreports in SSRS Example

In this example we will show you, How to create Subreports inside the Table Reports with example. For this, we are going to use the below shown report.  Please refer the SSRS Table Reports article to understand the Data Source and Dataset we used in this report.

Below screenshot shows you the second report and this is the report we are going to display inside the main report (subreport). Please refer SSRS Data Bars article to understand the Data Source and Dataset we used in this report and we done some formatting as well.

If you observe closely, It’s just a normal report with Basic Parameter and its default value is Australia.

First, We are going to hide the Parameter because we are going to pass the parameter value from main report to this subreport.To hide the parameter, Right click on the Parameter and select the Report Properties option. In the general tab, Please change the parameter visibility option from Visible to Hidden as shown below

Before we nest the Subreports, we need an Empty Row inside the table so select the Details Row from Tablix and right-click on it to open the context. From the context menu, please select the Insert Row option and select Inside Group – Below as shown below

Once you select Inside Group – Below option, a new Row will be added to the table

From the above screenshot you can observe that, newly created row is splited into two cells but we need single cell only for adding subreports. For this, Please select the entire row and right-click on it and select the Merge Cells option as shown below

Now you can notice that, we have one long cell to hold the subreport. Lets add the subreport to this cell

We can add subreport by two way:

First, We can drag and drop the Subreport from Toolbox to empty cell as shown below

Second, Right click on the empty cell and select the Insert option and then select Subreportoption as shown below

Once you selected the Subreport option, an empty subreport is added to the table.

Now, we have to add our report to this subreport region so, Right click on the Subreport and select the Subreport Properties.. option from the context menu

Once you selected the Subreport Properties.. option, a new window will be opened to select the report. For now, we are selecting Polished Data bar report as shown below

After selecting the required report, We have to assign the parameters value(s) of a subreport (If any). To add the parameter, go to Parameters tab and click on Add button.

Under the name section, Please select the Parameter name of the sub report

Next, We have to assign proper value to  the selected Parameter. Here, ParaCountry Parameter is expecting country names so we selected the [Sales Territory Country] column from the main report.

NOTE: If you don’t find the exact column in the main report, Use the fx button to create one.

Click Ok to finish creating Subreports in SSRS Table Report

Let us see the report preview by clicking preview tab.

As you can observe from the above screenshot, Subreport is perfectly nested inside the main report. Please don’t mind with color combinations.

Thank You for Visiting Our Blog