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.