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,
Step 1: Create ParentReport - ParentReport.rdl,with dataset using below query as shown in below image: