SSRS - Drilldown SSRS Report

Drill-down report describes an increasing granularity in the management of data with comfortable functions for navigating through the data where user is capable to jump to the next level of detail or the next report object on the same level, hide individual levels and switch between the detail and drill-down data information. 

Before create a drill-down SSRS report, we need to know about the output of the SSRS report. We are assuming that we have an Employee Master Data table in our database which contains the following information –

1) Emp Id

2) Name

3) Age

4) Salary

5) Department

Assumed Data in the table as shown below:

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. Whenever user clicks on the department then it will drill the report and shows the employees details in that department as shown below:

For the report, we need to add a report filter to display the data only for the selected employee department.

We need to follow the following steps to create our first Drill-down 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 Department column and as shown below:

 Report preview would be like as:

25) Now we need to add toggle functionality to the report. In the Row Groups, after right clicking, choose Group Properties as shown below:

26) In Group Properties, set the visibility of the details group as shown below-

We can do the same with properties of the Tablix Member as shown below:

27) Now, we are ready to preview of the report as shown below:

28) Click on the toggled department, it will show the following report. 

This completes the tutorial section of the simple drill down report. You can view this demo on Youtube - 

If you want to create the drill-through report then you can click here