SSRS: The Devil is in the Details
Introduction
There are two distinct types of groups in SSRS tablix data regions. The first might be called "Standard" groups. These groups pool dataset records together, allowing you to aggregate other fields (not the group by field) in the group. Aggregation like SUMming quantitative fields is common but SSRS allows for various aggregates. The second type of groups might be called a "Details" group, from the name of the default group added to the Table control. The Details group behaves quite differently from the Standard group. The Details group is often not fully understood. It is this group that we will discuss here.
Overview
The difference between a Details group and a Standard group is the "Group by" expression, either a field or an expression. A Details group does not have one. Adding a "Group by" clause to a Details group turns it into a Standard group. Likewise, removing the "Group by" expression from a Standard group changes it into a Details group. Note that the entire expression must be removed. Blanking the expression results in an error.
Details groups have a number of distinctive characteristics that differentiate them from a Standard group.
- Details groups do not group like data, they segregate individual records into separate rows.
- Details groups may not have any child groups.
- A tablix may include multiple Details groups.
NOTE: Because of characteristic 2, each Details group must be in a separate branch of the group hierarchy.
Creating a Details Group
Converting between a Details group and a Standard group is pretty simple and is described in the overview above. Creating a Details group is simple as well. Open the Tablix group dialog, select the "Show detail data" radio button, then click OK. Done.
There are several ways to open the Tablix group dialog. If you have an existing group you can click the down-arrow for the group and choose "Add Group". Whether you have an existing group or not, you can right-click a cell in the tablix and choose "Add Group". In either case the menus are context driven and the method used to open the menu will determine which options are available. Likewise, the specific option chosen to open the dialog determines whether "Show detail data" is available. Since Details groups cannot have any child groups, "Show detail data" will not be available in the dialog box if you opened it by choosing Add Group > Parent group. Since the new group is a parent, it will have a child and so cannot be a Details group.
Hands-On Details
It is the Details group that is responsible for a key behavior of a tablix, one row per record. Try this... Create a new report and add a data source. Then create a dataset from that data source that returns several columns. Add a table control to the report body and set each of the three default data cells to one of the returned fields in your dataset. Now run the report. What do you see? While in the designer, your table had only two rows, header row and data row, the report displays one header but as many data rows as you have rows returned by the dataset. You probably knew that would happen. It is expected behavior. The Details group made that happen.
Don't believe me? Switch back to the designer. Making no other changes, select any cell in the table to show the groups. Now click the down-arrow next to the Details group and choose Delete Group. When prompted, choose to delete only the group, not the associated rows. Run the report again. What do you see now? The table looks a lot like it did in design view, one header row and one data row.
Summary
Understanding the way groups operate, and specifically the Details group, will give you greater flexibility and capability displaying data in your reports. Play around with groups in your reports to better understand how they work. Too many times we are baffled by the behavior of tools because we either don't understand or misunderstand how they should work.
See Also
You can find more information on SSRS groups and the Details group in the TechNet library: