SSRS -Multi-level drill down report?

You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drill-down action.

You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix.

In order to define a drill down action, a tablix must first be created with the appropriate level of aggregation all the way to the level of drill down detail desired. This detail includes both the row and columns desired for the lowest level viewing needed. The drill down functionality works either at the row level, the column level, or the group (column or row) level.

Let’s start step by step:

  1. Create a table “DimEmployee” with following employee details.
EmployeeKey ParentEmployeeKey SalesTerritoryKey EmployeeName
1 NULL 101 Rakesh
2 NULL 102 Amit
3 2 103 Sunil
4 2 104 Avay
5 3 105 Sachin
6 3 106 Gaurav
7 5 107 Rupesh

2. Create another table “EmpSalesAmount” having sales details for each employee.

EmployeeKey Amount
1 5000
2 5225
3 4879
4 6589
5 412
6 5987

3. Create a query to read the details from database.

 

;WITH cte AS (

SELECT

EmployeeKey ,  EmployeeName , ParentEmployeeKey , 1 EmployeeLevel

FROM dbo.DimEmployee WHERE ParentEmployeeKey IS NULL

UNION ALL

SELECT

c.EmployeeKey ,c. EmployeeName , c.ParentEmployeeKey , p.EmployeeLevel + 1

FROM dbo.DimEmployee c

JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey

)

SELECT cte.*, ISNULL(f.Amount, 0) SalesAmount

FROM cte

LEFT JOIN dbo.Empsalesamount f

ON f.EmployeeKey = cte.EmployeeKey  ORDER BY EmployeeLevel, EmployeeName

4. Create Table on Report and drag and drop EmpName field from the dataset :

Capture1

5. Right click on the row and select row group properties :

Capture2.JPG

6. Configure the row group properties as per below:

Capture3.JPG

Capture4Capture5

8. Set Left Indent properties:

Capture6.JPG

9.  So we have done with our drill-down report. To see the preview, click on preview tab:

Capture7Capture8

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s