Chapter-4 Creating SSRS Matrix Report / Cross Tab Report

Matrix report is similar to Tabular Report but designed using generating pivot table style reports. We have to choose Matrix instead of tabular while starting designing of SSRS Report.

Download Full Tutorial for Chapter-4-Creating-SSRS-Matrix-Report in PDF format

Download Matrix Report Project Sample

Step-1: Choose Report Wizard and give the name of report as SSRSMatric1.rdl

Select Report Wizard
Select Report Wizard

Step-2: In Data source Dialog Box , Select  NorthwindDataSource created in Chapter-1 is taken as shared data source click on next button.

Select NorthwindDataSource created in Chapter-1
Select NorthwindDataSource created in Chapter-1

Step-3: Paste the query given below as shown in the screen (the query to create pivot table) and click next.




select c.ContactName,year(OrderDate) as ordyear ,count(1) as totorders
from orders o inner join Customers c on o.CustomerID=c.CustomerID
group by c.ContactName,year(OrderDate) order by c.ContactName

Report Wizard -Design the Query
Report Wizard -Design the Query

Step-4: Choose Matrix Report in the select report type dialog box and click next.

Report Wizard-Select the Report Type
Report Wizard-Select the Report Type

Step-5: Choose fields “orderyear” in column ,”ContactName” in rows and “totorders” in details box on selecting the respective fields and click on buttons column, rows and details and click next.

Report Wizard-Design the Matrix
Report Wizard-Design the Matrix

Step-6: Check the checkbox Preview Report and click finish button

Completing the Matrix Report Wizard
Completing the Matrix Report Wizard

Step-7: Your default Matrix Report is created with default style.

 

Matrix Report Preview
Matrix Report Preview


 



Step-8: We need a column for Totaling all the years 1996, 1997 and 1998 data in rightmost column as Total, for that, open the report in the design view and  right click on total year choose Insert column>Outside Group-Right , a column to rightmost position will be inserted.

Matrix Report Getting Total by Grouping
Matrix Report Getting Total by Grouping

Step-9: Type total in the header bar and choose column “totorders” below Total header as shown in the image given below , it will automatically put the sum formula to column, adjust the width accordingly and select the column and right align the content by clicking on right in the standard toolbar.

Matrix Roport Adding Grand Total
Matrix Report Adding Grand Total


 



Step-10: For Creating a serial Number as first column , right click on “Contactname” column and click on Insert Column>Inside Group-Left, a column will be inserted to left most position.

Creating a serial Number as first column
Creating a serial Number as first column

Step-11: Type S.No in the Header of newly inserted column and right click on the box below S.No and click on expression.

Right click on the box below S.No and click on expression
Right click on the box below S.No and click on expression

Step-12: in the expression window paste the following formula for generating the sequence number and click on ok button, expression will be fixed with the column, align the column to center by clicking to center in the standard toolbar, adjust the column width.

=RunningValue(Fields!ContactName.Value, CountDistinct, Nothing)

Past the above code in the expression Field
Past the above code in the expression Field


 



Step-13: Change fill color of header to dark green, select the header row and choose Background Color tool from standard toolbar and click on ok, similarly set the Foreground color(font color) to white. Type the header “Contact name” in the header column right to S.No.

Change fill color of header to dark green
Change fill color of header to dark green

Step-14: Change the Report Header from SSRSMatrix1 to “Year Wise No. of Order Placed”- for this double click on the header SSRSMatric1 and change the name as required.

Change the Report Header from SSRSMatrix1 to “Year Wise No. of Order Placed”
Change the Report Header from SSRSMatrix1 to “Year Wise No. of Order Placed”

Step-15: Create a last row to calculate the Grant total of all the values- for that insert a row as shown below by selecting the data row and right click and insert row option.

Create a last row to calculate the Grant total of all the values
Create a last row to calculate the Grant total of all the values

Step-16: Select the same column in the newly inserted row as shown in the image , SSRS will automatically put calculated expression in the fields.

image031

Step-17: The desired Matrix Report is created , the color and other visual properties can be changed as required in the designed view .

Preview of the Final Matrix Report
Preview of the Final Matrix Report

Previous Related Post:

Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net



Leave a Reply

Your email address will not be published. Required fields are marked *

*

3 × five =