• Article

Tutorial: Creating a Matrix Report (Report Builder 3.0)

This tutorial teaches you how to create a basic matrix report based on sample sales data. The matrix has nested row and column groups and an adjacent column group. You will also learn how to format columns and rotate text. The following illustration shows a report similar to the one you will create.

Report that displays data in a matrix

An enhanced version of the report you will create in this tutorial is available as a sample SQL Server 2008 R2 Report Builder 3.0 report. For more information about downloading this sample report and others, see Report Builder 3.0 sample reports.

What You Will Learn

In this tutorial, you will learn how to:

  1. Create a Matrix Report and Dataset from the New Table or Matrix Wizard

  2. Organize Data and Choose Layout and Style from the New Table or Matrix Wizard

  3. Format Data

  4. Add Adjacent Column Group

  5. Change Column Widths

  6. Merge Matrix Cells

  7. Add a Report Header and Report Title

  8. Save the Report

Other Optional Step

  1. Rotate Text Box 270 Degrees

Estimated time to complete this tutorial: 20 minutes.

Requirements

For more information about requirements, see Prerequisites for Tutorials (Report Builder 3.0).

1. Create a Matrix Report and Dataset from the New Table or Matrix Wizard

From the Getting Started dialog box in Report Builder 3.0, choose a shared data source, create an embedded dataset, and then display the data in a matrix.

Note

In this tutorial, the query already contains the data values, so that it does not need an external data source. This makes the query quite long. In a business environment, a query would not contain the data. This is for learning purposes only.

To create a new matrix

  1. Click Start, point to Programs, point to Microsoft SQL Server 2008 R2 Report Builder 3.0, and then click Report Builder 3.0.

    Note

    The Getting Started dialog box should appear. If it doesn't, from the Report Builder button, click New.

  2. In the left pane, verify that New Report is selected.

  3. In the right pane, click Table or Matrix Wizard.

  4. On the Choose a dataset page, click Create a dataset.

  5. Click Next.

  6. On the Choose a connection to a data source page, select an existing data source or browse to the report server, and then select a data source. If no data source is available or you do not have access to a report server, you can use an embedded data source instead. For more information about creating an embedded data source, see Tutorial: Creating a Basic Table Report (Report Builder 3.0).

  7. Click Next.

  8. On the Design a query page, click Edit as Text.

  9. Copy and paste the following query into the query pane:

    SELECT CAST('2009-01-05' AS date) as SalesDate, 'Central' as Territory, 'Accessories' as Subcategory,'Carrying Case' as Product, CAST(16996.60 AS money) AS Sales, 68 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'North' as Territory, 'Accessories' as Subcategory, 'Carrying Case' as Product, CAST(13747.25 AS money) AS Sales, 55 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'South' as Territory, 'Accessories' as Subcategory,'Carrying Case' as Product, CAST(9248.15 AS money) As Sales, 37 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'Central' as Territory, 'Accessories' as Subcategory,'Tripod' as Product, CAST(1350.00 AS money) AS Sales, 18 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'North' as Territory, 'Accessories' as Subcategory,'Tripod' as Product, CAST(1800.00 AS money) AS Sales, 24 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'South' as Territory, 'Accessories' as Subcategory,'Tripod' as Product, CAST(1125.00 AS money) AS Sales, 15 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'Central' as Territory, 'Accessories' as Subcategory,'Lens Adapter' as Product, CAST(1147.50 AS money) AS Sales, 17 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'North' as Territory, 'Accessories' as Subcategory,  'Lens Adapter' as Product, CAST(742.50 AS money) AS Sales, 11 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'South' as Territory, 'Accessories' as Subcategory,'Lens Adapter' as Product, CAST(1417.50 AS money) AS Sales, 21 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'Central' as Territory, 'Accessories' as Subcategory, 'Carrying Case' as Product, CAST(13497.30 AS money) AS Sales, 54 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'North' as Territory, 'Accessories' as Subcategory, 'Carrying Case' as Product, CAST(11997.60 AS money) AS Sales, 48 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'South' as Territory, 'Accessories' as Subcategory, 'Carrying Case' as Product, CAST(10247.95 AS money) As Sales, 41 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'Central' as Territory, 'Accessories' as Subcategory, 'Tripod' as Product, CAST(1200.00 AS money) AS Sales, 16 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'North' as Territory, 'Accessories' as Subcategory,'Tripod' as Product, CAST(2025.00 AS money) AS Sales, 27 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'South' as Territory, 'Accessories' as Subcategory,'Tripod' as Product, CAST(1425.00 AS money) AS Sales, 19 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'Central' as Territory, 'Accessories' as Subcategory,'Lens Adapter' as Product, CAST(887.50 AS money) AS Sales, 13 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'North' as Territory, 'Accessories' as Subcategory, 'Lens Adapter' as Product, CAST(607.50 AS money) AS Sales, 9 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'South' as Territory, 'Accessories' as Subcategory,'Lens Adapter' as Product, CAST(1215.00 AS money) AS Sales, 18 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate,  'Central' as Territory, 'Digital' as Subcategory,'Compact Digital' as Product, CAST(10191.00 AS money) AS Sales, 79 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate,  'North' as Territory, 'Digital' as Subcategory, 'Compact Digital' as Product, CAST(8772.00 AS money) AS Sales, 68 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate,  'South' as Territory, 'Digital' as Subcategory, 'Compact Digital' as Product, CAST(10578.00 AS money) AS Sales, 82 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'Central' as Territory,'Digital' as Subcategory, 'Slim Digital' as Product, CAST(7218.10 AS money) AS Sales, 38 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'North' as Territory,'Digital' as Subcategory, 'Slim Digital' as Product, CAST(8357.80 AS money) AS Sales, 44 as Quantity
UNION SELECT CAST('2009-01-05' AS date) as SalesDate, 'South' as Territory,'Digital' as Subcategory,'Slim Digital' as Product, CAST(9307.55 AS money) AS Sales, 49 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate,  'Central' as Territory, 'Digital' as Subcategory,'Compact Digital' as Product, CAST(3870.00 AS money) AS Sales, 30 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate,  'North' as Territory, 'Digital' as Subcategory,'Compact Digital' as Product, CAST(5805.00 AS money) AS Sales, 45 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate,  'South' as Territory, 'Digital' as Subcategory, 'Compact Digital' as Product, CAST(8643.00 AS money) AS Sales, 67 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'Central' as Territory, 'Digital' as Subcategory, 'Slim Digital' as Product, CAST(9877.40 AS money) AS Sales, 52 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'North' as Territory, 'Digital' as Subcategory, 'Slim Digital' as Product, CAST(12536.70 AS money) AS Sales, 66 as Quantity
UNION SELECT CAST('2009-01-06' AS date) as SalesDate, 'South' as Territory, 'Digital' as Subcategory, 'Slim Digital' as Product, CAST(6648.25 AS money) AS Sales, 35 as Quantity

  10. Click Next.

2. Organize Data and Choose Layout and Style from the New Table or Matrix Wizard

Use the wizard to provide a starting design on which to display data. The preview pane in the wizard helps you to visualize the result of grouping data before you complete the matrix design.

To organize data into groups and choose a layout and style

  1. On the Arrange fields page, drag Territory from Available fields to Row groups.

  2. Drag SalesDate to Row groups and place it below Territory.

    The order in which fields are listed in Row groups defines the group hierarchy. Steps 1 and 2 organize the values of the fields first by territory, and then by sales date.

  3. Drag Subcategory to Column groups.

  4. Drag Product to Column groups, and place below Subcategory.

    The order in which fields are listed in Column groups defines the group hierarchy.

    Steps 3 and 4 organize the values for the fields first by subcategory, and then by product.

  5. Drag Sales to Values.

    Sales is summarized with the Sum function, the default function to summarize numeric fields.

  6. Drag Quantity to Values.

    Quantity is summarized with the Sum function.

    Steps 5 and 6 specify the data to display in the matrix data cells.

  7. Click Next.

  8. On the Choose the Layout page, under Options, verify that Show subtotals and grand totals is selected.

  9. Verify that Blocked, subtotal below is selected.

  10. Verify the option Expand/collapse groups is selected.

  11. Click Next.

  12. On the Choose a Style page, in the Styles pane, select Slate.

  13. Click Finish.

    The matrix is added to the design surface. The Row Groups pane shows two row groups: Territory and SalesDate. The Column Groups pane shows two column groups: Subcategory and Product. Detail data is all the data that is retrieved by the dataset query.

  14. Click Run to preview the report.

For each product that is sold on a specific date, the matrix shows the subcategory to which the product belongs and the territory of the sales.

3. Format Data

By default, the summary data for the Sales field displays a general number and the SalesDate field displays both date and time information. Format the Sales field to display the number as currency and the SalesDate field to display only the date. Toggle Placeholder Styles to display formatted text boxes and placeholder text as sample values.

To format fields

  1. Click Design to switch to design view.

  2. Press the Ctrl key, and then select the nine cells that contain [Sum(Sales)].

  3. On the Home tab, in the Number group, click Currency. The cells changeto show the formatted currency.

    If your regional setting is English (United States), the default sample text is [$12,345.00]. If you do not see an example currency value, click Placeholder Styles in the Numbers group, and then click Sample Values.

  4. Click the cell that contains [SalesDate].

  5. In the Number group, from the drop-down list, select Date.

    The cell displays the example date [1/31/2000]. If you do not see an example date, click Placeholder Styles in the Numbers group, and then click Sample Values.

  6. Click Run to preview your report.

The date values display only dates and the sales values display as currency.

4. Add Adjacent Column Group

You can nest row and column groups in parent child relationships or adjacent in sibling relationships.

Add a column group that is adjacent to the Subcategory column group, copy cells to populate the new column group, and then use an expression to create the value of the column group header.

To add an adjacent column group

  1. Click Design to return to design view.

  2. Right-click the cell that contains [Subcategory], point to Add Group, and then click Adjacent Right.

    The Tablix Group dialog box opens.

  3. In the Group By list, select SalesDate, and then click OK.

    A new column group is added to the right of the Subcategory column group.

  4. Right-click the cell in the new column group that contains [SalesDate], and then click Expression.

  5. Copy the following expression to expression box.

    =WeekdayName(DatePart("w",Fields!SalesDate.Value))

    Click OK.

    This expression extracts the weekday name from the sales date. For more information, see Expressions (Report Builder 3.0 and SSRS).

  6. Right-click the cell in the Subcategory column group that contains Total, and then click Copy.

  7. Right-click the cell immediately below the cell that contains the expression you created in step 5 and click Paste.

  8. Press the Ctrl key.

  9. In the Subcategory group, click the Sales column header and the three cells below it, right-click, and then click Copy.

  10. Paste the four cells into the four empty cells in the new column group.

  11. Click Run to preview the report.

The report includes columns named Monday and Tuesday. The dataset contains only data for these two days.

Note

If the data included other days, the report would include columns for them as well. Each column has the column header, Sales, and sales totals by territory.

5. Change Column Widths

A report that includes a matrix typically expands horizontally as well as vertically when it runs. Controlling horizontal expansion is particularly important if you plan to export the report to formats such as Microsoft Word or Adobe PDF that are used for printed reports. If the report expands horizontally across multiple pages, the printed report is difficult to understand. To minimize horizontal expansion, you can resize columns to be only the width necessary to display the data without wrapping. You can also rename columns so that their titles fit the width needed to display the data.

To rename and resize the columns

  1. Click Design to return to design view.

  2. Select the text in the furthest Quantity column to the left, and then type QTY.

    The column title is now QTY.

  3. Repeat step 2 for the other columns named Quantity. There are two of them.

  4. Click the matrix so that column and row handles appear above and next to the matrix.

    The gray bars along the top and side of the table are the column and row handles.

  5. To resize the furthest QTY column to the left, point to the line between column handles so that the cursor changes into a double arrow. Drag the column towards the left until it is 1/2 inch wide.

    A column width of 1/2 inch is adequate to display the quantity.

  6. Repeat step 5 for the other columns named QTY.

  7. Click Run to preview your report.

The columns in the report that contains quantities are now named QTY and the columns are narrower.

6. Merge Matrix Cells

The corner area is in the upper left corner of the matrix. Depending on the number of row and column groups in the matrix, the number of cells in the corner area varies. The matrix built in this tutorial has four cells in its corner area. The cells are arranged in two rows and two columns, reflecting the depth of row and column group hierarchies. The four cells are not used in this report and you will merge them into one.

To merge matrix cells

  1. Click Design to return to design view.

  2. Click the matrix so that column and row handles appear above and next to the matrix.

  3. Press the Ctrl key, and then select the four corner cells.

  4. Right-click the cells and then click Merge Cells.

  5. Right-click the corner cell, and then click Text Box Properties.

  6. Click the Fill tab.

  7. Click the (fx) button for Fill color.

  8. Copy and paste the following expression in the expression box.

    #96a4b2

    This is the RGB hex value for a gray blue color used in the Slate style.

  9. Click OK.

  10. Click OK. again to return to the design view and click Run to preview your report.

The upper corner matrix is a single cell and has the same color as the row group and column group cells.

7. Add a Report Header and Report Title

A report title appears at the top of the report. You can place the report title in a report header or if the report does not use one, in a text box at the top of the report body. In this tutorial, you will remove the text box at the top of the report and add a title to the header.

To add a report header and report title

  1. Click Design to return to design view.

  2. Click the text box at the top of the report body that contains Click to add title, and then press the Delete key.

  3. On the Insert tab of the ribbon, click Header and then click Add Header.

    A header is added to the top of the report body.

  4. On the Insert tab, click Text Box, and then drag a text box inside the report header. Make the text box about 6 inches long and 3/4 inch tall and place it on the left side of the report header.

  5. In the text box, type Sales by Territory, Subcategory, and Day.

  6. Select the text you typed, right-click, and then click Text Properties.

    Note

    To format characters at the same time, they must be contiguous.

  7. In the Text Properties dialog box, click Font.

  8. In the Font list, select Times New Roman; in Size select 24 pt, in Color select Maroon, and in Style select Italic.

  9. Click OK.

  10. Click Run to preview the report.

The report includes a report title in the report header.

8. Save the Report

You can save reports to a report server, SharePoint library, or your computer. For more information, see Report Servers and SharePoint Report Servers (Report Builder 3.0 and SSRS).

In this tutorial, save the report to a report server. If you do not have access to a report server, save the report to your computer.

To save the report on a report server

  1. From the Report Builder button, click Save As.

  2. Click Recent Sites and Servers.

  3. Select or type the name of the report server where you have permission to save reports.

    The message "Connecting to report server" appears. When the connection is complete, you will see the contents of the report folder that the report server administrator specified as the default report location.

  4. In Name, replace the default name with SalesByTerritorySubcategory.

  5. Click Save.

The report is saved to the report server. The name of report server that you are connected to appears in the status bar at the bottom of the window.

To save the report on your computer

  1. From the Report Builder button, click Save As.

  2. Click Desktop, My Documents, or My computer, and then browse to the folder where you want to save the report.

  3. In Name, replace the default name with SalesByTerritorySubcategory.

  4. Click Save.

9. (Optional) Rotate Text Box 270 Degrees

A report with matrices can expand horizontally and vertically when it runs. By rotating text boxes vertically, or 270 degrees, you can save horizontal space. The rendered report is then narrower and if exported to a format such as Microsoft Word, will be more likely to fit on a printed page.

A text box can also display text as horizontal, vertical (top to bottom). For more information, see Text Boxes (Report Builder 3.0 and SSRS).

To rotate text box 270 degrees

  1. Click Design to return to design view.

  2. Click the cell that contains [Territory].

  3. In the Properties pane, locate the WritingMode property and in its drop-down list, select Rotate270.

    If the Properties pane is not open, click the View tab of the ribbon, and then select Properties.

  4. Verify that the CanGrow property is set to True.

  5. Resize the Territory column to be 1/2 inch wide and delete the column title.

  6. Click Run to preview your report.

The territory name is written vertically, bottom to top. The height of the Territory row group varies by the length of the territory name.