{"id":1537,"date":"2012-12-04T00:00:00","date_gmt":"2012-12-04T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/report-builder-3-0-adding-matrices-to-your-reports\/"},"modified":"2021-05-17T18:33:18","modified_gmt":"2021-05-17T18:33:18","slug":"report-builder-3-0-adding-matrices-to-your-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/report-builder-3-0-adding-matrices-to-your-reports\/","title":{"rendered":"Report Builder 3.0: Adding Matrices to Your Reports"},"content":{"rendered":"
\n

In the first four articles of the Report Builder 3.0 series (article 1<\/a> | article 2<\/a> | article 3<\/a> | article 4<\/a>), you learned how to add tables, charts, and maps to a report and configure their properties. In this article, you’ll learn how to add and configure a matrix. <\/p>\n

A matrix is a type of table that lets you aggregate data across both rows and columns, similar to a crosstab or pivot table. You set up the matrix by defining groups of data in a hierarchical order. For example, one of your groups might be based on dates, starting with years, then quarters, and finally months. The years would be at the top of the hierarchy and the months at the bottom. Repot Builder automatically aggregates the data based on how you’ve defined your groups across the rows and columns. In fact, Report Builder automates much of this process. You simply drag the fields you want to include from your dataset to the appropriate row or column, and then format the matrix components as best fits your needs. <\/p>\n

In this article, we’ll add a matrix to a report that shows the number of bicycles sold by the AdventureWorks bicycle company (Microsoft’s fictitious company used to provide sample SQL Server data). The matrix will group data by product subcategory (types of bikes) and product names. It will also group data by the year and quarter the bikes were sold. If you want to create this matrix on your own system, you’ll need to create a Report Builder report and add a data source and dataset that retrieve AdventureWorks data. <\/p>\n

On my system, I created a data source that connects to the AdventureWorks2012<\/strong> database on a local instance of SQL Server 2012. I named the data source AdventureWorks<\/strong>. I then created a dataset that uses the AdventureWorks<\/strong> data source to retrieve the necessary sales data. I named the dataset ProductsSold<\/strong>. Finally, I configured the dataset with the following T-SQL query: <\/p>\n

SELECT\n  s.Name AS SubcategoryName,\n  p.Name AS ProductName,\n  d.OrderQty,\n  YEAR(h.OrderDate) AS OrderYear,\n  DATEPART(qq, h.OrderDate) AS OrderQuarter\nFROM \n  Production.ProductSubcategory s\n  INNER JOIN Production.Product p\n    ON s.ProductSubcategoryID = p.ProductSubcategoryID\n  INNER JOIN Sales.SalesOrderDetail d\n    ON p.ProductID = d.ProductID\n  INNER JOIN Sales.SalesOrderHeader h\n    ON d.SalesOrderID = h.SalesOrderID\nWHERE s.ProductSubcategoryID BETWEEN 1 AND 3; <\/pre>\n
For each order, the SELECT<\/strong> statement retrieves the number of bicycles sold, the subcategory and product names, and the year and quarter the bikes were ordered. The ProductSubcategoryID<\/strong> value, specified in the WHERE<\/strong> clause, determines that only bicycles are returned and not other products. <\/p>\n
Once you’ve created your data source and dataset, you’re ready to create a matrix. The first step is to add the matrix to your report’s design surface and then to add the initial fields, so let’s get started with both of those tasks. <\/p>\n
Adding a Matrix to Your Report<\/h2>\n
If you’ve read the other articles in this series, you already know the basics of adding an object to a report. You follow similar steps for a matrix. Go to the Insert<\/strong> ribbon, click the Matrix<\/strong> icon, and then click InsertMatrix<\/strong>. Move your cursor to the report design surface, to the spot where you want to position the top-left corner of the matrix. The cursor should now appear with a tiny table icon. <\/p>\n
Click the left mouse button and drag the icon to the spot where you want to position the bottom-right corner of the matrix. When you let go, your design surface should look similar to the one shown in Figure 1. At this point, don’t worry too much about how you position and size your matrix. You’ll have plenty of opportunity to configure it as you’re adding other components. <\/p>\n
\"1696-b15f832b-d44b-489c-b141-422c235ea24\"<\/p>\n
Figure 1: Adding a matrix to your report<\/p>\n
Once you’ve added the matrix object, you can start adding the fields that define the columns and rows-and that also define the initial groups. The simplest way to do this is to add the top-level field from each group hierarchy. For this report, we’ll include two groups: one based on dates and one based on products. The top level of the date hierarchy will be the OrderYear<\/strong> field, and the top level of the product hierarchy will be the SubcategoryName<\/strong> field. The OrderQty<\/strong> field, which reflects the number of bicycles sold with each order, will provide the values to be aggregated. In other words, the number of bikes sold will be broken down by year and quarter as well as by subcategory and product. <\/p>\n
To set up your matrix, take the following steps: <\/p>\n
    \n
  1. Drag the OrderYear<\/strong> field from the ProductsSold<\/strong> dataset in the ReportData<\/strong> window to the Columns<\/strong> cell of the matrix.  <\/li>\n
  2. Drag the SubcategoryName<\/strong> field from the dataset to the Rows<\/strong> cell of the matrix.  <\/li>\n
  3. Drag the OrderQty<\/strong> field from the dataset to the Data<\/strong> cell of the matrix. <\/li>\n<\/ol>\n
    Your matrix should now look similar to the one shown in Figure 2. <\/p>\n
    \"1696-800d4e41-800f-46b1-a0fe-748437a0227\"<\/p>\n
    Figure 2: Adding fields to your matrix<\/p>\n
    There are several things to note about the matrix at this point. First, when you’re working within the matrix, the two-sided frame becomes active. That’s the thick grey border to the left and at the top. Also, the OrderYear<\/strong> and SubcategoryName<\/strong> fields are listed just as they appear in the dataset, except they’re enclosed in brackets to ensure that the name in its entirety is used correctly, should it contain spaces or special characters. The OrderQty<\/strong> field is treated differently, however. Because it’s been added to the Data<\/strong> cell, Report Builder assumes that this is the field that should be aggregated and applies the Sum<\/strong> aggregate function to the field. Finally, Report Builder also adds a column name above the SubcategoryName<\/strong> field. In this case, Report Builder automatically breaks the compound name into two words: SubcategoryName<\/strong>. <\/p>\n
    It’s not only the matrix itself that you should be aware of. Notice at the bottom of the design surface there are two windows: RowGroups<\/strong> and ColumnGroups<\/strong>. Report Builder automatically defines your initial groups based on the fields you added to the matrix. Because the SubcategoryName<\/strong> field is added as a row, Report Builder creates a row group that includes the SubcategoryName<\/strong> field. The same goes for the OrderYear<\/strong> field. Because it’s added as a column, Report Builder creates a column group that includes the OrderYear<\/strong> field. <\/p>\n
    At this point, you should run your report to see how the matrix appears when it’s rendered. Figure 3 shows what the matrix looks like on my system. As you can see, a column has been added for the subcategories as well as for each year, and a row has been added for each subcategory. <\/p>\n
    \"1696-52661701-c5d8-448f-b56d-2f668ab6c41\"<\/p>\n
    Figure 3: Viewing your matrix after adding the fields<\/p>\n
    Also notice in Figure 3 that the OrderQty<\/strong> values have been aggregated according to the groups we created. For example, according to the data, AdventureWorks sold a total of 2,415 mountain bikes in 2005 and 7,702 road bikes in 2008. As you can see, Report Builder does all the heavy lifting for you. All you have to do is drag the fields into the correct positions. <\/p>\n
    Expanding Your Groups<\/h2>\n
    The power of matrices really becomes apparent when you start adding levels to your group hierarchies. For our report, we’ll break the years down to quarters and the subcategories down to products. The next step, then, is to drag the OrderQuarter<\/strong> field from the dataset to the OrderYear<\/strong> cell in the matrix. When you do this, be sure that a thick blue line appears at the bottom of the OrderYear<\/strong> cell before you release your mouse button. This tells Report Builder to add the OrderQuarter<\/strong> field below the OrderYear<\/strong> field and subsequently at a lower level of the hierarchy. <\/p>\n
    Next, drag the ProductName<\/strong> field from the dataset to the SubcategoryName<\/strong> cell in the matrix. This time, make certain the thick blue line appears to the right of the cell. Again, you’re telling Report Builder to add the new field as the next level of the hierarchy. Your matrix should now look similar to the one shown in Figure 4. <\/p>\n
    \"1696-fd3d8a12-f886-4f75-b978-b407ebe1335\"<\/p>\n
    Figure 4: Adding a field to each group in your matrix<\/p>\n
    As you can see, a new row has been added for the OrderQuarter<\/strong> field and a new column for the ProductName<\/strong> field. This is the method that Report Builder uses to indicate that the groups now have multiple levels. Also notice that the new fields have been added as second levels to the groups shown in the RowGroups<\/strong> and ColumnGroups<\/strong> windows. <\/p>\n
    Once again, you should run your report to see what effect your changes have had. Figure 5 shows how the matrix now looks on my system (click to enlarge). <\/p>\n
    \"1696-9639c76b-843a-49c8-8b3b-19c1489d82d\"<\/a> <\/p>\n
    Figure 5: Viewing your matrix with the expanded groups<\/p>\n
    Because we’ve added the additional fields to our matrix groups, our report is much larger now, so what is shown in the figure is only the first part of the report. But as you can see, products and quarters are now included in the report, with the order quantities aggregated accordingly. <\/p>\n
    You’ve probably noticed that the matrix is not particularly readable, even though it contains the information we want. Fortunately, Report Builder includes a number of features that let us refine how the matrix appears, so let’s return to the design surface and play around with some of those options. <\/p>\n
    Formatting Your Matrix<\/h2>\n
    The first thing you’ll want to do is to adjust your column and row sizes to better accommodate the data. To do this, click somewhere in the matrix to bring up the gray frame on the left and at the top. You’ll notice that the frame is broken into sections that correspond to the rows and columns. Use those sections to adjust the cell sizes. You might need to run your report after each adjustment to get the matrix just the way you want it. <\/p>\n
    This is also a good time to position the matrix where you want it in the report and adjust any other report elements. For example, I added a report title, AnnualBikeSales<\/strong>, and repositioned it. I also removed the default text box in the footer (the one the displays the execution time). <\/p>\n
    Another step I took was to configure the column heads. For the SubcategoryName<\/strong> column, I simplified the name to Subcategory<\/strong>, and for the ProductName<\/strong> column, I simplified the name to Product<\/strong>. I also made the text bold. To take these steps, I simply edited the names directly in the cells, as I would in Word or Excel. I also made the OrderYear<\/strong> cell bold and centered, and I centered the OrderQuarter<\/strong> cell. <\/p>\n
    Again, when making such modifications, run your report often. Report Builder makes it easy to see your changes immediately. When I finished with my initial formatting, my design surface looked like the one shown in Figure 6. <\/p>\n
    \"1696-226df8a5-0d52-40e1-87b5-31f9a424553\"<\/p>\n
    Figure 6: Formatting your matrix<\/p>\n
    One of the trade-offs when resizing your columns and rows is that the matrix itself, in design view, often becomes less readable. As you can see in Figure 6, this is particularly the case for the OrderYear<\/strong> column. However, we don’t want this column too wide because it will spread the data too far when displayed. Don’t forget, you now have a column for each quarter of each year. For any report, you always want to keep your data in mind, even if it means a little inconvenience in the design phase. <\/p>\n
    After you’ve completed your formatting, you should, of course, run the report once more. Figure 7 shows what the first page of the matrix now looks like on my system when the report is rendered (click to enlarge). <\/p>\n
    \"1696-d24cb34f-b534-46a8-8c76-dff622a152d\"<\/a> <\/p>\n
    Figure 7: Viewing your matrix after it has been formatted<\/p>\n
    Notice that the products are now listed in a single line and the quantity columns are narrower, making it easier to display more information in a smaller space. Also notice that the column heads are bold and the year and quarter labels are centered. As a result of these changes, the matrix is much more readable. <\/p>\n
    Adding Totals to Your Matrix<\/h2>\n
    Although the data so far might be useful, with totals for each product for each quarter, it would also be helpful to include totals that give us an overview of the sales, such as how many mountain bikes sold each quarter. Fortunately, Report Builder makes this as easy-if not easier-than creating the matrix itself. <\/p>\n
    For our report, we’ll add totals to the bottom of the report (for all subcategories), for individual subcategories, and for the years. To add totals for all subcategories, right-click the SubcategoryName<\/strong> cell, point to AddTotal<\/strong>, and then click After<\/strong>. Report Builder adds a row to the bottom of the matrix, as shown in Figure 8. Notice that the label Total<\/strong> is also included, along with the aggregated quantities in the last cell. <\/p>\n
    \"1696-a7e9031c-734b-4420-ac79-7869ec5f6c8\"<\/p>\n
    Figure 8: Adding a total to the subcategory group<\/p>\n
    Now we’ll do the same thing for the ProductName<\/strong> and OrderYear<\/strong> cells. Once again, right-click the cell, point to AddTotal<\/strong>, and then click After<\/strong>. For the ProductName<\/strong> total, Report Builder adds another row, but for the OrderYear<\/strong> total, Report Builder adds a column, as shown in Figure 9. <\/p>\n
    \"1696-b7d42a1e-c7a6-4c45-9a6d-8b1eca8e8fe\"<\/p>\n
    Figure 9: Adding totals by year and product<\/p>\n
    As you can see, a Total<\/strong> row has been added after the ProductName<\/strong> row, and a Total<\/strong> column has been added after the OrderYear<\/strong> column. Now when you review your report, it will have additional aggregated data across these subcategories. Figure 10 shows what the last page of the report looks like on my system. <\/p>\n
    \"1696-066b14cd-fc99-4b5e-b37b-414dc5c0d24\"<\/a> <\/p>\n
    Figure 10: Viewing your report after totals have been added<\/p>\n
    The report now includes the additional rows and column. The last row shows total number of bikes sold for each quarter for all subcategories of bicycles. The second-to-last row provides the totals for each quarter for that specific subcategory, in this case, touring bikes. In addition, the column to the right displays the total number of each bike sold for all four years. <\/p>\n
    Configuring the Values in Your Matrix<\/h2>\n
    Adding the totals to our matrix makes it richer in detail but it also calls attention to the fact that we need to format it further to make sure it’s easy to read. The first step is to configure the way the quantities are displayed. For each cell that contains aggregated OrderQty<\/strong> values, right-click the cell, and click TextBoxProperties<\/strong>. When the TextBoxProperties<\/strong> dialog box appears, go to the Number<\/strong> page, as shown in Figure 11. <\/p>\n
    \"1696-RptBldr05_Fig11.jpg\"<\/p>\n
    Figure 11: Configuring the numbers in your matrix<\/p>\n
    On the Number<\/strong> page, select Number<\/strong> from the Category<\/strong> list. Then set the Decimalplaces<\/strong> option to 0<\/strong>, and select the Use1000separator<\/strong> checkbox. <\/p>\n
    Next, we want to change the way a couple of our column labels are displayed. The first is the OrderQuarter<\/strong> column. Currently, we display only a number, but it might be nice to use something a bit more descriptive. So right-click the OrderQuarter<\/strong> cell, and then click Expression<\/strong>. In the Expression<\/strong> dialog box, modify the expression so it reads as follows: <\/p>\n
        =\"Qtr \" & Fields!OrderQuarter.Value <\/pre>\n
    The formula simply adds Qtr<\/strong> (plus a space) before each quarter number so they’re not just floating out there and perhaps causing a moment of confusion. <\/p>\n
    Next, click the Total<\/strong> cell directly beneath the ProductName<\/strong> cell, and then click Expression<\/strong>. In the Expression<\/strong> dialog box, modify the expression so it reads as follows: <\/p>\n
        =\"Total \" & Fields!SubcategoryName.Value <\/pre>\n
    This time, we’ve added the subcategory name after each total to make it easier to identify what this total is for. <\/p>\n
    Now is also a good time to refine any other formatting elements you think necessary. For example, I made all column and row labels bold, along with all total amounts. Figure 12 shows what my design surface looks like after applying all this formatting. <\/p>\n
    \"1696-f2b0caff-2732-4146-ae97-91754536db2\"<\/p>\n
    Figure 12: Configuring the cell values in your matrix<\/p>\n
    The <<Expr>><\/strong> placeholder now appears where I defined expressions on the columns. Also, most of the cells are now bold, except those that include the subcategory and product names as well as the individual base totals (per quarter, per product). Figure 13 shows what the first page of the report now looks like on my system. <\/p>\n
    \"1696-8462df5f-16ff-47ec-9c42-1ec5a263e57\"<\/a> <\/p>\n
    Figure 13: Viewing your matrix after configuring the cell values<\/p>\n
    Notice in the Total<\/strong> column that the comma is added to the numbers, where appropriate, and that the figures are in bold. Notice also that the quarter labels now include Qtr<\/strong>. If you were to scroll down, you would see the other changes we made. <\/p>\n
    Applying Advanced Formatting to Your Matrix<\/h2>\n
    Now let’s put a few finishing touches on our report. The first is to ensure that our header rows are visible on every page and that they scroll with the user so longer pages are easier to read. To modify the matrix in this way, first make sure the gray frame is active. Right-click the frame and then click TablixProperties<\/strong>. In the Tablixproperties<\/strong> dialog box, select the Repeatcolumnheadersoneachpage<\/strong> option and the Keepheadervisiblewhilescrolling<\/strong> option, as shown in figure 14. <\/p>\n
    \"1696-RptBldr05_Fig14.jpg\"<\/p>\n
    Figure 14: Configuring your matrix properties<\/p>\n
    Next, we want to control where the page breaks occur in our report. Because the report is divided into product subcategories, each subcategory would make a good place for the pages to break. To do this, we need to change the property settings for that group. In the RowGroups<\/strong> window, right-click SubcategoryName<\/strong> and then click GroupProperties<\/strong>. When the GroupProperties<\/strong> dialog box appears, go to the PageBreaks<\/strong> page and select the Betweeneachinstanceofagroup<\/strong> option, as shown in Figure 15. <\/p>\n
    \"1696-RptBldr05_Fig15.jpg\"<\/p>\n
    Figure 15: Configuring the subcategory group properties<\/p>\n
    The next step is to add borders around our cells. To do so, select all the cells by clicking the top-left one, pressing the shift key, and then clicking the bottom-right one. Then, on the toolbar, select 3\/4 pt<\/strong> width and click All<\/strong> in the BorderSide<\/strong> drop-down list. This should add borders around all your cells. <\/p>\n
    Another step is to merge some of our cells. Let’s start with the Subcategory<\/strong> cell and the one below it. Select both cells, right-click them, and then click MergeCells<\/strong>. Do the same thing for the Total<\/strong> cell in the top-right corner and the cell beneath it. Then repeat the process for the Product<\/strong> cell and the one above that. When you do the Product<\/strong> cell, you might lose the Product<\/strong> label, so you’ll have to retype it if you do. <\/p>\n
    The final step is to add color to your matrix. As with any of the options, the colors you choose are up to you. To add color, select the cells that will share the same color. In the menu bar, click the Shading<\/strong> down-arrow and select your color. If you want to view more options, click MoreColors<\/strong> instead of selecting a color, and then choose a color from the expanded menu. When you’re finished, your design surface should look similar to the one shown in Figure 16. <\/p>\n
    \"1696-f36e5207-9e46-4700-b4a2-c6426d2886d\"<\/p>\n
    Figure 16: Adding color to your matrix<\/p>\n
    Your color scheme might look quite different than this, but what I’ve done here should give you a sense of what’s possible. When you run the report, you should see all the changes you’ve made, including the colors and borders. Figure 17 shows how the report’s first page now looks on my system, with all the formatting applied. <\/p>\n
    \"1696-b6fb2780-a8a7-4007-aa89-ef940ba9bb5\"<\/a> <\/p>\n
    Figure 17: Viewing the first page of our report after adding color to the matrix<\/p>\n
    As you can see, the report now includes all the formatting changes, which should make the data easier to understand. If you were to scroll down toward the bottom of this page, the header rows would follow along so you always know what the data means. If you were to go to the end of the report, you would find it on the third page because there are only three subcategories and we’ve configure the matrix to display each subcategory on its own page. Figure 18 shows what the report’s last page looks like on my system. <\/p>\n
    \"1696-14a8c864-f24e-4d3f-ae4f-fd099719b3e\"<\/a> <\/p>\n
    Figure 18: Viewing the last page of our report after applying advanced configuration settings<\/p>\n
    All touring bike figures are now displayed on a single page, and that page displays the header rows, with the totals at the bottom. You can also see the cells that we’ve merged and the colors that we’ve added to the subcategory totals. <\/p>\n
    As this article has demonstrated, creating a basic matrix is a relatively simple process in Report Builder. The real work comes in formatting the matrix and ensuring that everything is displayed exactly how you want to. Yet what we’ve done here barely scratches the surface in terms of the formatting options available to you. You can, for example, create property expressions that display totals according to specific colors, based on what those amounts are. Or you can alternate the colors used for rows. But at least now you have a sample of what a rich and powerful tool the matrix provides. Like all elements of Report Builder, the key is to get in there and try it out for yourself and experiment with the different configuration options available to you. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    It is easy to create a basic matrix in Report Builder. However, it takes some practice in order to format and display the matrix exactly how you want it. There are a large number of options available to enhance the matrix and Robert provides enough information to get you the point where you can experiment easily.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[5770,5707,5527,4379,4150],"coauthors":[],"class_list":["post-1537","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-matrices","tag-report-builder-3-0","tag-reporting","tag-reporting-services","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1537","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1537"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1537\/revisions"}],"predecessor-version":[{"id":91020,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1537\/revisions\/91020"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1537"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1537"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1537"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1537"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}