Posts Tagged ‘drill report’

SSRS – Matrix drill report

October 26, 2011 Leave a comment

Matrix drill report is one of the mainly used report model in any domain reports. The drill reports will gives the functionality of business analysis from higher granularity to lower granularity with aggregations.
The drill report also gives the functional comparison of different business cases at different granularity levels.
Here presenting simple steps and formatting options to develop matrix drill report using Report Builder 3.0
Step 1: Report Source query i.e. this query is in the de-normalization form to the report.

USE [AdventureWorksDW2008R2]
SELECT  PC.EnglishProductCategoryName AS [ProductCategory],
        PSC.EnglishProductSubcategoryName AS [ProductSubcategory],
        P.EnglishProductName AS [Product],
        D.CalendarYear AS [ProductOrderYear],
        D.CalendarQuarter AS [ProductOrderQuarter],
FROM    dbo.FactInternetSales AS FIS
        JOIN dbo.DimProduct AS P ON FIS.ProductKey = P.ProductKey
        JOIN dbo.DimProductSubcategory AS PSC ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
        JOIN dbo.DimProductCategory AS PC ON PSC.ProductCategoryKey = PC.ProductCategoryKey
        JOIN DBO.DimDate AS D ON D.DateKey = FIS.OrderDateKey
ORDER BY PC.EnglishProductCategoryName,

Step 2: Select Insert matrix as we are not considering wizard to develop report manually

Step 3: Selected the existing shared data source to “AdventureWorksDW2008R2” and creating the Dataset using the above source query

Step 4: Check the Fields from the query and name any alias names required with report requirements

Step 5: Drag the ProductCategory and ProductOrderYear into Rows Groups and Columns Groups respectively and setup the Textbox properties to fill the back ground colours



Step 6: Run the report to review the report as

Step 7: Add the child groups as i.e. same process applies to both rows and columns child groups as
Right click on Row Group (ProductCategory) -> Add Group -> Child Group -> select the value into child group -> ProductSubCategory

Step 8: Follow the same steps to create the Child group to ProductOrderYear and the view of the designer as

Step 9: To make the drill functionality to the report, setup the child group visibility and toggle properties. Hide the visibility on the run of the report and it needs to visible on selecting the toggled groups value as the higher group will get drill functionality to child groups.
Here formatted the background colours and length of the boxes for visibility of the report

Step 10: Setup the numbers format to the fact columns here sales amount to represent in currency for better useability of the report as

Step 11: Run the report to see the report view as

Step 12: Deploy the report to Report server and view the report at reports webserver

Points to consider
1. Primary goal is to present the logic of creating drill report and logical setting of visibility properties of groups
2. Formatting of the report and the colours are selecting on my personal view but in production level report developments will have lot more requirements

Categories: SSRS Tags: