Is there an easy way to rearrange tablix columns in SSRS design mode?
There is a way to move columns through the designer:
- insert the number of blank columns you want to move in your destination spot
- shift-left-click on the cells (NOT the header column) you want to move
- right-click and select the Cut command
- right-click on top of the destination columns and select Paste
- delete the now empty old columns
If you can read XML ( just understand where tags start and or end etc), you can easily accomplish the task. You may take the following series of steps:
- First of all backup the original report by copying it to another file.
- Right Click on your report in Solution Explorer and select "View Code"
- This opens up the RDL of the report --- don't get scared it is just a simple xml file
- Now locate within the RDL file the "Tablix1" tag -- look for
<Tablix Name="Tablix1"> ....</Tablix >
- You now need to look for different "
<Textbox Name="...">...</Texbox>
" tags nested within the<TablixCells><TablixCell><CellContents>....
tags - Now you can easily rearrange the columns of the report by simply rearranging the order of these
<Textbox...>...</Texbox>
and you will have the new report with new column ordering.
Actually, you need to move (cut and paste) the entire <TablixCell>
element for the column (everything between the <TablixCell>
and </TablixCell>
including the <TablixCell>
and </TablixCell>
tags themselves).
For instance, to rearrange the columns in the example below to make the "Product ID" column come before the "Product Name" column, you would select and cut the entire section around the "ProductName" cell element (everything from the first <TablixCell>
to the first </TablixCell>
) and then paste it after the </TablixCell>
for the "ProductID" column.
Note that there is a complete set of <TablixCell>
elements for each row defined in the Tablix; each one is in a separate <TablixRow>
element. If you left the default header column (where the column names are set), then the first <TablixRow>
defines that header row and the second one defines the data in the columns and it is the one you want to edit. Once you have rearranged the data columns, you will either need to do the same thing for the header column (if you have it) or, just rename the columns using the designer to match the data now in the columns.
Really though, this is so convoluted that it's probably easier to move a column by just using the designer to insert a new column where you want the column moved to, set it with the proper data source for that column, and then delete the original column. For the example below, you would insert a new column after Product ID, set it to the ProductName data source column (which would set it "Product Name" in the header row), and then delete the original Product Name column on the left.
...
<TablixCell>
<CellContents>
<Textbox Name="ProductName">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!ProductName.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>ProductName</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="ProductID">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!ProductID.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
...
after the cut/paste, you would then end up with:
...
<TablixCell>
<CellContents>
<Textbox Name="ProductID">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!ProductID.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="ProductName">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!ProductName.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>ProductName</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
...
Another note on working in the RDL:
If you get it wrong the report will display an error message and it will not display the data.
Unless you are familiar with RDL (Report Definition Language, a type of XML) these types of errors can be very frustrating to deal with sometimes rendering the report un-usable.
It is far safer to use the add new columns and delete the old ones method in the designer, mentioned above. This keeps you out of the RDL reducing your chances of damaging the report.