Matrix Report

Matrix Reports are a special report similar to a pivot table. Matrix reports are not uncommon, and are useful for measuring trends.

By Using Report Wizard

Right Click on the Reports branch of Solution Explorer.

Select Add New Report, then click Next to go past the welcome screen.

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.

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.

Click the Preview tab to see the result of your work.

Customized Report

Drag a Matrix data region to the design surface, add the fields you want to aggregate, and add the fields you want to group by to the Row Groups or Column Groups panes.

To add a matrix data region, row and column groups, and a field to a report layout
  1. Click the Design tab.
  2. In the Toolbox, click Matrix, and then click on the design surface. Report Designer adds a Matrix data region with two columns and two rows. The left corner of the matrix is aligned to the spot that you clicked.
  3. In the Report Data pane, expand the Sales dataset to display the fields.
  4. Drag the Category field from the Report Data pane to the matrix cell that displays Rows.
    When you drop the field into the cell, the following things happen:
    • The cell displays the field name, known as the field expression, in brackets: [Category].
    • The column header value displays the name of the field.
    • The row handle next to the cell displays a bracket inside the handle that indicates that this row is associated to a group.
    • The Grouping pane displays the row group Category.
  5. Drag the Geography field from the Report Data pane to the matrix cell that displays Columns.
    The column handle displays a bracket for the column group, and the Column Groups pane displays the column group Geography.
  6. Drag the LineTotal field to matrix cell that displays the text Data.
    LineTotal represents a numeric field in the dataset. The cell displays the aggregate expression [Sum(LineTotal)].
To add a nested row group

Drag the Subcat field from the Report Data pane to the Row Groups pane, and drop it under the Category group.

When you drop the field on the Row Groups pane, the following things happen:
  1. In the Row Groups pane, the new group Subcat appears, indented under the parent Category group. This shows a nested parent-child relationship.
  2. A new column is added to the matrix, in between the Category column and the Geography column. The cell in the second row displays the field name that you are grouping by: [Subcat].
  3. The column header cell displays the name of the field.
  4. The row handle for the second row displays two nested brackets inside the handle. This indicates that this row is associated to two nested groups.
To add a nested column group

Drag the CountryRegion field from the Report Data pane to the Column Groups pane and drop it under the Geography group.

When you drop the field on the Column Groups pane, the following things happen:
  1. In the Column Groups pane, the new group CountryRegion appears, indented under the parent Geography group.
  2. A new row is added to the matrix between the Geography row and the data row. The nested column header in the third column of the second row displays the field name that you are grouping by: [CountryRegion].
  3. The column handle for the third column displays two nested brackets inside the handle. This indicates that this column is associated to two nested groups.
To add an adjacent column group
  1. In the Column Groups pane, right-click Geography, point to Add Group, and click Adjacent After. The Tablix Group dialog box opens.
  2. In Group by, from the drop-down list, select [Year].
    Click OK. When you create this group, the following things happen:
    1. A group with the default name Group1 appears in the Column Groups pane as an adjacent group in relation to the Geography group.
    2. A new column is added after the Geography/CountryRegion column on which to display the group values for Year.
  3. Right-click the cell that contains [Sum(LineTotal)], and then click Copy.
  4. Right-click in the adjacent cell in the Year column, and then click Paste. This adds the aggregate expression [Sum(LineTotal)] for the column group based on [Year].
  5. (Optional) Preview the report.
The report displays unformatted values organized by category, subcategory, geography, countryregion, and year.