How to build SSRS Drill Down Report using BIDS

A drill down report is very useful for tables containing layered data. The report will be displayed with the top layer at first, and those layer can be expanded later to show more detailed data.

This is the design of the drill down report:

I used AdventureWorksDW2012 as my source DB. You can search Google to get the free database. Following sql script is used to get the dataset:

SELECT  DimOrganization.OrganizationName,
    DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear,
    DimDate.EnglishMonthName, DimDate.MonthNumberOfYear,
    SUM(FactFinance.Amount) AS SubTotalAmount
FROM  FactFinance
    INNER JOIN DimDate ON FactFinance.DateKey = DimDate.DateKey
    INNER JOIN DimDepartmentGroup ON FactFinance.DepartmentGroupKey = DimDepartmentGroup.DepartmentGroupKey
    INNER JOIN DimOrganization ON FactFinance.OrganizationKey = DimOrganization.OrganizationKey
GROUP BY DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName,       DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear

You should add at least 2 columns with one numeric column of SubTotalAmount for the report when you create a tabular report following the report wizard.

Next, right click on the data part of the above report design area, and choose Add Group, and Parent Group.

A Tablix Group window will popup, choose CalendarYear. Note you need to check "Add group header" to show the Group Name.

After Calendar Year Group column has been added, you can choose Organization Name and SubTotal Amount Column and right click on them to add another Group for English Month Name. Once again you need to check "Add group header" to show the Group Name. This will add one row for the group header so which would be very useful to choose the group during the design phase.

Applying same way you can add Group Organization Name and Department Group Name. You need to check "Add group header" to show the Group Name every time.

Now you need to do hide/show trick. Go to Visual Studio with BIDS menu, choose REPORT, and click View, and check Grouping.

Following Row Groups Info will appear at the bottom of Visual Studio.

Right click on the Group you want to hide, English Month Name, Choose Group Properties. A Group Properties popup window will appear. Choose Visibility on the left pane, and choose Hide. Check "Display can be toggled by this report item:" , and choose Calendar Year.

You can do same for the following 2 groups.

Now the last step is to add Total. Right click on the  Calendar Year Group, choose Add Total, you can add total for all years. Right click on the English Month Name, choose Add Total, you can add Total for one year.

Note, Group would be ordered by name by default, so August would be the first month always. This is not what we want. On the Row Groups Info, choose English Month Name, right click on the Group Properties. Group Properties popup window will appear. Choose Sorting on the left pane. and Choose MonthNumberOfYear column. Then your Month Group will be in the right order.

The final design would be this:


This would be how the report result finally look like.