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.
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.
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)
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.
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.
Step-17: The desired Matrix Report is created , the color and other visual properties can be changed as required in the designed view .
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