Problem solve Get help with specific problems with your technologies, process and projects.

Creating matrix reports with Report Builder 3.0

Matrix tables display data similar to pivot tables, but setting them up for SQL Server requires an understanding of the basic components.

Part 1 | Part 2 | Part 3

Once your data source and dataset is setup, you are ready to add a new matrix table to your report. This article demonstrates how to get started, with details on adding hierarchies and totals to your table with Report Builder 3.0.

After you've defined your data source and dataset, you can add the matrix to the report. On the Insert menu (ribbon), click the down-arrow on the Matrix icon and select Insert Matrix. Next, position the cursor on the report design surface. The cursor turns into a plus sign with a tiny table beneath it. This represents the top-left corner of the matrix. Click the design surface and drag the cursor to your right and down to create the shape of the table. When you release the mouse button, your design surface should look similar to the one shown in Figure 1.

Figure 1: Adding a matrix to a report (click to enlarge)
Adding a matrix to a report

At this point, don't worry about resizing the rows or columns. It's easier to resize the table and reconfigure matrix properties after the necessary row and column groups have been added.

What else can you do with Report Builder 3.0?

Check out Robert Sheldon's three previous series on adding chartsgauges and maps to SSRS reports.

The first step is to drag the SalesGroup column from the InternetSales dataset to the Columns section of the matrix. The dataset is listed in the left pane, under the Datasets node. If the InternetSales dataset and its columns are not visible, expand the Datasets node as necessary. The SalesGroup column becomes your first column group in the matrix.

Next, drag the OrderYear column to the Rows section of the matrix. The OrderYear column becomes your first row group in the matrix. Finally, drag the SalesAmount column to the Data section. The SalesAmount column contains the data that will be aggregated when the report is rendered. When you add the column, an aggregate expression is automatically created. Your matrix should now look similar to the one shown in Figure 2.

Figure 2: Adding groups to a matrix in a Report Builder 3.0 report (click to enlarge)
Adding groups to a matrix in a Report Builder 3.0 report

After you add elements to a matrix, it's often a good idea to preview the data to make certain that what you have is what you want. To preview the matrix, click the Run button on the Home menu. The design surface will switch to the view surface and the matrix will be displayed, a shown in Figure 3.

Figure 3: Previewing a matrix that contains a row group and column group (click to enlarge)
Previewing a matrix that contains a row group and column group

As you can see, the data is grouped by year (the row group) and territory (the column group). For each combination of year and territory, an aggregated total of the sales amount is provided. For example, the sales amount for Europe in 2005 was 709947.2016. (As noted above, we'll configure the matrix properties after we've added all our row and column groups.)

Adding group hierarchies

Although the matrix provides useful information as it's currently set up, you can make the report more valuable by adding additional groups. The most effective way to add groups is to create hierarchies. For example, the territories, as they exist now, are simply divided into sales groups. Sales groups, however, can be divided into countries. As a result, you can have a natural hierarchy, with the SalesGroup column acting as the parent of the SalesCountry column. If your matrix reflects this hierarchy, the aggregated data will become more granular and, subsequently, more useful.

To create this hierarchy, drag the SalesCountry property from the InternetSales dataset to the [SalesGroup] cell. The trick to creating a hierarchy by dragging a column is to make sure that the direction in which the hierarchy should flow is indicated by a bold border on the cell (this makes a lot more sense when you actually do it). In other words, when you drag the SalesCountry column to the [SalesGroup] cell in the matrix, the bottom of the [SalesGroup] cell should turn bold. When it does, release the mouse button. A new [SalesCountry] cell is then inserted beneath the [SalesGroup] cell.

The same logic applies to creating a hierarchy of dates. In this case, we want a hierarchy that includes OrderYear at the top, OrderQuarter at the next level, and OrderMonth at the bottom of the hierarchy. To create this hierarchy, drag the OrderQuarter column from the InternetSales dataset and drop it on the [OrderYear] cell in the matrix. Before you drop the OrderQuarter column, make sure that the right border of the [OrderYear] cell is bold. Now do the same thing with the OrderMonth column. Drag it to the [OrderQuarter] cell and drop it when the right border is bold. Your matrix should now look similar to the one shown in Figure 4.

Figure 4: Creating hierarchies for row groups and column groups (click to enlarge)
Creating hierarchies for row groups and column groups

One thing you might have noticed is that the row and column groups are now listed in the bottom two panes, with the hierarchies defined as we specified. As you'll see later, you can modify group properties to affect their behaviors and how they're displayed.

After you add the groups and create your hierarchies, you should once again preview the data. Figure 5 shows what part of the matrix will look like with the two new hierarchies. (When the report is rendered, the entire matrix now contains many more columns and rows.)

Figure 5: Previewing a matrix that contains multiple row groups and column groups (click to enlarge)
Previewing a matrix that contains multiple row groups and column groups

As you can see, the row groups are organized into a single hierarchy based on year, quarter and month. Each year is divided into the appropriate quarters (only those quarters in which sales exist), and each quarter is divided into months. The column groups are also organized into a territory hierarchy, with each sales group divided into countries.

Adding totals to a matrix

Notice in Figure 5 that an aggregated sales total is provided at the lowest level of each hierarchy. Still, it would also be nice to have totals summarizing other levels of the hierarchy. For example, you might want to see totals for each year and totals for all years.

Report Builder 3.0 includes a feature that lets you easily add total columns to the matrix. For example, to add a total at the end of all rows, right-click the [OrderYear] cell in the matrix, point to Add Total, and then click After. A row is added to the matrix that shows the word "Total" in the first cell and aggregation expressions in the cell beneath the territory hierarchy. Now repeat the process for the [OrderQuarter] cell. This time a row is inserted beneath the [OrderQuarter] cell, but to the right of the [OrderYear] cell. Finally, repeat the process for the [SalesGroup] cell. Note that because this is a column group, a new column is added rather than a row, as shown in Figure 6.

Figure 6: Adding total rows and columns to a matrix (click to enlarge)
Adding total rows and columns to a matrix

Now if you preview the report, you'll see that row totals have been added after each set of quarters (at the end of the year) as well as after all the years. In addition, a column has been added to the right of all the data. The column displays the aggregated sales totals for each row. Figure 7 shows part of the matrix, with totals provided after each set of quarters.

Figure 7: Previewing a matrix that contains total rows and columns (click to enlarge)
Previewing a matrix that contains total rows and columns

As you can see, the aggregated totals have been automatically generated. Unfortunately, these totals are not always easy to pick out. In fact, it can be difficult to distinguish one quarter from the next, particularly when you view the right-hand side of the report. For this reason, you should configure the matrix to ensure that the data is displayed in a way that is easily understood.

Continue to part three


THE MATRIX IN REPORT BUILDER 3.0
 Part 1: Enter the matrix
 Part 2: Creating matrix reports
 Part 3: Matrix configuration techniques

ABOUT THE AUTHOR:
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

This was last published in March 2010

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close