Create Matrix Report in SSRS

Create Matrix Report in SSRS

1. First of all open Visual Studio 2008 then Go to File -> New Project to create new project.

Create Matrix Report in SSRS-1
2. A New Project Window will pop-up. Select Business Intelligence Projects and then select Report Server Project. Give Project Name and then click onΒ OK.

Create Matrix Report in SSRS-2
3. Now Right click on Reports and select Add -> New Item.

Create Matrix Report in SSRS-3
4. Now Select Report Wizard from Visual Studio Installed Templates and give Report Name.

Create Matrix Report in SSRS-4
5. Now click on Add. It will pop-up Report Wizard.

Create Matrix Report in SSRS-5
6. Then Click on Next Button. Now we are going to connect sql server. We can also connect to other data source like oracle, xml etc. Here we select Microsoft Sql Server.

Create Matrix Report in SSRS-6
7. Then click on Edit button. Now we configure details related to our database like database server name, credentials and target database. After that we can verify it with ‘Test Connection’ to check whether connection is done or not.

Create Matrix Report in SSRS-7
8. Then click on Next. It will show Query Builder Window.

Create Matrix Report in SSRS-8
9. In our case, Query is as below :

SELECT      PC.Name AS ProdCat, PS.Name AS SubCat, DATEPART(yy, SOH.OrderDate) AS OrderYear, 
            'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr, 
            SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales
FROM        Production.ProductSubcategory PS 
INNER JOIN  Sales.SalesOrderHeader SOH 
INNER JOIN  Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID 
INNER JOIN  Production.Product P ON SOD.ProductID = P.ProductID 
            ON PS.ProductSubcategoryID =  P.ProductSubcategoryID INNER JOIN
            Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE       (SOH.OrderDate BETWEEN '1/1/2002' AND '12/31/2009')
GROUP BY    DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 
            'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

Create Matrix Report in SSRS-9

You can also build your query with the help of Query Builder.

10. Then click on Next, here select Matrix type from Report Type.

Create Matrix Report in SSRS-10
11. Then Click on Next.
Here we add OrderYear & OrderQtr in Columns section, ProdCat & SubCat in Rows section
and Sales in Details Section.
Check the Enable Drilldown if you want the facility of drilldown.

Create Matrix Report in SSRS-11
12. Then click on Next. Select Matrix Style. Here we select Slate.

Create Matrix Report in SSRS-12
13. Then click on Next. Here you can find summary of our Report.

Create Matrix Report in SSRS-13
14. Then select Finish. Now your report design screen will look like below :

Create Matrix Report in SSRS-14
15. Click on Preview Tab to see the preview of Report.

Create Matrix Report in SSRS-15
Congratulations! Our Matrix Report is Completed.

16 thoughts on “Create Matrix Report in SSRS

  1. Nice job, makes Matrix easy to understand. I added a Matrix to one of my reports, but have one problem…I cannot get the header row with the column groups to repeat after a page break. In a tablix this can be easily done with Advanced mode (Set Static Row to RepeatOnNewPage True,KeepWithGroup After) However,this approach generates error messages on a Matrix.

  2. Hi,
    Good overview of Matrix object. Do you know if it is possible to make the column headers repeat on a new page like the Tablix object?

  3. Hi Bushan Thanks for share such a clean post it actually help full but i have one query like in Excel Pivot when we click on any number (GrandTotal colum) it gives the data for that number, can we add that functionality in SSRS report also

Leave a Reply to Bhushan Shah Cancel reply

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