SQL Server Reporting Services - Example for Subreport and Drill-down report

In this post,let us see simple example for subreport & drill-down report


What is Subreport in SSRS?

Subreport is nothing but child report created to display the data 's based on data displayed by parent report.

/*Sample Data*/

CREATE DATABASE TECH_ORG

USE TECH_ORG
GO

CREATE TABLE Employee(
EMPID INT,
EMP_Name VARCHAR(30),
Designation VARCHAR(30),
DEPTID INT,
DEPT_Name VARCHAR(30))

INSERT INTO Employee SELECT 1,'Sathya','Programmer',1,'Database'
INSERT INTO Employee SELECT 2,'Sathish','Senior Programmer',1,'Database'
INSERT INTO Employee SELECT 3,'Praveen','Programmer',2,'Java'
INSERT INTO Employee SELECT 4,'Arun','Senior Programmer',2,'Java'
INSERT INTO Employee SELECT 5,'David','Programmer',3,'Flex'
INSERT INTO Employee SELECT 6,'Mike','Senior Programmer',3,'Flex'
INSERT INTO Employee SELECT 7,'Sunny','Programmer',1,'Database'
INSERT INTO Employee SELECT 8,'Hanshika','Programmer',1,'Database'



CREATE TABLE Employee_PersonalDetails(
EMPID INT,
EMP_Name VARCHAR(30),
Designation VARCHAR(30),
Gender VARCHAR(10),
Age SMALLINT,
Location VARCHAR(30))

INSERT INTO Employee_PersonalDetails SELECT 1,'Sathya','Programmer','Male',23,'Chennai'
INSERT INTO Employee_PersonalDetails SELECT 2,'Sathish','Senior Programmer','Male',28,'Pune'
INSERT INTO Employee_PersonalDetails SELECT 3,'Praveen','Programmer','Male',26,'Delhi'
INSERT INTO Employee_PersonalDetails SELECT 4,'Arun','Senior Programmer','Male',28,'Pune'
INSERT INTO Employee_PersonalDetails SELECT 5,'David','Programmer','Male',30,'Delhi'
INSERT INTO Employee_PersonalDetails SELECT 6,'Mike','Senior Programmer','Male',29,'Banglore'
INSERT INTO Employee_PersonalDetails SELECT 7,'Sunny','Programmer','Female',22,'Punjab'
INSERT INTO Employee_PersonalDetails SELECT 8,'Hanshika','Programmer','Female',21,'Kolkata'

/*Sample Data*/

If you are new  for creating SSRS report & not familiar with creating datasources and datasets,

Kindly refer my previous post on SSRS -  http://sathyadb.blogspot.in/2012/08/sql-server-reporting-services-example.html.

Step 1: Create ParentReport - ParentReport.rdl,with dataset using below query as shown in below image:

/*ParentReport - Dataset - Query */
SELECT EMPID,
EMP_Name,
Designation,
DEPTID,
DEPT_Name FROM Employee WHERE Designation = 'Programmer'

/*ParentReport - Dataset - Query */

Step 2: Drag&drop Table from Toolbox into the body of ParentReport as shown in below image.

ParentReport will display all employees details with designation = 'Programmer' 

Step 3:   Create ChildReport - ChildReport.rdl,with dataset using below query as shown in below image:

/*ChildReport - Dataset - Query */

SELECT EMPID,
EMP_Name,
Designation,
Gender,
Age
Location FROM Employee_PersonalDetails WHERE Designation = @Designation

/*ChildReport - Dataset - Query */

Step 4: Drag&Drop Table from Toolbox into the body of ChildReport as shown in below image.

ChildReport will display all employees personal details with designation based on employee designation displayed from ParentReport.


Step 5:Drag&drop Subreport  from Toolbox into body of ParentReport as shown in below image

 

Step 6: Right-click on  <Subreport>  - > Subreport Properties... as shown in below image

Step 7:Under General tab ,assign ChildReport as Subreport as shown in below image:     

Step 8: Under Parameters tab,map the ChildReport input parameter with  value of the ParentReport dataset as shown in below image:                                                                                                             

 

Step 9:Click on  the Preview tab under ParentReport ,you will notice Parentreport & Childreport (Subreport) displayed  as shown in below image.    

                                                                    

Step 10 :For creating drill-down report on [Gender] column in ChildReport.Try as shown in below image under ChildReport. 

                                                                     

Step 11:

Click on [Gender] text box and then click (Details) - > Group Properties... as shown in below image

 Step 12: Grouping by values of [Gender] column

Step 13:Check on Hide option to hide values initially and then to display values while expanding drill-down report & to get drill-down report on [Gender1] text box ,try as shown in below image:

Note:

[Gender1] text box will hold gender details in the drill down report format, So if you dont want [Gender] column in the report,you can delete that column

Step 14:Click on Preview tab under ChildReport and then enter 'Programmer' for Designation and then click View Report

You can also see the drill-down report by clicking on Preview tab of ParentReport.

Look at the below images for Drill-down report:

See Also:

• My other posts on SSRS