Is there an easy way to rearrange tablix columns in SSRS design mode?

Question

I have an SSRS report which contains more than 20 columns in a tablix. Our users have decided that the data is OK, but they want the columns moved around (sigh!).

It seems like it should be easy to re-arrange the columns (move column 3 to column 1, swap columns 4 & 5 etc.). But, drag and drop does not seem to work and the only solution seems to be deleting the original column and re-inserting it in the correct place (and re-applying any expressions & formatting already created for the column).

Is there any easier way of doing this? Please note that I don't want a programmatic solution, but just need to change it once in design mode.

Wexler

@ensisNoctis not even close. AtPaulStock, I had to just delete my columns entirely and readd them, as mine were grouped columns. – Vaticinal

Answer 1

There is a way to move columns through the designer:

1. insert the number of blank columns you want to move in your destination spot
2. shift-left-click on the cells (NOT the header column) you want to move
3. right-click and select the Cut command
4. right-click on top of the destination columns and select Paste
5. delete the now empty old columns

Clodhopping

Tried this with rows; worked like a charm. Much much safer/easier than editing XML... – Joo

This should be the top answer. SO much safer than messing with the XML. – Dincolo

I agree this seems safer, especially if you are newer to SSRS like myself and just looking for a quick fix. – Sporran

Doesn't work with certain formulae expressions, for instance RunningValue mysteriously lost its 3rd parameter when I tried this, so I had to restore that manually. I imagine the XML-based method would have preserved this. – Knitting

To fully take advantage of this approach, I find it necessary to continue holding down the SHIFT key while selecting the column textboxes (and the headers too if desired) AND also while right-clicking to choose CUT. If I do all of this properly I get satisfaction -- but all of these details are very subtle and non-intuitive. Thanks for your answer. – Numbat

I couldn't get this to work with row groups in a column :( – Vaticinal

Works well. But don't forget to adjust the width of the whole report back to the previous value, to prevent surprises when generating PDFs. – Magyar

@Knitting Thanks for the heads-up. Column visibility expressions must be manually entered as well. – Godly

Glad you posted this, it works well and saves time. Still, wish Microsoft had come up with a better way! – Gilstrap

Seems to work for 2 or more columns ... I wasn't so sure this works for just one column? Will try again... – Corelli

Either this answer isn't very clear and I'm following it wrong, or it just doesn't work for me in VS 2015. – Elaelaborate

For drag-n-drop, Rajeesh's answer on this page works really well too! If it doesn't work the first time, ctrl-z and try again. – Mellifluous

Answer 2

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:

1. First of all backup the original report by copying it to another file.
2. Right Click on your report in Solution Explorer and select "View Code"
3. This opens up the RDL of the report --- don't get scared it is just a simple xml file
4. Now locate within the RDL file the "Tablix1" tag -- look for <Tablix Name="Tablix1"> ....</Tablix >
5. You now need to look for different "<Textbox Name="...">...</Texbox>" tags nested within the <TablixCells><TablixCell><CellContents>.... tags
6. 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.

Fitting

+1 for another example of how little Microsoft has invested in giving its developers reasonable reporting tools. – Assiduity

Just an FYI for anyone reading this response, you have to move the elements containing the column names separately from the elements containing the details. Column widths are also stored separately as are any visibility rules you have applied... This method starts to look not so easy anymore... – Buckskins

Scott Blasingame's Asnwer is way simpler, if you find this XML Method a bit too complicated! – Gaylordgaylussac

Answer 3

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>
...

Searles

Answer 4

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.

Cohin

Answer 5

I came across this situation today as I was trying to reorder columns by dragging the column header of the tablix, it doesn't work! However, I discovered that it is possible to drag a cell and (carefully) drop it on another cell then the cells swap. This way you can re-arrange columns by swapping header and content cells without having to create new empty columns which is better if you don't want the report body width to increase and produce empty pages in PDF rendering, of course it can be fixed again. To drag a cell, single click on the cell but don't enter edit mode, then hover mouse around the borders and drag once you get the 'move' cursor. This is applicable to report designer available for Visual Studio 2017.

Rhinal

Answer 6

My solution:

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);
        }
    }
}

Usage:

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);
}

Dariusdarjeeling