What is a Sub-Report?
I would like to
introduce a Sub-report as a simple report item which will display in inside the
body of a main report. It will use as embedded
Item within a report and any report can be used as a Sub-report. A sub report
mostly takes the parameter from main report. We can place a Sub-report in a data
region, the Sub-report will repeat with each instance of the group or row in the
data region. We can add more than one sub report into the main report.
We have already
created a parameterised “Employees Details Report” and you
can also create that report by clicking here. We will use that report as the
Sub-report in this article.
Before creating
a main report with sub report in Reporting Services, we need to know about the
output of the SSRS report. We are assuming that we have Employee Master Data
table in our database which contains the following information –
1) Emp Id
2) Name
3) Age
4) Salary
5) Department
EmpId
|
EmpName
|
EmpSalary
|
EmpAge
|
Department
|
105
|
Eliza Roy
|
$ 15,965.00
|
25
|
Admin
|
112
|
Roy Chauhan
|
$ 15,965.00
|
25
|
Admin
|
108
|
Rahul Kuamr
|
$ 15,965.00
|
22
|
Finance
|
101
|
Anil Sharma
|
$ 15,965.00
|
22
|
Finance
|
102
|
Bill Smith
|
$ 18,540.00
|
25
|
HR
|
109
|
Gary White
|
$ 18,540.00
|
25
|
HR
|
100
|
Amit Gupta
|
$ 15,450.00
|
32
|
IT
|
107
|
Sumit Singh
|
$ 15,000.00
|
32
|
IT
|
113
|
Lucky God
|
$ 15,965.00
|
23
|
Others
|
106
|
Flash God
|
$ 15,965.00
|
23
|
Others
|
104
|
David King
|
$ 18,025.00
|
22
|
Procurement
|
111
|
King Street
|
$ 18,025.00
|
22
|
Procurement
|
103
|
Chris Gray
|
$ 18,025.00
|
28
|
Sales & Marketing
|
110
|
Rosy Wang
|
$ 18,025.00
|
28
|
Sales & Marketing
|
We
need to display this information in our SSRS report and Report header should be
like as “Department Wise Employees Summary”. In this report, we will
show the department name and number of employees in the each department. Every
department will show the employees details in the data region which will be called
the Subreport.
Subreport
Screen:
Main report Screen without Subreport:
Subreport within Main Report.
We need to follow the following steps to implement our first Subreport into main SSRS Report:
1) On the start window, All Programs list chose SQL Server Data tools from Microsoft SQL Server 2012.
2) SQL Server Data Tools will open as –
3) Now, Click File, Add, New Project (ctrl+shift+N) from the top-level menu –
4) Chose a Report Server project to the solution. Fill in the Add New Project dialog as shown below:
At this point, we have created a Report Server project called “Reports” as shown above.
5) Click OK button on the New Project window and will Launch the report wizard-
6) In the Solution Explorer, Right Click on the Shared Data Sources and click on Add New Data Source.
7) Then Shared Data Source Properties window will open. Change the name as DataSource and click on the Edit Button.
8) Click the Edit button to display the Connection Properties dialog; fill in your Server name and select the respective database which contains the data for the report as shown below:
9) Click on the Test Connect button to verify the connection is working or not for the report as shown below –
10) After connection verification, click OK Button.
11) Window will return to Shared Data Source Properties window as shown below-
12) Click OK button to finish the Shared Data Sources wizard, now we have the DataSource to get the information from the database as shown below:
13) Right click on the reports folder, choose Add and click on New Item as shown below:
14) This will open Add New Item – Reports, choose Report and named as Department Wise Employee Summary as shown below:
15) After click on Add button, SSDT will create Department Wise Employee Summary report. We need to add report header to the report as shown below :
16) Here is now; we need to open Report Data. In the Report Data, Right click on the Datasets and click on the Add dataset as shown below:
17) Add Dataset will launch Dataset properties window. We need to set the name as DeptEmployeeSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:
SELECT
EmpId,
EmpName,
EmpSalary,
EmpAge,
Department
FROM
dbo.Employees
Order by
Department
|
18) After click on the OK button, a dataset has been added in the report data as shown below:
19) Now right click on the report and add table as shown below:
20) Add dataset to the table as shown below:
21) Now right click on the report details and Add Group to choose Parent Group to the repots as shown below:
22) It will launch the Tablix group, we need to select Department as Group by and checked Add group header as shown below:
23) Now we have the following data into the report as shown below:
24) Click Preview on the report as shown below-
Now, we need to remove details row as shown below:
Now, we need to remove extra columns from the report as shown below:
We need to add total number of the employees in the each department as shown below:
25) Now, we need to insert a new row inside the group as shown below:
26) After adding the blank row inside the group, we need to right click on the new blank row and choose merge cells as shown below:
27) We need to right click on the blank row and choose Subreport from the insert options as shown below:
28) Right click again on the added Subreport and click on Subreport Properties as shown below:
29) In the Subreport Properties window, choose the Subreport as shown below:
30) In Subreport Properties window, we need to set the Parameters to the Subreport as shown below:
31) Click on OK button and we have added the Subreport in the main report as shown below:
32) Click on the preview and view the report as shown below:
