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.
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 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 11:
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:
You can also see the drill-down report by clicking on Preview tab of ParentReport.
Look at the below images for 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.
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 ReportYou can also see the drill-down report by clicking on Preview tab of ParentReport.
Look at the below images for Drill-down report:
No comments:
Post a Comment