{"id":85295,"date":"2019-10-01T00:40:01","date_gmt":"2019-10-01T00:40:01","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=85295"},"modified":"2022-04-24T20:50:10","modified_gmt":"2022-04-24T20:50:10","slug":"reporting-services-basics-adding-groups-to-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/reporting-services-basics-adding-groups-to-reports\/","title":{"rendered":"Reporting Services Basics: Adding Groups to Reports"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. Reporting Services Basics: Overview and Installation<\/a><\/li>\n
  2. Reporting Services Basics: Creating Your First Report<\/a><\/li>\n
  3. Reporting Services Basics: Data Sources and Datasets<\/a><\/li>\n
  4. Reporting Services Basics: Parameters<\/a><\/li>\n
  5. Reporting Services Basics: Adding Groups to Reports<\/a><\/li>\n<\/ol>\n\n

    So far in this series, I\u2019ve shown you how to create a basic report with parameters. The next necessary skill for report developers is to add grouping levels to the report. Managers often want to see subtotals, for example, at various levels, and adding groups is the way to do this. The report might be divided into categories and subcategories, maybe by locations and departments, or possibly by year and month. Whenever there is a hierarchical relationship in the data, it might make sense to add groups based on those relationships.<\/p>\n

    There are several ways to add groups, but I\u2019m going to show you the ones that have worked the best for me.<\/p>\n

    The Groups section<\/h2>\n

    When you look at a report in Visual Studio in design view, you will see a section under the report canvas with Row Groups<\/em> and Column Groups<\/em> as shown in Figure 1.<\/p>\n

    <\/p>\n

    Figure 1: The groups section<\/p>\n

    If you don\u2019t see the section, click on the report and then select Report View Grouping<\/em> from the menu as shown in Figure 2 to make it visible.<\/p>\n

    <\/p>\n

    Figure 2: Where to turn on the Grouping section<\/p>\n

    You may be wondering what the difference is between column and row groups, and they are quite different. Row groups are used to organize the report into horizontal sections in a typical report. Figure 3 shows one page of a report with row groups.<\/p>\n

    <\/p>\n

    Figure 3: A report with row groups<\/p>\n

    Column groups are used in matrix reports, which might also be described as \u201cpivot\u201d reports. A matrix report displays data from a column across the top of the report as headings. You can also add row groups to matrix reports. For example, you might want to display several years across a report along with row groups for category and subcategory as shown in Figure 4.<\/p>\n

    <\/p>\n

    Figure 4: A matrix report<\/p>\n

    The SSRS Toolbox contains Table<\/em> and Matrix<\/em> items. When you add either one to a report, you\u2019ll notice that each is called a Tablix <\/em>instead of the original name. It\u2019s also possible to turn a Table<\/em> into a Matrix<\/em> by adding column groups or turn a Matrix<\/em> into a Table<\/em> by removing the column groups. In my experience, it is better to start with the one you need.<\/p>\n

    For years, I used the SSRS wizard to create matrix reports, but eventually I realized that simple matrix reports were not that difficult.<\/p>\n

    Creating a matrix report<\/h2>\n

    In this example, I\u2019ll show you how to create the report shown in Figure 4. If you need help with setting up an SSRS project or creating data sources or datasets, be sure to go back to the earlier articles in this series to learn more.<\/p>\n

    To get started, you\u2019ll need an SSRS project with a shared data source pointing to the AdventureWorks2017 database. Create a new report by right-clicking Reports<\/em> and selecting Add New Item\u2026. <\/em> In the Add New Item <\/em>dialog, select Report<\/em>. Name the report Sales by year and category<\/em> and click Add<\/em>. The dialog will look similar to Figure 5.<\/p>\n

    <\/p>\n

    Figure 5: Creating a new report<\/p>\n

    Inside the Report Data<\/em> window, create a Data Source<\/em> that points to the project\u2019s Shared Data Source<\/em>. Create a Dataset<\/em> that is embedded in the report named ProductSales<\/em> with the query below:<\/p>\n

    SELECT PC.Name AS Category,\r\n       PS.Name AS SubCategory,\r\n       P.Name AS ProductName,\r\n       P.ProductID,\r\n       YEAR(SOH.OrderDate) AS OrderYear,\r\n       SUM(SOD.OrderQty) AS Quantity,\r\n       SUM(SOD.LineTotal) AS SalesAmt\r\nFROM Production.Product AS P\r\n    JOIN Sales.SalesOrderDetail AS SOD\r\n        ON SOD.ProductID = P.ProductID\r\n    JOIN Sales.SalesOrderHeader AS SOH\r\n        ON SOH.SalesOrderID = SOD.SalesOrderID\r\n    JOIN Production.ProductSubcategory AS PS\r\n        ON PS.ProductSubcategoryID = P.ProductSubcategoryID\r\n    JOIN Production.ProductCategory AS PC\r\n        ON PC.ProductCategoryID = PS.ProductCategoryID\r\nGROUP BY YEAR(SOH.OrderDate),\r\n         PC.Name,\r\n         PS.Name,\r\n         P.Name,\r\n         P.ProductID;<\/pre>\n
    After creating the dataset, the Report Data<\/em> window should look like Figure 6.<\/p>\n
    <\/p>\n
    Figure 6: The Report Data window<\/p>\n
    The next step is to drag a Matrix<\/em> object to the report canvas from the Toolbox<\/em> window. You can also right click the canvas and select Insert Matrix<\/em>. The empty matrix object will look like Figure 7.<\/p>\n
    <\/p>\n
    Figure 7: The empty Matrix<\/p>\n
    When creating a matrix report, the trick is figuring out what goes where. The Columns<\/em> cell will contain the column you want displayed as headings across the report. The Data<\/em> cell will contain the value you want to aggregate. The Rows<\/em> cell will be the remaining columns. In this case, the report must display the years across the report and add up the sales amount.<\/p>\n
    Drag OrderYear<\/em> to the Columns<\/em> cell, SalesAmt<\/em> to the Data<\/em> cell, and Category<\/em> to the Rows<\/em> cell as shown in Figure 8.<\/p>\n
    <\/p>\n
    Figure 8: Report after adding main items<\/p>\n
    Notice that you can see the grouping levels in the grouping section. When you run the report, it will look something like Figure 9.<\/p>\n
    <\/p>\n
    Figure 9: The report so far<\/p>\n
    The report\u2019s not pretty at this point, but it does display as expected with the years going across the top as headings. The original report also includes subcategories. Go back to design view and right click on the Category<\/em> group in the Row Groups<\/em> section. Click Add Group Child Group\u2026 <\/em>as shown in Figure 10.<\/p>\n
    <\/p>\n
    Figure 10: Adding a Child Group<\/p>\n
    This brings up the Tablix group<\/em> dialog. Select SubCategory<\/em> as shown in Figure 11.<\/p>\n
    <\/p>\n
    Figure 11: The Tablix group dialog<\/p>\n
    After clicking OK, the report should now look like Figure 12.<\/p>\n
    <\/p>\n
    Figure 12: After adding SubCategory<\/p>\n
    When previewing the report, the top of it should look like Figure 13.<\/p>\n
    <\/p>\n
    Figure 13: The report<\/p>\n
    You can format your report or not as desired since this article is about grouping, but at this point, I have to add some formatting for my own sanity! In this case, I have bolded the top row and the first two columns. Everything should end up bolded except for the Data<\/em> cell as shown in Figure 14.<\/p>\n
    <\/p>\n
    Figure 14: Bold everything except for the total<\/p>\n
    I also formatted the SalesAmt<\/em> in the Data<\/em> cell to be Currency<\/em> with a thousands separator and no decimal places and added a heading with the report name. The default margins are too wide for this report, so I also changed them in the Report Properties<\/em> and made sure that everything in the report was pulled to the left. (To learn more about formatting, see the previous articles.)<\/p>\n
    After the formatting is complete, the report looks like Figure 15.<\/p>\n
    <\/p>\n
    Figure 15: The formatted report<\/p>\n
    The next step is to add some totals to the report. It is so easy to do when working with matrix reports! Right-click the Data<\/em> cell that contains the summed SalesAmt<\/em> and select Add Total Row<\/em>. Repeat for Add Total Column<\/em>. Figure 16 shows you where to find these.<\/p>\n
    <\/p>\n
    Figure 16: Adding totals<\/p>\n
    Adding these two totals will leave your report canvas looking like Figure 17.<\/p>\n
    <\/p>\n
    Figure 17: After adding the row and column totals<\/p>\n
    You might just guess the next step: adding a grand total in the empty cell. Hover over the cell until the column list appears and click it as shown in Figure 18. Select SalesAmt<\/em> which will automatically sum.<\/p>\n
    <\/p>\n
    Figure 18: Adding SaleAmt to the empty cell for a grand total<\/p>\n
    I also had to format that last cell since it didn\u2019t pick up the previous formatting. After running the report, the first page in Print Layout<\/em> mode looks like Figure 19.<\/p>\n
    <\/p>\n
    Figure 19: The matrix report with totals in Print Layout mode<\/p>\n
    Obviously, you can add colors, a footer, and more to make this report look nicer, but just getting the data displayed correctly was quite simple! There\u2019s one more thing that might be useful here. If you are in Print Layout<\/em> mode and scroll, you\u2019ll see that the top headings do not carry over from page to page. (If you are seeing alternating blank pages, go back adjust the page margins and make sure that the report edge has been pulled to the left.) To get the headings to appear on the second and subsequent pages, go to the Tablix<\/em> properties and change the RepeatColumnHeaders<\/em> and RepeatRowHeaders<\/em> to True<\/em> as shown in Figure 20. Probably the easiest way to see the Tablix<\/em> properties is by selecting it from the list in the Properties<\/em> window.<\/p>\n
    (Note that this method to repeat headings doesn\u2019t work on regular table reports, and I\u2019ll explain how to do that later in the article.)<\/p>\n
    <\/p>\n
    Figure 20: The properties to get the matrix report headings to repeat<\/p>\n
    Now that you have seen how to create a matrix report, it\u2019s time to learn how to create a regular row-grouped report.<\/p>\n
    Creating a table report<\/h2>\n
    You saw a glimpse of row groups in the last section, but this time you will learn even more about row groups in reports as you create the report shown in Figure 1. To get started, create a new report in the project with the name Product Sales<\/em>. It will also point to the project\u2019s shared data source. Create a dataset using the same query that was used in the matrix report. Add a Table<\/em> control to the report canvas and fill in the Data<\/em> row, also known as the detail <\/em>group, as shown in Figure 21. The headings should fill in by themselves.<\/p>\n
    <\/p>\n
    Figure 21: The table with the detail row fields<\/p>\n
    The report is grouped by Category Sub Category OrderYear<\/em>, so OrderYear<\/em> is the first \u201cparent\u201d above the detail. In this type of report, I think it is easier to start at the detail and build out, but you can also go the other way. To add the group, right click the Details<\/em> group in the Row Groups<\/em> section. Select Add Group Parent Group\u2026<\/em> as shown in Figure 22.<\/p>\n
    <\/p>\n
    Figure 22: Adding a parent group<\/p>\n
    This will bring up the Tablix group dialog where you will select the group\u2019s field. Select OrderYear<\/em> since it is the direct parent of the detail row. Also check Add group footer<\/em>. This is where the subtotals will go. The dialog should look like Figure 23.<\/p>\n
    <\/p>\n
    Figure 23: The Tablix group dialog<\/p>\n
    After you click OK<\/em> to add the group, you\u2019ll see quite a few changes to the report canvas. The Order Year<\/em> group has been added to the report and to the Row Groups<\/em> section. Figure 24 shows the report canvas at this point.<\/p>\n
     Figure 24: The OrderYear group added to the report<\/p>\n
    The next parent level is SubCategory<\/em>. This time, I\u2019ll show you another way to add a group. Right-click on the OrderYear<\/em> cell and select Add Group Parent Group\u2026<\/em> as shown in Figure 25.<\/p>\n
    <\/p>\n
    Figure 25: Another way to add a parent group<\/p>\n
    Add SubCategory<\/em> as the new group and be sure to select Add group footer<\/em>. Repeat the process to add Category<\/em> using either method. Make sure to add the new group as a parent to SubCategory<\/em>. Once all the groups are added, the report canvas should look like Figure 26.<\/p>\n
    <\/p>\n
    Figure 26: All the groups added to the report<\/p>\n
    There are a lot of empty cells, and you can add totals to some of them. To figure out which grouping level a particular cell belongs to just select it. You\u2019ll see the grouping level light up in orange as shown in Figure 27.<\/p>\n
    <\/p>\n
    Figure 27: The grouping level lights up in orange<\/p>\n
    To add subtotal for each level, fill in SalesAmt<\/em> for each cell underneath the SalesAmt<\/em> cell. You can also add subtotals for Quantity<\/em>. They will automatically sum as shown in Figure 28.<\/p>\n
    <\/p>\n
    Figure 28: Adding subtotals<\/p>\n
    You can also add a grand total to the report by right-clicking the bottommost SalesAmt<\/em> cell and selecting Add Total<\/em> as shown in Figure 29.<\/p>\n
    <\/p>\n
    Figure 29: Adding a grand total<\/p>\n
    When adding a total in this way, it is automatically added to the next parent grouping level. In this case, Category<\/em> is at the top, so the total is added to the report level. You can add a grand total for Quantity<\/em>. In this case, just add Quantity<\/em> to the cell. The report design should look like Figure 30.<\/p>\n
    <\/p>\n
    Figure 30: Adding grand totals<\/p>\n
    Preview the report. The report should look something like Figure 31.<\/p>\n
    <\/p>\n
    Figure 31: The unformatted report<\/p>\n
    There is quite a bit of formatting that should be done, but I\u2019ll leave it up to you to decide what to do except to be sure to modify the report\u2019s margins so that all the fields fit on one page. After formatting, my report looks like Figure 32.<\/p>\n
    <\/p>\n
    Figure 32: Report with some formatting<\/p>\n
    If you take a close look, it\u2019s hard to tell what levels the total amounts refer to. To fix that, follow these steps. In the cell under ProductName<\/em>, add this expression to the Value <\/em>property:<\/p>\n
    =\"Total for \" & Fields!OrderYear.Value<\/pre>\n
    Holding down the Shift key, select the two cells in row 4 and columns 4 and 5. Right-click and select Merge Cells<\/em> as shown in Figure 33.<\/p>\n
    <\/p>\n
    Figure 33: Merging two cells<\/p>\n
    Add this expression to the new larger cell:<\/p>\n
    =\"Total for \" & Fields!SubCategory.Value<\/pre>\n
    Remove the word Total<\/em> from the cell to the left. Merge the cells in row 5 and columns 4 and 5. Add this formula:<\/p>\n
    =\"Total for \" & Fields!Category.Value<\/pre>\n
    Finally, merge the cells in the bottom row and columns 4 and 5. The cell should just say Grand Total<\/em>. Delete the original word Total<\/em> from several cells. The report design should look like Figure 34.<\/p>\n
    <\/p>\n
    Figure 34: The subtotals labeled<\/p>\n
    I would like to move some of the labels more to the left, but I found that the Merge<\/em> Cells option was not always available. If it is for you, then modify accordingly.<\/p>\n
    The last thing to do to make this report more usable is to have the headings repeat on new pages. If you go into Print Layout<\/em> mode and scroll to page 2, you will see that the top headings do not repeat. The RepeatRowHeaders<\/em> property doesn\u2019t seem to do anything on regular table reports. There is a way to do this, though.<\/p>\n
    On the grouping section at the bottom of the page, select Advanced Mode<\/em> as shown in Figure 35.<\/p>\n
    <\/p>\n
    Figure 35: Turning on Advanced Mode<\/p>\n
    Make sure this is checked which will add Static<\/em> sections to the row and column groups as shown in Figure 36.<\/p>\n
    <\/p>\n
    Figure 36: The Static groups<\/p>\n
    Working only on the row groups, select the first Static<\/em> group and open the Properties<\/em> window. Change the RepeatOnNewPage<\/em> property to True<\/em> as shown in Figure 37.<\/p>\n
    <\/p>\n
    Figure 37: The RepeatOnNewPage property<\/p>\n
    Repeat the process for the next two Static<\/em> groups. Now when you run the report and scroll to page 2, you\u2019ll see the top headings repeated. Figure 39 shows you page 2 of my report.<\/p>\n
    <\/p>\n
    Figure 39: The report with repeating row headings<\/p>\n
    Summary<\/h2>\n
    This article introduced adding grouping by walking you through the creation of two simple reports. This article covered a lot of ground, but there is still so much to learn. In the next article, you\u2019ll learn more about adding expressions to reports to add even more functionality.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    Most SSRS reports are organized into grouping levels where it\u2019s possible to add totals for each group.  In this article, Kathi Kellenberger continues her SSRS series by walking through how to create both a Matrix and Table report.  …<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[11292],"class_list":["post-85295","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85295","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\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=85295"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85295\/revisions"}],"predecessor-version":[{"id":85335,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85295\/revisions\/85335"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85295"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}