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

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

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

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

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.

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

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

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.

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.

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.

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

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.

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.

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.