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.
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
- Refer to the Simple Table Design section from this blog and design a simple table.
- Click on the table surface to enable the grouping panel in the design view.
- Go to the Row Groups pane in grouping panel and open the context menu in the Details group field.
- From the context menu, click on the Parent Group option in the Add Group category.
- 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
- Choose data set field in the Group by drop-down list.
- 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
- 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 =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.
In the report preview, the header and footer will be added for each group.
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!