Add Headers and Footers with Tablix Groups

In an earlier blog post, we learned how to add grouping levels and totals in a tablix data region to design a hierarchical business report. Now, let’s see how to add headers and footers with a tablix group. The header and footer in tablix data regions are called static rows or columns. Each group is called dynamic rows or columns. The group header and footer repeat once for each group instance.

As a first step, launch the Bold Reports Enterprise 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 the steps involved in adding a header and footer with tablix group, I am going to design a table report. It will display each product name under a category as the header of each group and total yearly sales of each product under a category as the footer of each group. 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 JOIN
Sales.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 group header and footer

1. Refer to the Simple Table Design section from this blog and design a simple table.

   

2. Click on the table surface to enable the grouping panel in the design view.

   

3. Go to the Row Groups pane in grouping panel and open the context menu in the Details group field.

   

4. From the context menu, click on the Parent Group option in the Add Group category.

   

5. Once you click on the Parent Group option, a Tablix Group dialog will open to configure the grouping.

   

   • • Group by: Based on the data set assigned to the tablix region, data set fields will be listed in this drop-down. Click on the square icon to create an expression.
     • Add header: Enable this option to add a header to this group
     • Add footer: Enable this option to add a footer to this group
6. Choose data set field in the Group by drop-down list.
7. Select Add header to add a header row to the group and select Add footer to add a footer row to the group. Click on the OK button.

   

Now, a static row will be added above and below the group in the tablix data region.

In report preview, the header and footer will be added for each group.

Format header and footer

You can display data, label content, or total in the group header and footer of the tablix data region.

Merge header cells

Select the header cells and right-click in the cell. Then, click  Merge Cells

Now, set data or label content and format the header cell as required.

Merge footer cells

Select the first two footer cells and right-click in the cell. Then, click  Merge Cells.

Now, set data or label content and format the footer cell as required.

Edit footer cell content

1. Edit the footer cell that was merged in the previous step and set the expression  = “Total yearly sales of ” & Fields!ProdCat.Value using the expression builder. Set the font weight property to as Bold in the properties panel.

   

2. Set =Sum(Fields!Sales.Value) expression in the last cell to calculate the summary of the sales field. Set the font weight property to Bold and align to Left in the properties panel.

   

Edit header cell content

Edit the header cell and set the = “Sales Report of ” & Fields!ProdCat.Value & ” Category” expression using the expression builder. Set the font weight property to Bold in the properties panel.

To improve the look and feel of the report, apply required font properties, and border and currency formatting for sales and sum of sales field.

Report preview

In the report preview, the header and footer will be added for each group.

Conclusion

I hope this blog provided a clear idea on how to add a header and footer to tablix groups using the 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.