Add Grouping and Totals in Tablix Data Regions

In an earlier blog post, we learned the basic structures of tables, lists, and matrices; the sections of a tablix data region; and visual cues. We had a slight introduction to groups and totals and learned how to design a simple table report in the Bold Reports Designer. In data reporting, we can do much more with the tablix data region. For example, an employee hierarchy, multinational geographic groupings, a chart of accounts, and complex product portfolios can be naturally organized and presented in a hierarchical structure using tablix. Now, let’s see how to add grouping levels and totals in a tablix data region to design a hierarchical business report using Bold Reports Designer.

As a first step, launch the Bold Reports On-Premise server demo site, referring to the steps to create a report, and create a new report.

Before organizing data in tablix, we must create a data set that contains the required information. To showcase grouping levels and totals, I am going to design a table report. It will display the total yearly sales of each product in a category, total yearly sales of each category, and total yearly sales of overall product categories. So, the data set must contain information like product details and sales amounts for each quarter in a year.

Create data set

The following query runs against the AdventureWorks2012 database. It retrieves the product category, subcategory, order year, quarter, and its sales amount data from the database. Refer to the Query Builder Overview blog and data set creation documentation, then create a data set in the report using the following query.

SELECT Top 50 PC.Name AS ProdCat, PS.Name AS SubCat, DATEPART(yy, SOH.OrderDate) AS OrderYear, 'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales FROM Production.ProductSubcategory PS INNER JOINSales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

Add table to report

The table report item is listed in the item panel under Data Regions.

Drag the table report item into the design area from the item panel.

Once you drop the table item, it renders with two rows and three columns in the design area. Respective table properties will be listed in the properties panel.

Assign data set

To assign a data set, select the table report item and set the data set name to the Dataset property in the properties panel.

Add grouping

We can classify the groups as details group, row group, column group, and hierarchy groups. A details group is used to display the detailed data and it has no group expression. By default, each tablix structure has a details group. Row grouping is used to group report data in the vertical direction and column grouping is used to group report data in the horizontal direction. Both contain a group expression, and these can be arranged either hierarchically or adjacent to each other. The data sets grouped in nested levels are called hierarchy groups. For this report design, I am going to group the OrderQtr and Sales data fields with OrderYear, ProdCat, and SubCat data fields vertically. Thus, grouping will go like the following structure.

In Bold Reports Designer user interface, you can add groupings and totals in tablix data regions using the grouping panel or tablix cell menu. I am going to add groups using the grouping panel. Refer to the Grouping Panel documentation to understand the following designing steps.

First let’s assign OrderQtr and Sales data fields in the table design like in the following image.

Now, we can start grouping the OrderQtr and Sales data fields with OrderYear, ProdCat, and SubCat data fields in the tablix data region.

Add row groups

To create the grouping levels explained earlier, first we need to create a parent group and then create child groups. Go to the Row Groups pane in the grouping panel and open the context menu on the Details group field.

From the context menu, click on Parent Group… option under Add Group category.

In the Tablix Group dialog, select the OrderYear field in the Group by drop-down list and click OK.

Then, open the group menu in the OrderYear1 group field. Click the Child Group… option under the Add Group category.

In the Tablix Group dialog, select the ProdCat field in the Group by drop-down list and click OK.

Then, open the group menu in the ProdCat1 group field. Click the Child Group… option in the Add Group category.

In the Tablix Group dialog, select the SubCat field in the Group by drop-down list and click OK.

The table design with grouping structure will look like the following.

Delete the unused rows and columns from the table design. Refer to the Tablix Group, Insert or Delete Row Group, Insert or Delete Column Group, Insert or Delete Row, and Insert or Delete Column documentation to learn more about handling rows and columns in a tablix data region.

Add total

Now, a table report has been created with row groups. The next step is to add some totals to the report. We can use the Add Total option to display the sum of numeric values for a tablix group or an overall tablix data region.

Total yearly sales of each product in a category

To add a total for each product in a category, right-click in the [Sales] cell to open the cell menu. Select Add Total in the menu.

This action will add an adjacent new row to the Details group.

Select the third row, fourth column cell and enter = “Total yearly sales of ” & Fields!SubCat.Value expression. Now, the table design will look like the following.

Switch to the preview mode. A new row will display the total sales of each product in a category.

Total yearly sales of each category

Now let’s calculate the sum of yearly sales of each category. Right-click in the [SubCat] cell in the data region and select Add Total–>After.

Now, another new row will be added inside of the ProdCat group in the data region, as shown in the following.

Remove the [Sum(OrderQtr)] expression from the fourth cell. Edit the fourth row, third column cell, which contains Total text, and set the following expression = “Total yearly sales of ” & Fields!ProdCat.Value.

Switch to the preview mode. A new row will display the total sales of category.

Adding grand total

To calculate the grand total of overall product sales, right-click in the [ProdCat] cell in the data region and select Add Total–>After.

The previous action will add an adjacent new row to the OrderYear group,

Remove the [Sum(OrderQtr)] expression from the fourth cell. Modify the text to “Grand Total” in the last row, second column.

Merge cells

We can span or split cells in the tablix data region to improve the table design or to span labels. In the grand total row, we can span the first two cells with the following steps.

Select the first two cells in the grand total row and right-click in the cell.

Click on Merge Cells option in the menu.

Switch to the preview mode. The overall sales of products for each year will be displayed.

Format data in table

By default, the summary data for the Sales field displays a general number. To display the numbers as currency, set the currency formatting for Sales field cells using the Format property.

Format appearance of table

Now, let’s add some formatting in the tablix data region to improve the look and feel of the report. Change the table header text to Year, Category, SubCategory, Quarter, and Sales. In the following design, I have added a header to the report and applied a font color, a font family, alignments, and a background color. I’ve also made some adjustments in the width and height of the cells in the tablix data region. Refer to the tablix report item documentation to explore tablix data regions more.

Preview the report at this point.

Page breaks and keep together

You may want to view or print the sales details of each year in a single page. In this scenario, we can make use of page breaks. Adding page breaks in tablix groups will break each group instance into separate pages based on the Break Location property. To display the sales details of each year in a single page, select the OrderYear member in the grouping panel and open the properties panel. In the Page Break category, set the Break Location property to Between.

Sometimes, each instance of group data may expand to multiple pages. To control displaying group data together, set the KeepTogether to true for groups in the tablix data region. In the Miscellaneous category, enable the Keep Together property check box for the OrderYear group.

Now preview the report. The sales details for each year will be displayed in separate pages.

Repeat on new page

Navigate to the second page of the report and notice the table header is missing. To repeat the headers on every page of the report, follow these steps.

Refer to the Advanced Mode documentation and enable advanced mode in the grouping panel.

Once you click Advanced Mode, it will show the static columns in both row and column groups. Now, select the static column presented in the row group pane (Header Row Group) and open the properties panel.

Under the Miscellaneous category, enable the Repeat On NewPage property check box and set Keep With Group as After.

Now, preview the report. The header rows will show up on every page.

Conclusion

I hope this blog provided a basic idea about grouping and totals in tablix data regions using Bold Reports Designer. To explore further, go through our sample reports and Bold Reports documentation. I have also attached the generated .rdl file for further reference.

If you have any questions, please post them in the comments section. You can also contact us through our contact page or, if you already have an account, you can log in to submit your support question.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official Twitter, Facebook, LinkedIn, Pinterest, and Instagram pages for announcements about upcoming releases.