CODEWITHSUNDEEP
ssrs-2008reporting-servicesssrs-tablix
PaulStock
PaulStock

Reputation: 11263

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

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.

Upvotes: 84

Views: 71813

Answers (6)

Scott Blasingame
Scott Blasingame

Reputation: 1961

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

Upvotes: 141

user11354240
user11354240

Reputation: 131

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

Upvotes: 0

Rajeesh
Rajeesh

Reputation: 421

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.

Upvotes: 3

rashkay
rashkay

Reputation: 824

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.

Upvotes: 31

Mac
Mac

Reputation: 176

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.

Upvotes: 3

DizGrizz
DizGrizz

Reputation: 51

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

Upvotes: 5

Related Questions