Basics of SSRS Tablix Data Region

A tablix report item summarizes business data and displays the result in a flat or cross-table format. Cross-table format is widely used by analysts in financial domains to organize and analyze large data sets. It allows the creation of multidimensional views for analyzing and satisfying the needs of business users. In SSRS RDL standard, “tablix” is a generalized term that means the combination of table, matrix (cross-table or pivot table), and list report items (table+list+matrix=tablix). It displays report data from a data set in cells that are organized into rows and columns. In this article, we will discuss the basic behavior of tables, matrices, and lists and their key features.

Table

A table can be used to display detailed data or grouped data. A simple table design contains a table header row and a details row with three columns. A table can have only row groups. By default, each cell in a table contains a text box.

Matrix

A matrix can be used to display summarized data. It can have row groups and column groups. A simple matrix design contains a row group, a column group, a corner cell, and a data cell. By default, each cell in a matrix contains a text box.

List

A list report item can be used to create free-form layouts. By default, a cell in a list contains a rectangle. It acts as a container to place multiple report items side by side to design a free-form layout.

Tablix sections

A tablix data region area can be classified into four sections. The following screenshot shows the areas for a tablix region with nested row groups and column groups.

Sections:

• Corner cell: Used for displaying static information. It is created when the tablix data region has both row groups and column groups.
• Row group: Used to group report data vertically. These can be arranged either hierarchically or adjacent to each other.
• Column group: Used to group report data horizontally. These can be arranged either hierarchically or adjacent to each other.
• Body cells: For displaying aggregated data with respect to row-grouped and column- grouped data in the report.

The tablix data region must contain a body section, but the other sections are optional.

Cells, rows, and columns

The tablix data region comprises cells that are organized as rows and columns. A tablix cell contains the actual content to be presented by the tablix data region. Each tablix cell can contain any report item, including a text box, an image, or another data region such as a chart, a map, a tablix region, or a gauge. Refer to the cell properties documentation and learn how to handle report items within tablix cells.

The tablix data region includes two types of rows and columns: Static and Dynamic. The static row or column is used to display labels and totals. At runtime, the static row or column renders only once in the tablix data region. On the other hand, the dynamic row or column is created when we add row or column groups in the tablix data region. It displays unique values for every group that repeat in the tablix data region.

The body and corner area contain only static cells. The row and column group area contain both static and dynamic cells. Refer to the insert or delete a row and insert or delete a column documentation to learn how to add or delete rows and columns in a tablix data region.

Visual cues

Visual cues on a tablix data region will assist you to work with the tablix areas to showcase the information in an efficient manner. When you select a tablix cell, row and column grippers and group indicators will show the groups to which the cell belongs. The following screenshot shows a matrix with both row and column groups, and a total row and a total column.

Groups

We can add groups in the tablix to display data in a visual hierarchy. In a tablix data region, we can classify the groups as a details group, row group, column group, and hierarchy groups. The tablix data region displays detail data on detail rows and columns and grouped data on group rows and group columns. You can create both nested groups and adjacent groups.

To build grouping structure in the tablix data region, we can use the grouping panel or cell context menu. Refer to the details group, row group, and column group documentation to create, edit, and delete groups in tablix data regions.

We can also add headers and footers for each group in the tablix data region. Refer to the add group header and footer documentation.

Total

We can use the Add Total option to display the sum of numeric values for tablix groups or the whole tablix data region. You can add a row to display totals for columns and a column to display totals for rows. Refer to the Add Total documentation to learn the step-by-step procedure to add totals in a tablix data region.

Key features

A tablix data region provides basic functionalities like sorting, filtering, and grouping, but the following key features distinguish the tablix data region as an efficient widget for advanced scenarios:

• Interactive sorting by value columns.
• Displaying no data items.
• Conditionally hiding data.
• Merging and splitting cells.
• Conditionally formatting values with color and text styles.
• Totaling values.
• Hierarchical grouping by rows and columns.
• Linking with relevant page URLs.

Simple table design

Now, I will show you the steps to design a simple report using the table report item. This will provide some basic knowledge for using the tablix report item in 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.

Create data set

To present data in a tabular format, create a data set and bind the data to the tablix data region. In this designing section, the following data set query is used for data set creation.

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

This query runs against the AdventureWorks2012 database. Refer to the Query Builder Overview blog and data set creation documentation, then create a data set in the report using the previous query.

Add table to the 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, the table renders with two rows and three columns in the design area. Respective table properties will be listed in the properties panel.

Insert column

In this report, I want to display four columns from the data set in tabular format. So, let’s add one more column in the existing table structure.

1. Select a table cell and right-click to open the cell context menu.

   

2. Choose Insert Column–>Left.

   

Now, the new column will be added to the table data region.

Refer to the insert or delete column documentation for more information on inserting or deleting columns in a table.

Assign data

1. Select the table cell and click on the data assign menu icon to open data assign menu.

   

2. Click on the required data field name in the menu.

   

Similarly, you can assign data fields in other cells.

You can assign data to the table in a few other ways. Refer to the assign data to tablix documentation to learn more.

Set header text

We can add column header text for each column in the table by following these steps:

Select a cell in the table. The text box properties will be listed in the properties panel.

In the Content property, type the header text in the content property text box and press Enter.

Now, the text will be displayed in the cell like in the following image.

Similarly assign header text for other columns.

Resize the column

To improve the report readability, we can resize the table row height and column width.

Place the mouse pointer on the column border you want to move.

Drag the column gripper horizontally to adjust the column width.

Resize the row

Place the mouse pointer on the row border you want to move.

Drag the row gripper vertically to adjust the row height.

Format table

We can format a table to enhance the visual appearance of the tablix data region in the report design. Formatting includes changing the font style, font color, font family, font size, font weight, and background color of table cells:

1. Select a cell or multiple cells and open the properties pane.
2. Under the Basic Settings category, set the font family to SegoeUI, font color to White, and font weight to Semibold.
3. Set the background color to #339898 under the Appearance category.
4. Under the Alignment category, set the text alignment to Center.

In the table design, we have a sales column. Usually, displaying information directly from the database doesn’t result in it being properly formatted. So let’s apply some formatting for the sales field. Select the Sales data cell.

Under the Basic Settings category, enter the ‘$’#,0.00;(‘$’#,0.00) format in the format property.

Under the Alignment category, set the Text Alignment to ‘Right’.

Here, I have applied some basic formatting to the table. Refer to the tablix report item documentation to explore more about tablix data regions. This way, you can modify a tablix data region to create minimal complex to more complex tabular structures in your reports.

We can adjust the design surface width and height to make the report design look better, too.

Preview report

You can preview the report using the built-in Report Viewer. The tablix data region displays the product names with their categories and the sales of the products over years in tabular format.

Conclusion

I hope this blog provided a clear overview of tablix data regions and how to design and display data in a tablix using Bold Reports Designer. You can download this report design from here. To explore further, go through our sample reports and Bold Reports documentation.

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.