2 3

Added by Peter Avila October 11, 2013

There are two ways you can format grouped data in a table data region in SSRS: You can use the stepped format or the block format. I’ll show you an example of each, how to create them, and then how to convert a group from one format to the other. I’ll also introduce group columns, show you how the presence of a second group column can have an undesired side-effect when converting from block format to stepped, and then show you how to remove the side-effect. To close out, I’ll show you a false solution to the second group column issue that you will want to avoid.

Let’s get to know the two formats, first. From a design perspective, the difference between the two is very simple: The group header row is present in the stepped format and absent in the block format. Here’s a report using the stepped format. Notice that the group header row, though empty, is present. Also notice that the detail rows start below the group header row, creating a “step” from the header row to the first detail row.

Now here’s the same report in block format. Notice that there is no group header row, and so the detail rows can occupy its space, eliminating the “step” and creating a “block” effect:

Unless you are including data in the group header row (we see a good example of this in the BI795 course when doing drill-down), you will probably want to use the block format.

Those are the two formats. Let’s have a look at how to create each one and then how to convert a group from one format to the other.

Create a Group in Stepped Format

To use the stepped format when creating a group, just select the Add group header option.

Create a Group in Block Format

To use the block format when creating a group, just leave the Add group header option unselected.

Converting from Stepped to Block

Converting a group from stepped format to block format just means deleting the group header row. Right-click in any textbox in the group header row and select Delete Rows.

Convert from Block to Stepped

Converting a group from block format to stepped format means inserting a group header row. If your report has more than one group column, you’ll have to make an additional adjustment. Group columns are the ones to the left of the double-dotted vertical border.

To insert the new group header row, right click in any group column textbox and select Insert Rows and then Inside Group – Above. The figure below shows a right-click on the Order Date column textbox, but we could have just as well selected the textbox for the SalesOrderNumber column. The results would have been identical.

Notice a couple of things. First, we can confirm that the new row is a group header row and not, say, a second detail row; the row selector symbol on the left side of the table tells us that. Second, it looks like the Order Date has been pushed down a row!

What’s going on? SSRS always creates the header row all the way back to the first group column regardless of how many other group columns there might be; it just pushes those other columns down and out of the way. The remedy to this side-effect is a bit clunky. Fortunately, it’s also very simple. We’ll just need to merge the two textboxes in the Order Date column and then re-link the resulting textbox to the data.

To merge the two textboxes, select both together, right-click in the selection, and then select Merge Cells.

To re-link the textbox to the data, hover over the resulting textbox until you see a small, list icon in the upper right corner of the textbox. Click that icon and select the column you want—in this case, Order Date.

Fixed!

And you’re back in business!

A Word of Caution

When converting from block format to stepped format, you might discover that if you insert the new row from one of the detail textboxes instead, the Order Date doesn’t get pushed down. This might seem like an easier solution at first, but it isn’t!

While it’s true that the Order Date does not get pushed down, have a look at the row selector symbols on the left side of the table.

We just inserted a new detail row and not the group header row that the stepped format requires. And it makes perfect sense when you think about; we inserted from a detail textbox, and we selected “Inside Group.” In other words, we were in a detail group so we got a new detail row.

After formatting the Order Date textbox to “d” for small date, the report looks like this, clearly not the desired format in this case:

The correct way to remove the side-effect is to insert using the group column textboxes as shown above.

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ