SSRS: Working with Tablixes
Introduction
We have all heard the phrase "think outside of the box". This is easier said than done. We all have our boxes that we think in but we just have differently sized (and shaped) boxes. It is a rare instant in time when we stand on our box and leap beyond our own knowledge, so the rest of the time we just get creative inside our box. Whether you are in your box or standing on it, the key is to expand the boundaries of your box with more tools and information that can then be combined in more and different ways, thus growing the box and extending your reach. A table control may be thought of as a box. Rather than thinking outside that box, we will see how we can expand it by getting creative inside it.
Overview
A table control in SSRS is one form of a tablix. SSRS: Converting Between Tablix Controls (Matrix, Table, List) describes the relationship of the three tablix controls in greater detail. Because of this relationship, the techniques described here for one of the tablix controls may be adapted for use with any other tablix control. It is also important to understand that SSRS tables, lists and matrixes have changed over time. Some of the information in this article may not apply to all versions of SSRS.
Discussion
We begin with our box...
This appears to be a simple control, easy to use and manage. While a tablix can be used as a simple control, its advanced capabilities allow it to become much more. By understanding this control, and others, you will expand the boundaries of your box and expand the capabilities of your reports.
Understanding Tables
In order to get creative with your box, it is important to understand its capabilities and characteristics. Understanding leads to innovation.
Composite Control
A tablix is a composite control. It is not just a tablix, but a tablix containing a textbox in each of its cells. It should be noted that while a table controls begins with a textbox in each cell and will automatically add a textbox into any new/empty cell, it is possible to insert any other control, including another tablix control (table, matrix, or list) into a table cell instead of a textbox. When you right-click a table cell, the context menu appears. The context menu will include a menu item called insert which will expand to reveal the controls that may be inserted into a cell.
Each of the 3 tablix controls has some unique features. A table has a header row, a matrix has column groups, and a list allows you to add multiple controls to its detail cell. Through understanding the composite nature of the tablix, we understand that this capability of the list is actually from the rectangle that is embedded in the list's detail cell by default. This understanding allows us to extend this unique and powerful capability of the list to a table, or even a matrix, simply by embedding a rectangle into a detail, header, or group cell.
Inheritance
Although a table is a composite control, it behaves as a single control in many ways. This is because child controls inherit from their parent table, effectively expanding the property set of those child controls. For instance, any control added to a table cell will inherit the dataset of the parent table, whether or not that control is normally data-driven. This is true for the textboxes added to a tablix cell by default as well as the rectangle in a list control. Filters applied in the table properties will also filter the data displayed in its child controls.
The properties of a child control may also transmit up to the parent tablix. An example is the textboxes embedded by default in a tablix cell. Textboxes have their CanGrow property set to true by default. When the contents of the textbox grows beyond what can be displayed, the textbox height grows. This causes the tablix row height to grow in order to accommodate its child control.
Docking
Embedded controls automatically dock to all four sides of the table cell they are added to. This is because they inherit their height and width properties from their parent table row and column.
Tablix Properties
The tablix controls have dozens of properties as do the other various controls that may be inserted. Some of these properties are read-only or may only be set to a static value while others may be calculated using the expression builder provided in SSRS design tools. Expressions greatly expand the capabilities of the control by adding a dynamic element. The expression builder is one of the most powerful capabilities in SSRS. It exposes a wide range of functions, fields (both built-in and dataset), parameters, variables, constants, and operators. It is through expression builder that custom code is executed.
As an example, the table control includes more than 50 properties that can be set using an expression, including 15 Border properties, 4 Background properties, 5 Font properties, Color (foreground), and more, all of which can be either set with a fixed value or dynamically using expression builder. This can be compared to the roughly 25 properties whose values' are static or read-only.
Other Characteristics
Tables in SSRS share many characteristics with their better-known cousins in Excel.
Cell Selection
Row selectors appear as grey boxes/buttons to the left of each row when a table cell is selected. Column selectors appear at the top of each column. The box/button in the top left corner is a "Select All" button. You can select multiple cells by shift-clicking, which includes the two cells clicked and all cells in between, or control-clicking which selects all cells that are clicked on. This capability allows the report designer to easily manage content and properties in multiple cells at once.
Cell Merge
This is another common table characteristic expected in Excel, HTML, and other tables that is also available in SSRS tables. Cell merges in SSRS tablixes are less flexible than Excel but still a valuable tool for controlling layout and presentation of your data. Simply select adjacent table cells in the same row, right-click and choose merge cells. You may also select Merge from the Home menu if working in Report Builder. Merging cells is context driven and may be unavailable if the selected cells are of different rows or scopes. For instance, cells of two different column groups cannot be merged even if they are on the same row nor can you merge a detail cell with an adjacent header cell in the same row.
Grouping
All types of Tablixes support row grouping while the matrix also supports column grouping. Grouping is a means of defining the scope of the associated data, effectively creating data areas within the tablix based on a subset of the assigned dataset. Column groups and row groups may be used together where each defines a different scope and a third, more restrictive scope is defined where the two intersect. Row and column groups may be created as a parent group, child group, or adjacent group. You can create as many groups using any combination of these group types. A parent group sets the scope for its child groups.
When a row group is created, a group header column is added to the left of the tablix detail cells. A new column group creates a new group header row above the detail cells. That header column can be deleted without deleting the associated group if you wish to.
Details Group
The Details group is a special group that is created in a table or list by default. It can be added to the matrix if desired by simply selecting an existing column or row group and adding a child or adjacent group. Then choose "Show detail data" instead of assigning a group by field or expression. A Details group cannot be added as a parent because Details groups must be the lowest level group in their hierarchy.
It is this group that causes the tablix controls to generate one row for each dataset record that is in scope. This behavior occurs whether or not any data is being displayed in the tablix. Deleting the Details group will eliminate this behavior. In a table with a single row group and no Details group, adding a field to a detail cell will work as if the First() aggregate function was used. The field value for the first record in the data scope will be displayed.
Expanding the Box
Below are examples of some tablix control extensions. Use these as a starting point to expand the box you think in and add your own extensions here as well. The more we share, the more we learn, the bigger our box becomes.
Embedded Tables and Controls
At right is an interesting table layout that is impossible to create with a single table. Note the top row of the table has four cells. The last three cells span the same distance as two of the cells on the second row. So far, this could be done with one table but the separation between the two cells below falls squarely in the center of one of the cells above. While it looks like one table, and will behave as one table, this is actually two tables. This configuration was created by merging the last two cells in the first row of the outer table and inserting a second table into the merged cells, and removing the header row from the embedded table. Because of inheritance, the embedded table will behave as an integral part of its parent.
Layout Table
Tables have long been used in design to manage page layout. Popularity of layout tables has declined but they can still be a useful tool. With multiple controls on a single design surface, like the report body or a rectangle, dynamic changes to the size of one of those controls (i.e. matrix) may have unpredictable effects on the positioning of nearby controls as it grows. A layout table provides very predictable behavior in these circumstances.
SSRS tablixes are not directly suited for use as a layout table because they are data-driven and so, require a dataset. If you wish to use a tablix to control the layout of static information not taken from a dataset then you will need to work around the required dataset since it carries with it a performance cost. One approach is to minimize that cost by creating a minimal dataset. For instance, the dataset might have a query like SELECT ''. Another approach is to simply use any existing dataset. You are, after all, writing a report so you must be displaying some data already. By using an existing dataset, you incur no additional performance cost and even though the tablix has a dataset, it doesn't mean you have to use it. Next, hide the evidence of the table by setting BorderStyle to None or BorderColor to match the background color on the tablix and all embedded controls (textboxes, etc.). The last thing to do is delete the Details group from any tablixes used in the layout so it does not automatically generate additional, unwanted rows.
Rectangle
The docking characteristic of controls added to a tablix cell may have undesirable effects. When the contents of a table cell textbox causes it to grow, the height of the entire row is affected. This can result in unwanted distortion of pictures, charts, indicators, and other controls contained in other cells of the row. A simple solution to this problem is to add a rectangle to the cell first, then add the picture, chart, etc. to the rectangle. If the row grows, the rectangle will grow because it is docked to all four sides of the tablix cell but the controls within the rectangle will not since they are not docked.
Summary
By understanding the characteristics of the tablix controls, a report designer can create some interesting extensions, using it in ways that may not have been previously considered.