Live Chat Icon For mobile
Live Chat Icon

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.

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

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

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

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

    Add parent group
    Add parent group
  5. Once you click on the Parent Group option, a Tablix Group dialog will open to configure the grouping.
     Group dialog
    Group dialog
      • 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.

    Group dialog
    Enable header and footer for group

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

Design with header and footer
Design with header and footer

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

Header and footer in preview
Header and footer in preview

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

Merge cells
Merge cells

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

"<yoastmark
Header cells are merged

Merge footer cells

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

Merge cells
Merge Cells

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

"<yoastmark
Footer cells are merged

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.

    Set label for footer cell
    Set label for footer cell
  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.

    Display sum of sales in footer
    Display sum of sales in footer

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.

Set label for header
Set label for header

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.

Each product group with header and footer
Each product group with header and footer

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

Leave a Reply