Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.
Matrix Reports are a special report similar to a pivot table. Matrix reports are not uncommon, and are useful for measuring trends. In this lab we’ll walk through the basic steps of creating a Matrix report.
Step 1. Add the new report.
1.1 Right Click on the Reports branch of Solution Explorer.
1.2 Select Add New Report, then click Next to go past the welcome screen.
1.3 Select your shared data source, or create a new one for this report. When you have done so, click the Next button.
Next you will need to enter the query to supply data to the report. I generally recommend using a tool like SQL Server Management Studio refined your query. For this lab enter the following query:SELECT [FiscalYear] , [ProductCategoryName] , [ProductSubcategory] , [Product] , [TotalAmount] FROM [ContosoRetailDW].[Report].[V_ProductTotalsByYear]
Now the Report Wizard will ask what type of report we want. Since we are creating a matrix report select the Matrix option and click Next to continue.
It is now time to design the layout of our matrix report. Click on FiscalYear, then click on the Columns button to move it into the column area. Next click on ProductCategoryName and click the Rows button. Repeat with ProductSubcategory and Product. Finally, click TotalAmount and put it in the details area by clicking the Details button. Your screen should now look like:
Click Next once your screen is complete.
Now the wizard offers to format the report for you. To do so yourself later, picking generic will leave you with plain black and white, no coloring. For this example, let’s pick Corporate and click Next.
Finally we need to give this report a good name. Let’s enter “Product Total By Year Matrix” and click Finish.
Step 2. Preview your work.
The report will now be generated and brought into your editor. Click the Preview tab to see the result of your work. A sample of the report is shown below.