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?
To add an adjacent row or column group to a tablix data region. In the Grouping pane, right-click a group that is a peer to the group that you want to add. Click Add Group, and then click Adjacent Before or Adjacent After to specify where to add the group. The Tablix Group dialog box opens.
To display column headers on multiple pagesRight-click the row, column, or corner handle of a tablix data region, and then click Tablix Properties. In Column Headers, select Repeat header columns on each page. Select OK.
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
.
<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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With