none
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

    Thursday, February 14, 2008 6:20 PM

Answers

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

    Monday, April 28, 2008 4:16 PM
    Moderator
  • 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

     

    Tuesday, March 11, 2008 5:59 PM

All replies

  • 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

     

    Tuesday, March 11, 2008 5:59 PM
  • 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 Smile Maybe its time to switch back to a reporting solution that doesn't overlook trivial S**t like this, Crystal anyone?

    • Proposed as answer by ramizan Tuesday, November 10, 2009 8:59 AM
    Thursday, April 24, 2008 10:09 PM
  • 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.

    Monday, April 28, 2008 4:16 PM
    Moderator
  • 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?

     

    Thursday, September 11, 2008 12:22 PM
  • 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>

     

    Thursday, September 11, 2008 2:47 PM
  • 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.

     

    Thursday, November 20, 2008 2:56 AM
  • 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

    Tuesday, December 02, 2008 3:55 PM
  •  

    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.
    Wednesday, December 03, 2008 10:15 AM
  • 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
    Friday, May 29, 2009 9:33 PM
  • 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.
    Thursday, August 20, 2009 11:13 PM
  • 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.....
    Friday, October 02, 2009 2:26 AM
  • 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.
    • Proposed as answer by oozama Friday, May 21, 2010 5:52 AM
    Wednesday, January 20, 2010 12:25 PM
  • 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

    Friday, May 21, 2010 5:56 AM
  • 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! :)

    • Proposed as answer by jordics Tuesday, August 03, 2010 4:17 PM
    Tuesday, August 03, 2010 3:32 PM
  • I am trying to do this for a long. Do you have sample of codes/reports for this?

    Thanks. 

    Wednesday, July 27, 2011 4:06 PM
  • 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


    Thursday, August 18, 2011 10:21 AM
  • 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.

    • Proposed as answer by tautvis Thursday, November 24, 2011 11:53 AM
    Thursday, November 24, 2011 11:53 AM
  • This is wonderful trick to know.  Thank you for sharing.
    Friday, February 10, 2012 8:35 PM
  • 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.
    Friday, February 10, 2012 8:40 PM
  • Can you add/change the output format to each column as well (for example date field, currency field etc..)?

    Friday, February 10, 2012 8:42 PM
  • 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.


    • Edited by Waldek Nowak Saturday, February 18, 2012 6:49 PM
    Saturday, February 18, 2012 9:51 AM
  • 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?

    Tuesday, March 13, 2012 11:57 PM
  • 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);


    Tuesday, March 27, 2012 8:00 PM
  • 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?

    Thursday, July 05, 2012 6:32 PM
  • 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?


    • Proposed as answer by zj01 Thursday, September 13, 2012 8:53 AM
    • Unproposed as answer by zj01 Thursday, September 13, 2012 8:58 AM
    Thursday, September 13, 2012 8:52 AM
  • 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.


    • Edited by viswanadan Wednesday, January 30, 2013 12:02 PM
    Wednesday, January 30, 2013 11:59 AM
  • 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.
    Thursday, April 18, 2013 5:46 PM
  • I am trying to do this for a long. Do you have sample of codes/reports for this?

    Md. Shohel Rana

    Saturday, April 27, 2013 5:53 AM
  • It's simple and Easy !!!

    1. Add a row above/below your header row.
    2. Insert a chart (unbound) in the column you need to set width dynamically.
    3. Charts have the property of "DynamicWidth", so set a parameter in expression for this property.
    4. Now when you supply the values to the parameter, the chart will grow and hence the column accordingly.

    Dynamic Column Width

    This is a simple way around - Hope you all like it!

    Geo Pakistan.

    • Proposed as answer by Ghufran Sajjad Tuesday, December 17, 2013 1:21 PM
    Tuesday, December 17, 2013 1:14 PM
  • Thanks Ghufran, !!!!

    Jorge Arranz

    Wednesday, February 05, 2014 8:42 PM
  • Hmmm, I see a flaw with this 'work around' being that it only dynamically sizes the chart and not the whole column. Anyone else find that to be the case as well? If not, can you please provide the solution to how you worked around that? Thanks!
    Tuesday, February 18, 2014 5:51 PM
  • Hi i was facing the same problem, what i did was to create a chart inside the column with dynamic width expression like this: =Parameters!ReportParameter1.Value & "cm"

    Hope it helps..

    Thursday, February 20, 2014 7:04 PM
  • Ghufran,

    I have tried the dynamic chart option and it works well if the report has a single page.

    My report is a matrix with a text column and x data columns. I have added a chart in each column (in the group header) as part of the group header.

    I set the dynamicwidth for the text column to  = Parameters!TextWidth.Value.ToString + "in"     (calculated based on the font size and max text length)

    and the column widths to   =  Parameters!ColWidth.Value.ToString +"in"   (calculated based on font/num columns/ page width)

    I have set the properties to repeat on every page and the column headings and the 'charts' are repeating as desired.  However the dynamic width  is NOT being retained on the second page (and subsequent) pages.

    Any ideas as to what may be causing this. I just installed SP2 to see if that might resolve the issue  but it doesn't appear to have any effect.

    btw - thanks for the suggestion. This was the first thing I have found that even begins to address this issue when the reports are on the server rather than local.  The fact that it works on the first page in itself negates Microsoft's contention that it cannot be done. I wish they would address the fact that they dropped the ball entirely on this and fix what is a common need for any type of reporting environment.


    Liz

    Monday, February 24, 2014 10:09 PM
  • I found the chart idea to work as well this was and also found other ways to make this work as well however the same issue remains as Liz pointed out above.....once you move past page 1, SSRS appears to ignore all column widths and go about it's own merry way and cannot be controlled again. If anyone has found a work around for this post page 1 issue that would be great to hear.

    N

    Friday, March 14, 2014 5:57 PM
  • Try this - doesnt work for a Matrix but does work really well for a Tablix report

    http://stackoverflow.com/questions/16262175/dynamic-column-width-for-a-rdlc-report

    A quick summary. If you have a Text box that has a min width of 2 inches and a max width of 4inches and for example you want to increment an inch at time..

    Create 3 adjacent columns  : Col1  width 2in  ; Col2  width 1in ; Col3  width 1in

    For all the rows in your Tablix, merge the 3 columns.

    In the column visibility Hide Col2 and Col3 as required.. the column width will shrink/expand to the required size.

    Hacky but works.

     


    Liz

    Friday, April 11, 2014 4:48 PM
  • I have tried the chart's DynamicWidth option, but didn't work. I'm using VS2013 to create a RDLC report with a matrix. This report has variable number of columns, so I can only use matrix. I have tried to add a row insert the group just below the header row, add a pie chart into the data column, and set the DynamicWidth as an expression =Parameters!HeaderWidth.Value & "in". But it doesn't work.
    Thursday, July 03, 2014 2:05 AM