Mar 30, 2016

SSRS Matrix Reports Crosstab

Tablix is a new feature introduced in SSRS 2008.
Tablix combines the features of a tabular report and cross tab features.
A tablix report, display the report data in rows and columns, and allows us to organize the data in  aggregated groups.
It also allows us to add drill down features to get into details of a report.
Tablix = Table + Matrix.
So essentially it allows to add pivot like features to our SSRS reports.

Lets take an example.

Step 1. Create a blank report
Step 2. Add a connection to the Adventure works database
Step 3. Create a dataset with the following query:

SELECT  Sales.SalesTerritory.Name as Region,
        Sales.SalesTerritory.CountryRegionCode,
        Sales.SalesTerritory.[Group] as Territory,
        Year(Sales.SalesOrderHeader.DueDate) as Year,
        Month(Sales.SalesOrderHeader.DueDate) as Month,
        Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID 



Step 4:  Drag and drop the Matrix control from the ToolBox.



Step 5: Drag and drop the territory to the rows and Year to the column:


Step 6: ON clicking Preview, you should be able to get something like this:


Step 7: However the report, does not have any aggregated data.
So lets drag and drop the TotalDue field to the 'Data' area in the report.


Step 8: On clicking preview, you should be able to see the aggregated report as below:

Go ahead and add some currency formatting to the data.

Lets look at groupings and drilldowns in the next tutorial.
http://ssrstutorials.blogspot.com/2012/10/lesson-15-ssrs-matrix-reports.html

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib