Dynamic Column width for a report

Question

Will the width property of a column be able to be set at run-time (i.e. ability to set the width of a column to an expression) in SQL Reporting Services 2008? One problem I have seen is the inability to set the width of columns in a report when you you have several columns whose order is determined based on parameters.

 

Lets say I have 2 columns in a report and two fields from the database which will populate those columns: 1 field that is varchar(5) and one which is varchar(50). If I do not know which column each field will go to until runtime how am I supposed to adjust the column width? Letting the column width be set by an expression would solve everything as I could pass the width in to the report. As it stands now, I would have to set each column's width to accomodate a field of type varchar(50) in order to avoid wrapping of data.

 

You could say just create 2 reports, one for each case, but what if I had 15 fields? 20?

 

Has anyone figured out how to dynamically set the width of a column? Will the width of a column be able to be set at run-time in SQL Server Reporting Services 2008 via expression or some other means?

 

Questions, questions, questions.

 

Chuck

Answer 1

Hi Chuck,

 

This is a common request and I definitely see the value in it.  However, we didn't get to this for SSRS 2008.  Please feel free to file a feature request (or add votes to an existing request) on the connect.microsoft.com.

 

Thanks,

Chris

 

Answer 2

So I spent a week putting together an interface for users to pick and choose columns and save the metadata. Then I made these fantastic dynamic queries with all these pivots to generate the reports and now I need to be able to do something as simple as set column width at runtime because I have no idea what column will end up where since its all user configurable through the UI. Anyways, looks like SSRS has foiled me again Maybe its time to switch back to a reporting solution that doesn't overlook trivial S**t like this, Crystal anyone?

Answer 3

One day, Reporting Services will probably include an object model which developers can manipulate via code to implement requirements like these. For now, one solution is to have a reasonable fixed column width (such, as 30) and set the textbox CanGrow to True so it wraps to next line if needed. Another solution is to have two columns and hide one of them conditionally.

Answer 4

Ok just a thought but the only way I can think of doing it.

 

SSRS stores reports as xml files so maybe you can manipulate the xml progrmatically to alter column widths and anything else for that matter?

 

Answer 5

Ok slight update

 

RDL xml files are used during deisgn of reports but when you deply they go into the Content column of a table called Catalog:

 

SELECT [Name],

CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML

FROM [Catalog]

WHERE type = 2

 

So I think this can be edited programmatically.  Widths can be found like this:

 

        <TableColumns>

          <TableColumn>

            <Width>9cm</Width>

          </TableColumn>

          <TableColumn>

            <Width>3.02646cm</Width>

          </TableColumn>

          <TableColumn>

            <Width>3.25cm</Width>

          </TableColumn>

          <TableColumn>

            <Width>3.5cm</Width>

          </TableColumn>

          <TableColumn>

            <Width>3.5cm</Width>

          </TableColumn>

        </TableColumns>

 

Answer 6

This is in response to the solution of hiding Columns.

 

The only problem with this solution is that you can only do this with Table reports. This does not work with Matrix reports. Or, at least it doesn't work the way we need it to.

 

Answer 7

Here's a simple workaround if you absolutely need to get this working.

It's a hack, and you'll have to run this program every time you change the report, but it works.

1. You've probably already changed the visibility of all your dynamic columns to be an expression.

2. Make all your column widths in your table a weird number that doesn't occur elsewhere in your report: i.e. "1.23456in"
3. Save your report
4. Figure out the min and max number of dynamic-width columns, say 1 to 5 columns
5. Figure out the cumulitive width of dynamic-width columns: say 5 inches
6. Write a simple program (or just use the code below) to open the RDL or RDLC as a textfile, loop 5 times, do the text replace of "1.23456in" to (MAX_WIDTH / i), save as "dudeguy" + i + ".rdlc", where i is your loop counter
7. Change the logic of your interface program/webapp to reference "dudeguy" + num_columns

Imports System.IO

Public Class Form1

   Private Sub Button1_Click(ByVal sender As System.Object, _

                                ByVal e As System.EventArgs) Handles Button1.Click

     

      Const MAX_WIDTH As Double = 5

      Dim contents As String = File.OpenText("c:\dudeguy.rdlc").ReadToEnd

      For i As Integer = 1 To 5

         File.WriteAllText("c:\dudeguy" & i & ".rdlc", _

         contents.Replace("1.23456in", FormatNumber(MAX_WIDTH / i, 5) & "in"))

      Next

   End Sub

End Class

Answer 8

 

You might also try to write code for it.

 

http://blogs.sqlxml.org/bryantlikes/pages/824.aspx

 

http://msdn.microsoft.com/en-us/library/ms155798.aspx

Answer 9

 

I thought reports were only saved as xml files while you are developing/designing.  Once you publish this gets placed into SQL Server - see my earlier posts.

Answer 10

Instead you can just limit the wraping of the data and set a column auto width using this solution: http://blog.sharepointalist.com/2009/05/ssrs-column-width-auto-size.html

---

Paul Shkurikhin blog.sharepointalist.com

Answer 11

Has there been any progress for this on Microsoft's end?  Or failing that, has anyone come up with a valid workaround?  Right now it seems like my best option is the create/edit the .RDL file and upload it every time the user views the report, which obviously has some problems.

Does anyone have a life vest that they can throw me here?  Thanks in advance.

Answer 12

I just spent half a day installing, configuring, researching SSRS to explore the possibility of it replacing our simple SQLXML/XSL/HTML reporting solution, and because of this issue, it's not going to happen.

How could MS POSSIBLY  think that static column widths would cut it when everything driving the reports can be so flexible and powerful (I.E. XML data)?

I have data instances where 2 column groups will occur (4-8 columns displayed for each) up to 6 column groups (still 4-8 columns per group). The ONLY way I could use SSRS right now would be to maintain incredibly small columns then deal with a ton of white-space when the counts were small.

To the guys releasing SSRS: This platform has SO much potential and after no less than 3 years, I can still do better with any other number of reporting solutions (including my own which is FAR more powerful at the moment so far as I can tell). So back to it my attention goes.....

Answer 13

I've been strugling with the same issue. I finaly managed to get dynamically sized columns, but it's far from perfect. What I did is that I sized the TableColumn width to 0in and in the header I put CanGrow to true. Now on my screen the reports do resize to the content. But what happens when I export to Excel or PDF is that the columns are actually 0in, which is quite small, to be honest.

In my opinion the screen, the printer and all the exports should always look the same. It's already a living ____ between different browsers to get everything to look the same, but within one product eats the cake.

Answer 14

Not sure if u have managed to solve this problem but i got the solution use the following:

rp.DetailSection1.ReportObjects.Item("AccNum2").width = 200

rp is your report instance name

Dim rp As New 'Report Name'

and i have a question: it is possible to set the width of a field in the report dynamicaly and i mean by dynamicaly is the width can grow according to the width of

the txt iside it

Answer 15

Hi guys!

After an entire morning of looking for information about how to bind dynamic columns to a report I decided to develop a kind of "table manager" to do it. I attach the code below so you can try it. I'm sorry but it's kind of "personalised" (and the comments are in Catalan) but I have the deadline of the project very close! :)

STEPS to make it work:

1. Create a report and put a Table in it. Give a name to the table "Ex.: Dades"

2. Add 8 columns (I needed 8) and give name to all of them just changing the number you put on the back. For the headers I used capsCol1, capsCol2, capsCol3, ... and for the data i used valCol1, valCol2, valCol3. It's important that the numbers are from 1 to 8.

3. You have to create a List<ColumnReport> with 8 ColumnReport, one for each column informing the Title, the Position, the Width, the formula you want to use to get the information for the rows (Ex.: "=Fields!Afortunat.Value")

 

public class ColumnReport : IComparable
 {
  private string _titolColumna, _formulaColumna;
  private int _ample, _posicio;
  private bool _visible;
  
  public string ColumnTitle
  {
   get { return _titolColumna; }
   set { _titolColumna = value; }
  }

  public string FieldFormula
  {
   get { return _formulaColumna; }
   set { _formulaColumna = value; }
  }

  public int Width
  {
   get { return _ample; }
   set { _ample = value; }
  }

  public int ColPosition
  {
   get { return _posicio; }
   set { _posicio = value; }
  }

  public bool Visible
  {
   get { return _visible; }
   set { _visible = value; }
  }

  #region Miembros de IComparable

  public int CompareTo(object obj)
  {
   return this.ColPosition.CompareTo(((ColumnReport)obj).ColPosition);
  }

  #endregion

 

 

Then You just have to use the class GestorReport. Intantiate it and provide the follow information: reportPath, tableName (the name you gave to the table), the two prefixes of the headers fields and value filds (capsCol and valCol in this example) and pass also the list of ColumnReport

 

Here is the code:

 

public class GestorReport
  {
    private string _pathReport, _nomTaula, _prefixColTitol, _prefixColValor;
    private List<ColumnReport> _lstColumnes;

    public GestorReport(string pathReport, string tableName, string prefixColTitle, string prefixColValue, List<ColumnReport> lstColumnes)
    { 
      _pathReport = pathReport;
      _nomTaula = tableName;
      _lstColumnes = lstColumnes;
      _prefixColTitol = prefixColTitle;
      _prefixColValor = prefixColValue;
    }

    public XmlDocument Configurar()
    {
      // 1. Obtenim el report
      XmlDocument objXmlDocument = new XmlDocument();
      objXmlDocument.Load(_pathReport);
      XmlNamespaceManager mgr = new XmlNamespaceManager(objXmlDocument.NameTable);
      string uri = "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
      mgr.AddNamespace("df", uri);

      // Val, ara obtenim totes les columnes on hi ha els tamanys!
      XmlNodeList wCols = objXmlDocument.SelectNodes(String.Format("/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixColumns/df:TablixColumn/df:Width", _nomTaula), mgr);

      IEnumerator<ColumnReport> it = _lstColumnes.GetEnumerator();
      for (int i = 0 ; i < _lstColumnes.Count; i++)
      {
        ColumnReport ci = _lstColumnes[i];

        // Som-hi... què hem de fer?
        // 0. Configurar l'amplada
        // Això va per ordre...segons la i on estem ja l'hi podem anar endinyant l'amplada
        wCols[i].InnerText = ci.Width + "cm";

        // Ara anem a per les coses serioses
        XmlNode campTitol = objXmlDocument.SelectSingleNode(String.Format("/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixRows/df:TablixRow/df:TablixCells/df:TablixCell/df:CellContents/df:Textbox[@Name='{1}{2}']", _nomTaula, _prefixColTitol, ci.ColPosition), mgr);
        XmlNode campValor = objXmlDocument.SelectSingleNode(String.Format("/df:Report/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixRows/df:TablixRow/df:TablixCells/df:TablixCell/df:CellContents/df:Textbox[@Name='{1}{2}']", _nomTaula, _prefixColValor, ci.ColPosition), mgr);

        // 1. Configurem la visibilitat
        if (!ci.Visible)
        {
          XmlElement nVis = objXmlDocument.CreateElement("Visibility", uri);
          XmlElement nHid = objXmlDocument.CreateElement("Hidden", uri);
          nHid.InnerText = "true";
          nVis.AppendChild(nHid);
          campTitol.AppendChild(nVis);

          wCols[i].InnerText = "0cm";
        }

        
        if (!ci.Visible)
        {
          //XmlNode campVisibilitatValor = campValor.SelectSingleNode("./df:Visibility/df:Hidden", mgr);
          XmlElement nVis = objXmlDocument.CreateElement("Visibility", uri); 
          XmlElement nHid = objXmlDocument.CreateElement("Hidden", uri);
          nHid.InnerText = "true";
          nVis.AppendChild(nHid);
          campValor.AppendChild(nVis);

          wCols[i].InnerText = "0cm";
        }

        // 2. Configurar el títol de la columna (es podría fer per paràmetres però sudem)
        XmlNode campTitolValor = campTitol.SelectSingleNode("./df:Paragraphs/df:Paragraph/df:TextRuns/df:TextRun/df:Value", mgr);
        campTitolValor.InnerText = ci.ColumnTitle;

        // 3. Configurar la fòrmula
        XmlNode campValorValor = campValor.SelectSingleNode("./df:Paragraphs/df:Paragraph/df:TextRuns/df:TextRun/df:Value", mgr);
        campValorValor.InnerText = ci.FieldFormula;
      }

      return objXmlDocument;
    }
}

 

 

Then, you just have to call Configurar() and you'll get an XmlDocument which can be passed to the reportViewer as follows:

 

GestorReport gi = new GestorReport(@"C:\Users\Jordi\Documents\Projectes\Habitatge assequible\Programació\Sortejos\Sortejos\Report1.rdlc", "Dades", "capsCol", "valCol", lstColumnes);
XmlDocument reportAjustat = gi.Configurar();

// Configurem el report al viewer...: Report, paràmetres i datasets
reportViewer1.LocalReport.ReportPath = string.Empty;
reportViewer1.LocalReport.DataSources.Clear();

 // A. The dataset with the data   
reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("ResultatSorteig", lstResultat.Elements));

 // B. THE REPORT
byte[] rdlBytes = Encoding.UTF8.GetBytes(reportAjustat.OuterXml);
MemoryStream stream = new MemoryStream(rdlBytes);
 reportViewer1.LocalReport.LoadReportDefinition(stream);

// C. Some parameters
ReportParameter[] parametres = new ReportParameter[1];
parametres[0] = new ReportParameter("VisibilitatColumnaAfortunat", "S", true);
reportViewer1.LocalReport.SetParameters(parametres);

// Refresquem i avall que fa baixada!
reportViewer1.RefreshReport();
reportViewer1.Show();

 

Hope this helps! :)

Answer 16

I am trying to do this for a long. Do you have sample of codes/reports for this?

Thanks. 

Answer 17

Hi Jordics,

 

I also want to set the column width dynamically... Can you please tell me where to write the above given code?

 

Thanks and Regards,

Satish K

Answer 18

For the ones who just want a column to be as narrow as the text in it, there's a simple solution: in the design make the column as narrow as you think your narrowest column can be and set the Property CanGrow to TRUE. This will automaticly increase the column size by the text in it with no white spaces.

NOTE: you must set it to header and all the groupings that follow bellow for the same column.

Answer 19

This is wonderful trick to know.  Thank you for sharing.

Answer 20

You're God if this is working.  Thank for sharing this code.  It would be nice if you can provide full code to the rest of the world so that they can appriciated this wonderfull work/codes.

Answer 21

Can you add/change the output format to each column as well (for example date field, currency field etc..)?

Answer 22

Does anybody know if SSRS in SQL 2012 contains any improvement in coding of column width?

I tried to investigate but didn't find anything useful so far. If anybody finds something promising please let us know. Power View does not seem to have API.

Answer 23

Hi Chuck,

Thanks for clarifying this limitation of SSRS. It has now been almost 4 years since you acknowledged that this is a common request with value - has there been any progress towards providing a solution?

Answer 24

Hi jordics,

Your code for class GestorReport was very helpful. I needed to do some modifications for SSRS 2008 R2: namespace, XPath. I also did modification to make it more general to select subset of any columns in any order preserving original formating of the column. Google translated your comments from Catalan to English.

Modifications to SSRS 2008 R2:

string uri = "http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition";
strSearch = String.Format("/df:Report/df:ReportSections/df:ReportSection/df:Body/df:ReportItems/df:Tablix[@Name='{0}']/df:TablixBody/df:TablixColumns", _nomTaula);

Answer 25

It seems to me that Microsoft doesn't consider this as a valueable feature in the SSRS report.  The reason is that Microsoft has released recent SQL Servers (2012) and non of the feature exists.

Or is this something that Microsoft plan to do it in the future but when?

Answer 26

I tried using the method  Dave Amour mentioned: query out the XML code for dynamic report and then update it back after changing column width, but it does not work, still showing old result. 

If I query out the XML code again, I can see the code is changed properly. Is anything else need to do to "re-fresh" the output?

Answer 27

Is Auto column Width for matrices table is not part of SQL server 2012?  Please let know us whether this feature is provide by any ISV Vendor as part of SSRS custom Tool.

Answer 28

Based on the last two releases of SQL server I believe they have dropped development on SQL Reporting Services.  I think they are now pushing SQL Enterprise edition ($$$$$) and PowerView.  They haven't said it specifically, but I think reporting services is becoming a deprecated tool.

Answer 29

I am trying to do this for a long. Do you have sample of codes/reports for this?

---

Md. Shohel Rana