Is there an easy way to reorder table columns in SSRS design mode?

Question

I have an SSRS report containing more than 20 columns in a table. Our users decided that the data was fine, but they want the columns to move (sigh!).

It seems that you need to easily rebuild the columns (move column 3 to column 1, swap columns 4 and 5, etc.). But drag and drop does not work, and the only solution seems to remove the original column and reinsert it in the right place (and reapply any expressions and formatting already created for the column).

Is there an easier way to do this? Please note that I do not want a software solution, but just need to change it once in development mode.

Answer 1

There is a way to move columns through the constructor:

1. enter the number of empty columns you want to move to the destination
2. shift-left-click on the cells (NOT in the header column) that you want to move
3. right click and select cut
4. right-click at the top of the destination columns and select Paste
5. delete now empty old columns

Answer 2

If you can read XML (just understand where tags start and end, etc.), you can easily complete the task. You can take the following steps:

1. First of all, back up the original report by copying it to another file.
2. Right-click your report in Solution Explorer and select View Code.
3. This opens the RDL report - don't be alarmed, it's just a simple XML file
4. Now find the tag "Tablix1" in the RDL file - find <Tablix Name="Tablix1">....</Tablix >
5. Now you need to look for different tags " <Textbox Name="...">...</Texbox> " nested in the <TablixCells><TablixCell><CellContents>....
6. Now you can easily change the column order of the report by simply changing the order of these <Textbox...>...</Texbox> and you will get a new report with a new column order.

Answer 3

In fact, you need to move (cut and paste) the entire <TablixCell> element for the column (everything between <TablixCell> and </TablixCell> , including the <TablixCell> and </TablixCell> ).

For example, to reorder the columns in the example below so that the Product Identifier column comes in the Product Name column, you must select and cut the entire section around the "ProductName" "cell element (everything from the first <TablixCell> to the first </TablixCell> ), and then insert after </TablixCell> for the "ProductID" column.
Note that there is a complete set of <TablixCell> elements for each row defined in Tablix; each of them is in a separate <TablixRow> element. If you left the default header column (where the column names are set), then the first <TablixRow> defines this header row, and the second defines the data in the columns, and this is the one you want to edit. After you have changed the data columns, you will need to either do the same for the header column (if you have one), or simply rename the columns using the constructor to match the data in the columns.

Indeed, it is so confusing that it might be easier to move the column by simply using the constructor to insert a new column where you want to move the column, set it with the appropriate data source for that column, and then delete the original column. In the following example, you will enter a new column after the Product Identifier , set it to the ProductName data source column (which will set it to “Product Name” in the title bar), 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 cutting / pasting, you will 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> ... 

Answer 4

Another note on working in RDL:
If you make a mistake, the report will display an error message and will not display the data.

If you are not familiar with RDL (report definition language, XML type), these types of errors can be very frustrating to sometimes process a report that cannot be used.

It is much safer to use the new columns and remove the old method in the designer mentioned above. This relieves you of RDL, reducing your chances of damaging the report.

Answer 5

I came across this situation today when I was trying to reorder the columns by dragging and dropping the column heading of the table, this does not work! However, I found that you can drag a cell and (carefully) place it in another cell, and then swap the cells. This way you can reorder the columns by changing the header and content cells without having to create new empty columns, which is better if you do not want the report body width to increase and create blank pages when rendering PDF, of course, this can be fixed again. To drag a cell, click it once, but don’t go into edit mode, then hover over the borders and drag as soon as you get the move cursor. This applies to the report designer available for Visual Studio 2017.

Answer 6

My decision:

 using System; using System.IO; using System.Linq; using System.Xml; namespace MoveSsrsColumns { class TablixColumnReorderer { readonly XmlDocument _xData = new XmlDocument(); readonly XmlNamespaceManager _nsManager; readonly XmlElement _tablixNode; public TablixColumnReorderer(string rdlFileName, string tablixName) { using (var fs = new FileStream(rdlFileName, FileMode.Open)) using (var xr = XmlReader.Create(fs)) _xData.Load(xr); _nsManager = new XmlNamespaceManager(_xData.NameTable); _nsManager.AddNamespace("def", "http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"); _tablixNode = _xData.SelectNodes(string.Format(TablixXPath, tablixName)_nsManager) ?.Cast<XmlElement>().FirstOrDefault() ?? throw new ApplicationException("Tablix node notfound"); } const string TablixXPath = @" /def:Report /def:ReportSections /def:ReportSection /def:Body /def:ReportItems /def:Tablix[@Name='{0}']"; const string SearchColumnXPath = @" def:TablixBody /def:TablixRows /def:TablixRow /def:TablixCells /def:TablixCell /def:CellContents /def:*[@Name='{0}']"; const string ParentTablixCellXPath = "parent::def:CellContents/parent::def:TablixCell"; int FindColumn(string columnControlName) { var columnControl = _tablixNode .SelectNodes(string.Format(SearchColumnXPath, columnControlName), _nsManager) ?.Cast<XmlElement>() .Single(); if (columnControl==null) throw new ArgumentException($"Column with control {columnControlName} notfound"); if (!(columnControl.SelectSingleNode(ParentTablixCellXPath, _nsManager) is XmlElement tablixCell)) throw new ArgumentException($"Tablix cell for column with control {columnControlName} notfound"); var columnIndex = ((XmlElement) tablixCell.ParentNode) ?.ChildNodes .Cast<XmlElement>() .TakeWhile(e=>e!=tablixCell) .Count() ?? -1; if (columnIndex==-1) throw new ArgumentException($"Cannot get index for column with control {columnControlName}"); return columnIndex; } public void SetPosition(string sourceColumnControlName, string destinationColumnControlName) { SetPosition(FindColumn(sourceColumnControlName), FindColumn(destinationColumnControlName)); } public void SetPosition(string sourceColumnControlName, int destinationColumnIndex) { SetPosition(FindColumn(sourceColumnControlName), destinationColumnIndex); } public void SetPosition(int sourceColumnIndex, string destinationColumnControlName) { SetPosition(sourceColumnIndex, FindColumn(destinationColumnControlName)); } const string TablixCellsXPath = "def:TablixBody/def:TablixColumns"; const string TablixRowCellsXPath = "def:TablixBody/def:TablixRows/def:TablixRow/def:TablixCells"; public void SetPosition(int sourceColumnIndex, int destinationColumnIndex) { var tablixColumnsNode = _tablixNode .SelectSingleNode(TablixCellsXPath, _nsManager) as XmlElement ?? throw new ApplicationException("TablixColumns node notfound"); tablixColumnsNode.InsertBefore( tablixColumnsNode.ChildNodes[sourceColumnIndex], tablixColumnsNode.ChildNodes[destinationColumnIndex] ); var tablixRowsCells = _tablixNode .SelectNodes(TablixRowCellsXPath, _nsManager) ?.Cast<XmlElement>() ?? throw new ApplicationException("Tablix rows cells notfound"); foreach (var cells in tablixRowsCells) cells.InsertBefore( cells.ChildNodes[sourceColumnIndex], cells.ChildNodes[destinationColumnIndex] ); } public void Save(string rdlFileName) { using (var fs = new FileStream(rdlFileName, FileMode.Create)) using (var xw = XmlWriter.Create(fs, new XmlWriterSettings { Indent = true, IndentChars = " " })) _xData.Save(xw); } } } 

Using:

 public static void Main(string[] args) { var tcr = new TablixColumnReorderer("myreport.rdl", "Tablix1"); tcr.SetPosition("bill_number", 0); tcr.SetPosition("account", 1); tcr.SetPosition("to_date", 2); tcr.Save("myreport#2.rdl"); Console.WriteLine("done"); Console.ReadKey(true); }