Live Chat Icon For mobile
Live Chat Icon

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.

New report
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.

Table listed in in item panel
Table listed in in item panel

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

Add table to the design surface
Add table to the design surface

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.

Basic table structure
Basic table structure

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.

Assign data
Assign data

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.

Row Groups
Parent group | Order Year

       Child group | Product Category

              Child group | Subcategory

                      Details Group | OrderQtr | Sales

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.

Assign data
Assign data fields in table cell

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.

Assign data
Grouping menu

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

Create OrderYear group
Create OrderYear group

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

Assign data
Create OrderYear group

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

Assign data
Child grouping menu

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

Assign data
Create ProdCat group

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

Assign data
Child grouping menu

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

Assign data
Create SubCat group

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

Assign data
Table design with grouping structure

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.

Assign data
Adding subtotal in table

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

Assign data
Table design with subtotal

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.

Assign data
Set label text for subtotal

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

Assign data
Table preview with subtotal

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.

Assign data
Add total for product category

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

Assign data
Table design with total row

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.

Assign data
Set label text for total row

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

Assign data
Preview displays total for each product

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.

Assign data
Add grand total in table

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

Assign data
Table structure with grand total

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

Assign data
Set label text for grand total

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.

Assign data
Merge cells in table

Click on Merge Cells option in the menu.

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

Assign data
Preview displays grand total

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.

 Assign data

Currency formatting

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.

Formatted table design view
Formatted table design view

Preview the report at this point.

Formatted table preview
Formatted table preview

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.

Set page break in group
Set page break in group

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.

Table preview with page break
Table preview with page break

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.

Assign data
Enable advanced mode

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.

Open table member properties
Open table member properties

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

Assign data
Enable repeat on new page

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 TwitterFacebookLinkedInPinterest, and Instagram pages for announcements about upcoming releases.

Leave a Reply