Archive

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],
        FIS.SalesAmount
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,
        PSC.EnglishProductSubcategoryName,
        P.EnglishProductName,
        D.CalendarYear,
        D.CalendarQuarter


 
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: