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:
- Create a table “DimEmployee” with following employee details.
2. Create another table “EmpSalesAmount” having sales details for each employee.
3. Create a query to read the details from database.
;WITH cte AS (
EmployeeKey , EmployeeName , ParentEmployeeKey , 1 EmployeeLevel
FROM dbo.DimEmployee WHERE ParentEmployeeKey IS NULL
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
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 :
5. Right click on the row and select row group properties :
6. Configure the row group properties as per below:
8. Set Left Indent properties:
9. So we have done with our drill-down report. To see the preview, click on preview tab: