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
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report
Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS
Chapter-10 : Creating and Deploying Web Services for SSRS
Chapter-11 : Creating SSRS Report by consuming Web Service Data Source
Chapter-12 : Working with Data Bars in SSRS
Chapter-13-Displaying Color in Data Bar Based on Condition
Chapter-14- Displaying Data Labels along with Data Bars in SSRS
Chapter-15-Displaying Indicators in SSRS
Chapter-16-Creating SSRS Linked Report
Chapter-17 Adding or Creating Drop Down Parameter List in SSRS
Chapter-18 Creating Group above or stepped report in SSRS
Chapter-19-Creating Multi Value Parameter Report in SSRS
Chapter-20-Using CSS Stylesheet in SSRS