Creating a Matrix Report in SQL Server 2008 Reporting Services

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:

clip_image002

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.

clip_image004

About these ads

4 Responses to “Creating a Matrix Report in SQL Server 2008 Reporting Services”

  1. SQL Server 2008 Key Says:

    I feel this is among the so much vital information for me. And i’m glad studying your article. However should remark on some normal issues, The web site style is ideal, the articles is really nice : D. Good task, cheers

  2. Robin Says:

    Fantastic web site! Thank you for your time to create and maintain it!

  3. Weezy Says:

    I code while you sleep?! Love it.

  4. Raghu Says:

    really nice article…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: