How to add row group headers to a "headerless" style report?

Question

For the following Query

SELECT *
FROM (VALUES ('England','London',9787426),
             ('England','Manchester',2553379),
             ('Scotland','Edinburgh',452194),
             ('Scotland','Glasgow',1168270),
             ('Wales','Cardiff',447287)) V(Country, City, Population)

Creating a report from the Report Wizard (with the following options)

constructs a report like

(Which Robert Bruckner calls "headerless" here)

When confronted with such a report structure is there any simple/non flaky way to convert it to the format below (with the Country header outside of the tablix body)?

i.e. without needing to delete the existing group, then re-adding it?

Pagan

I do not sure is this is available in the Report Builder but it looks like you want to group by country and City and add FIRST(City) and MAX(Population) as the detail fields – Arnett

@Irb It isn't clear to me how that would help. Can you elaborate? In case my question wasn't clear I am just asking how to go from No cells merged vertically to cells merged vertically without deleting and adding back in the original grouping on Country. (or whether this is simply not possible - at least without manually editing the RDL) – Pagan

Answer 1

Method 0:
If there is an existing table (or using wizard), there is no better way to do it than the one you explained. By deleting and readding the group.

Method 1:
You can design the tablix from scratch in report designer. Add the tablix, set the dataset and add the group.

Method 2:
The other way to do is modify the xml code(RDL).

Cleanup for your design: Remove the second row and add Country data element to detail row, so your design look something like this without the vertical merge and dotted lines.

To add group headers the way you want via the xml code you need to move the TablixCell from TablixRow to TablixRowHierarchy.:

• Cut the TablixCell fragment under TablixRow->TablixCells containting your country data and keep it in seperate notepad/document.

• Delete the corresponding TablixColumn under TablixColumns

• RenameTablixCell Node to TablixHeader

• Add the <Size>1in</Size> fragment between TablixHeader and CellContents

• Paste the TablixHeader fragment under TablixRowHierarchy -> TablixMembers -> TablixMember in between SortExpression and TablixMembers.

• Remove one of the <TablixMember /> fragment under the TablixColumnHierarchy. It should match the number of column in the report except the columns before the dotted lines (or before the dotted group).

TablixHeader element defines the header for the group.

TablixCells element defines the list of cells in a row of the body section of a Tablix

Here is the link to RDL specifications. It doesn't have 2012 version but the 2008 version still looks good.

Here is the schema diagram of Tablix from RDL specifications. When your data is in the TablixHeader then the columns will be in vertical merge and with the dotted lines outside the tablix row. When it is in the TablixCells it is part of TablixRow.

Fasciate

Answer 2

There doesn't seem to be an easy way to add the category columns back.

The easiest way I know of is to add a parent group for the same field to the row group that you want the column back for. This will create a new category column and you can delete the old grouping. Just only delete the group, not the related cells & columns.

In your case you would add a parent group on the Country field to the existing Country grouping. You should now have your category column for country back and you can delete the original Country grouping.

Vesuvius

OK, thanks. Useful link. It seemed like I might be missing some more obvious option. Reassuring that this isn't the case then! I won't mark this as answered for a while yet in case there are alternative approaches. A disadvantage of this approach is that any custom group properties on the original group will need to be re-applied on the newly created group. I suspect this might be unavoidable though. – Pagan

You could try removing the newly created group instead of the old one, but not removing the related columns. This will leave the category column but the grouping on it may not work as expected anymore. – Vesuvius

I did try that and you're right it doesn't work as hoped for. The country column just shows "England" for my example data with all cities shown under that group. – Pagan

Answer 3

I would follow these steps:

1. Under "Row Groups" right-click the table1_Details_Group entry and choose Add Group / Parent Group
2. For Group By, choose Country. Click OK
3. Under "Row Groups" right-click the new Country entry and choose Delete Group
4. Choose Delete Group Only. Click OK
5. Right Click the original Country column and choose Delete Columns
6. Right Click the Country Group row and choose Delete Rows

Here's the final output:

Francenefrances

Answer 4

An easy way to add a header is to Add Total -> Before

This will add a new row before the group details.

Micra