I have many reports that hide/show columns based on either parameters or user input. When columns are hidden the body size is not recalculated and also cannot be set through expressions. This leads to at best extra blank pages when reports are printed, at worst reports that cannot be embedded as subreports into other reports because they off-set the elements around them when the area into which they are inserted automatically resizes in rendering. This should be considered a bug in the current release (2005). Is there any chance of this making it into the new version (either that the renderer recalculates the body width or that expressions could be used in fields such as body width)?

I would absolutly love this change if possible. We are using a report to send to clients based dynamicly off what the client requests. The only problem is that with over 24 possible columns, there is a lot of extra white space. This mainly becomes a problem when rendering in pdf or image form.

This feature enhancement would be critical for us. We have report summaries that can contain between 1-10 columns, and the number of columns is not known ahead of time. Because space is highly limited, we cannot have white space to the right of the title columns (the "left" columns). Therefore, for EVERY report we create, we have to create a 1 column version, a 2 column version, a 3-5 column version, and a 5-10 column version. I can't tell you how annoying this is. We have actually held back on some reports just because of the added burden of maintaining 4 reports instead of 1. I am sincerely hoping that MS helps us here, and allows columns to be TRULY hidden, whitespace and all. This is such a critical feature that I have placed it at the top of my wish list, above all the other vital things that other people have listed. How about is MS? Mike

Thanks for the feedback. Make sure that you have filed this request on the Connect site (or voted for an existing request). We haven't gotten to this one for RS2008 yet but it might still make it before we RTM.

Thank you for that. I was unfamiliar with the connect site. I have since added this suggestion. Ryan

I checked with the developers and we did implement the collapsing the body size when items are hidden. But if you try to get it working in the latest CTP, you will find that it doesn't seem to work. Why? Because of the new designer semantics that hides all of the textboxes within the column and not the column itself. We expect to fix this in the next CTP and you will get the behavior you want.

Thanks Brian. This sounds like GREAT news. It will simplify my tasks significantly, and allow me to stop generating 5 versions of every report to handle variable columns! Thanks for letting us know! Michael

AWESOME! Thanks again, Brian!

Hi, I was able to solve this issue with some C# code. I loaded the rdl file in a XmlDocument changed the columns I needed to be "0in" and it worked quite well. Let me know if you need more details or you are basically waiting for SSRS2008 I was able to achive the dinamic group by with the columns not being displayed on the screen.

I have a problem. Please help me. I have different fields in a cross-tab report like - Company, Sector, Region, Branch and then execId. I want to display only those fields to which a user belongs. Example - If a director logs in then I want to display all fields, if a Manager logs in then I want to display Branch associated to that manager and execs under that branch and so on. Also, Company, Sector, Region, Branch and execId are groups. Visibility of one depends on the visibility of the previous group. I tried to use your idea of writing expression in visibility property, but it fails. If I try to hide Company group for a sales person(exec) then it hides all the fields as Company is the first group. Could you please let me know how can I do that in Reporting services? I can get their networkId when they log in. Regards, Ashish

Hi Huber Can you tell me in Details,how to Hide/Show Column. Regs Mahesh

Can you please describe how you edited the XML at runtime?

Use C# to load the Report file in a XMLDocument object. Do all the needed changes to the document object in memory and then save it. Once saved you can then point RS to the new file (generate the name through code with GUID).

I'm having trouble figuring out when this event will be triggered in SSRS. I have a multivalued report variable, they choose the fields of the table they want to see, and then click view report. Can I write custom code to respond to this action or do I have to use a custom assembly? When using this report on a site and loading the report file into a XML document what is best practice for the location of the report file? Sorry I know very little about SSRS and not very much about XML.

You are better off then waiting few more days and get the SQL2008 RS CTP and play with it. The release if all is right should help you with the most of the issues you have with 2005. If you are not confident with your knowedge of SSRS and XML it will be a long process to go over all the details of what you need to do. If you use tablix from 2008 it may resolve most of the issues you may be having.

Hi,I dont think i want to buy the new version just to fix this issue. I'd rather fix this with some coding. but since the reports are on the server, how do I load them into the xmlDocument?and after loading what property of the report should I change?Thank you.

This is a very tricky solution since you remove the flexability and security of the REPORTING SERVICES SERVER and secure your reports through your web app security layer. The benefit is that you still design your report in RS report Designer, save it and at run time you take care of the columns that are dynamic and needs change/width modification etc. The solution needs to resideon an IIS Server. So your reports would need to be saved as files and not deployed on Reporting services server. Imagine you have a web site A and a folder on it Reports where your reports would reside. Also imgeine you have a folder TEMP where your reports are saved after you change the XML and update the columns width etc. In a nutshell: 1. This is an IIS web site solution 2. It hacks at run time the RDL (XML format) files and saves then at runtime as GUID names. 3. It loads the files in a web page with a report viewer control on it at run time. So a user would select a report, would select the parameters and click run report. At run time the system would check the required columns, hide (make their width 0 - which translates to 0.0032 or whatever Microsoft deiceded 0 means in their RDL language) the not needed columns and save the file in the TMP folder. After that it displays the saved RDL file in the report viewer control. little more details. This is a sample function. prmGeoRegion and prmGroupBy are the dynamic columns that make your life miserable in this case since they depend on user selection/parameters. private string RDLFixer(string sourceReport, string DestinationPath, string prmGeoRegion, string prmGroupBy) { XmlDocument xdoc = GetXmlDocument(String.Concat(Globals.Settings.ReportsFolder,sourceReport)); //Get the xml document to work with. _NsMgr = SetXmlNamespace(xdoc); //Set the namespace for the xquery _datasetName = GetDataSetName(xdoc); //Get the dataset string form the rdl file. _command = GetCommandToExecute(xdoc); //Get the actual sql command string to execute _connectionString = GetConnectionStringToUse(xdoc); //Get the connection string to use xdoc = FixColumns(xdoc, prmGeoRegion, prmGroupBy); //Fix the columns to the desired width //Get the final file path string FullDestinationFilename = DestinationPath + System.Guid.NewGuid().ToString() + _destinationFileExtension; xdoc.Save(Server.MapPath(FullDestinationFilename)); //Save the file //Check if the file has been saved sucessfully - if not sleep little . while(!File.Exists(Server.MapPath(FullDestinationFilename))) { Thread.Sleep(200); } return FullDestinationFilename; //Return the full file path. } Sample function to "fix" parts of the XML once loaded into the XMLDocument. //The function to fix the GEO columns in the XmlDocument private XmlDocument FixColumns(XmlDocument xdoc, string geoRegion, string groupBy) { string newWidth = null; string area = null; ArrayList alColumns = ArrangeColumns(geoRegion, groupBy); XmlNode root = xdoc.DocumentElement; XmlNodeList groupings = root.SelectNodes("//x:RowGrouping", _NsMgr); string tmpGeoRegion = geoRegion; //Get the value in a temp variable since we will pass it by reference string tmpGroupBy = groupBy; //Get the value in a temp variable since we will pass it by reference for (int i = 0; i <= groupings.Count; i++) { newWidth = GetWidthByIndex(ref tmpGeoRegion , ref tmpGroupBy, i, out area); //Pass tmpGroupBy by reference so that the //Group by changes stay present in the loop if (newWidth != null) { groupings.Item(i).FirstChild.InnerText = newWidth; newWidth = null; } //FixHeaderCell(root, area, alColumns); FixCellBorder(groupings.Item(i), area, alColumns); } return xdoc; } Obviously there are more functions involved but this I hope gives you some idea how this would work.

Hey, [1] Has anyone played with 2008 to ensure that this thing actually works in 2008, as I've read that in a few CTP releases of 2008 it wasn't fixed? and [2] One way of improving what this produces in 2005 (blank pages) is to simply drop a textbox to the right of the report, which then appears on the blank pages (which in my case is every second page). I have disclaimer text in there or, alteratively you can say 'This page is intentionally left blank.' So now if anyone asks you why is it the case in documents that there are always pages marked 'This page is intentionally left blank', when there appears to be no valid reason for doing so, you can tell them.... 'well, back in sql server 2005, when you are hiding columns based on an expression in Hidden property box, there used to be this little feature...' and so on!

Hi, I have created a report in SSRS-2005. It is a matrix report and have 4 groups Group1, Group2, Group3 and Group 4(3 Row group and 1 column group). I am settingHiddien propertyof 2/3 row groups dynamically (based on user parameter). It worked fine with 2005, but as I migrated the report to 2008, It started to hide data row of group prior to hidden group. Further I found to re-produce this issue I don't even need to set hidden property dynamically. For example: If I set hidden property of Group3 =True. Group3 will hide correctly... but side effect is severe.It will also hide all the data rows of Group2-- however still it will show Subtotal of those hidden rows of Group 2. While I will remove all the code to control visibility: obviously it will work... but that will ____ the purpose of my report. Please help.

I am facing the same problem: Hi, I have created a report in SSRS-2005. It is a matrix report and have 4 groups Group1, Group2, Group3 and Group 4(3 Row group and 1 column group). I am settingHiddien propertyof 2/3 row groups dynamically (based on user parameter). It worked fine with 2005, but as I migrated the report to 2008, It started to hide data row of group prior to hidden group. Further I found to re-produce this issue I don't even need to set hidden property dynamically. For example: If I set hidden property of Group3 =True. Group3 will hide correctly... but side effect is severe.It will also hide all the data rows of Group2-- however still it will show Subtotal of those hidden rows of Group 2.

More Detail: CU-1 is installed. Group-3 is not first group (should not be considered as parent group of Group3). All 3 groups use subtotal. Thanks in Advance.

Just for the record... I'm having the same problem. (Have to say, I'm still a newbie on SSRS.)I'm developing a report to be used in Dynamics CRM to show the cases created on the last 3 months to send to the customers (in PDF format)and I also have the white spaces. CRM is used to "log" all support interventions, and not all customers have the same contracts. Visually I solve this by hidding and showing the fields, but the reserved space remains. Sorry, but can't understand why the DEV teamdesigned it this way!SSRS2005 Version 9.00.3042.00EDIT:Seems like there is a workaround:http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/a7c31842-4673-46c7-b96a-606ace22d742/

Hi Folks,Even i have same problem in hiding column: Wheni write column visibility expression it hides the column but i am able to get White space between columns.ExampleI have 3 column called "A","B"and"C". if i hide column "B" i will get white space between Column "A" and "C".Any suggestion would be greatly appreciated.Thanksin AdvanceJags

Hi Guys, So has this actually been fixed, I've tried this on SQL 2008 with SP1, and I'm still getting white spaces between columns when hiding them. This was supposedly fixed way back in 2007 CTP according to someone further up in this post, this is obviously not the case. If it is, then removing the white spaces is not very intuative is it as I would expect it to remove them automatically. So has anyone figured this out, or is this one of those never to be fixed issues. Thanks W

Hi Guys, Apparently you need to use the ConsumeContainerWhitespace and set it to true, Tried this but isn't working for me. Other people seem to have some success with it. Goodluck. W

Any further updates on this issue? I tried the ConsumeContainerWhitespace like Knersis suggested butit still doesnt' remove the whitespace between columns. Are there any other work arounds or upcoming fixes?

I am also facing same problem. Tried ConsumeContainerWhitespace it is not wokring out. I also tried setting the height/width to 0in and infect deleteting the textbox control.... but the whitespace is not removed :(Is there any other workaround found?

I am having the same problem when I've been trying to get a report header. I have found out that you have to use a page header and set the hidden to true for any page > 1. Unfortunately, I get white space on subsequent pages. SSRS 2008 won't let me us a table, so Vikas I wish that I could use your solution. Can you think of another way to suppress the white space when the hidden is true for that area? Thanks, Amy

I found some place that you need to set the report properity 'consumecontainerwhitespace' to true, but the rdlc report properties doesn't have that property listed. Thanks, Amy

Might as well carry on asking this, since it is such a bizarre oversight/decision. Is there any chance this is going to be fixed?

These posts are related to hidden columns, but I am having problems with extraneous white space in other areas, too. I have reports in which I hide entire tablix or subreports based on whether there is any data in them. SSRS leaves white space where the tablix or subreport is positioned in the main report. This is not professional looking and I do not relish the idea of going live with these reports in a few months. I have tried setting ConsumeContainerWhitespace to true as others before me and have not had success with that. Is Microsoft doing anything to solve this problem in 2008 R2?

It doesn't seem that SQL Team serious about the Problem. I am running the above report from Different Server which is running 2005 reporting server in hope I will deploy it to Production Server (2008) once problem gets fixed.It has been about 10 months. Either I have to discontinue the report or have to go whatever available in 2008. Seriously this product does not seem to be seriose about Reporting.

Almost 2 years after this issue was raised, and it's still not fixed. I'm finding more and more simple reporting features that are "supposed" to be fixed in SSRS 2008 are nothing but empty promises. Even Excel can hide columns properly, and this is suppose to be an enterprise solution?

Is this fixed in a latest release? Please let me know. Thanks! I am able to eliminate on-the-fly by using Custom Extension. But that is more complicated than getting a working release.If anybody wants to achieve that by using Custom Extension you can go http://prologika.com/CS/forums/p/1057/3819.aspx to learn how to change a tablix column width on-the-fly.

For me to same problem. Based on passed parameter value i have to hide and visible the report items[Tables and charts]. By using Hidden property i can hide the table. But still empty space is there. How to avoid that empty space.Please help me.. Its very urgent.Thanks in advanceBala

Hi All, We can remove this empty sapce by using subreport control in SSRS. If you are having five tables in a report. In that based on parameter you want to hide and visible the tables means just create all the five tables in seperate reports. Then create one main report. Within that main report call other reports by using subreport control. set the subreport height property value as very min value. Now based on the passing parameter values make hide or visible the subreports. now when hiding the subreport you wont get white space. This is a tricky way ;). If you need any assistance in this feel free to contact me. RegardsBala

Hi All, I've created a report using SSRS 2008. In this report i've hidden the columns based on an expression, but these hidden columns are still taking up the spaces equal to their width resulting in a lot of blank space at the end of the report. I've tried setting "consumecontainerwhitespace" property of the report but couldn't solve this issue. Because of this extra white space the format of the exported report is also distorted. Can anyone plz help. Thanks in advance.

sorry GogglesPisano, your solution doesn't make any difference.

Column Visibilty is disabled in case of group. Please help my report is facing the same issue

It's May 2011 and we still don't have an answer or a fix... :( This is not good. Switching to Crystal Reports could be the only answer on some reports.

It is September 2011. Close to 4 years since the first threat. Cant get rid of that whice spaces when columns are hidden. Clients do not understand this. Nor will they pay for some time consuming work arounds to solve this behaviour.Armand

I've been playing with this today in SSRS 2008 R2 and perhaps based on what I discovered I can help someone. There is a difference between clicking on a Tablix Column and setting the Hidden property in the Properties box in the lower right of the BIS and right clicking on the Tablix Column and selecting the Column Visibility from the popup menu. If you simply click on the Tablix Column to highlight both the header and data row and then change the Hidden property what you are doing is changing the individual Hidden properties of the header and data row simulatenously. This means you have changed the text box hidden properities and NOT the actual Tablix Column. This will result in the text boxes being hidden but the Tablix column will not hide thus the "whitespace". HOWEVER, if you right click on the Tablix Column (that little gray bar ABOVE your header box, this will give you a pop up menu that has a Column Visibility option. Use the Expression to indicate True or False for when you want it hidden. You will notice that when you run the report and your condition is met that the columns to the right are now pulled over against the column to the left of your hidden column, thus eliminated the whitespace. In addition, even objects outside the Tablix that are to the right of the Tablix will be pulled over as well. You DO NOT need to set the Report property for ConsumeContainerWhitespace to True to make this work. In fact, setting this property one way or another has no effect at all related to this issue. Hope this helps someone.

NElliott01, Just wanted to let you know this DID help someone, thanks for taking the time!

Fantastic. Had me stumped for awhile and figured I wasn't the only one.

Right-clicking a rectangle and setting the Visibility to HIDE does not work. The data is suppressed but the data after does not "move up" to fill in the white space. You end up with a blank section in the report. Is there a solution for this?

I also had this problem until I made it conditional by putting in an expression on the Hidden property. If you don't have a condition where you want to see it sometimes and other times you do not then put in an expression that will always be true such as "=iif(true,true,false)"...I just tried this and it worked.

I've been playing with this today in SSRS 2008 R2 and perhaps based on what I discovered I can help someone. There is a difference between clicking on a Tablix Column and setting the Hidden property in the Properties box in the lower right of the BIS and right clicking on the Tablix Column and selecting the Column Visibility from the popup menu. If you simply click on the Tablix Column to highlight both the header and data row and then change the Hidden property what you are doing is changing the individual Hidden properties of the header and data row simulatenously. This means you have changed the text box hidden properities and NOT the actual Tablix Column. This will result in the text boxes being hidden but the Tablix column will not hide thus the "whitespace". HOWEVER, if you right click on the Tablix Column (that little gray bar ABOVE your header box, this will give you a pop up menu that has a Column Visibility option. Use the Expression to indicate True or False for when you want it hidden. You will notice that when you run the report and your condition is met that the columns to the right are now pulled over against the column to the left of your hidden column, thus eliminated the whitespace. In addition, even objects outside the Tablix that are to the right of the Tablix will be pulled over as well. You DO NOT need to set the Report property for ConsumeContainerWhitespace to True to make this work. In fact, setting this property one way or another has no effect at all related to this issue. Hope this helps someone. I confirm, but consider this scenario based on a real example im working with: You have 10 tablix items which you want either hidden or visible depending on an expression. You wish these 10 tablix to be in succession but with a small spacing if they are visible. Now the problem is that when a tablix is hidden you can still see the whitespace and this whitespace consist of the tablix size and the small spacing. Now only having column visibilty set to false will still give you the small spacing problem which stacks to 10 tablix. if the small spacing you have is 0.5cm then 10 tablix = 5cm white space eventhough all tablix are hidden. Solution: 1) Insert your expression on the tablix column visiblity = this will hide the column and whitespace of the tablix size when expression returns true (do this for all columns if you have more than 1 column) 2) Insert a row with a small height instead of the small spacing, i.e. remove the small spacing and let this spacing be controlled with the row so when column is invisible then the small white spacing is removed too. 3) we are not enitirely done yet, because in order to have a correct number of pages when we print we need to add the expression to the row visibilty too. So now the report and the printing of e.g pdf and mhtml will be correct and you will have no whitespacing issues. I hope this helps.

Hallo! I don´t get it. may be someone can help me. If I highlight ONE COLUMN by clicking on the grey frame. I have higlighted one column. Then I click the right mouse button and choose Tablix-Properties. Next I set the Visibility to a Textbox. But I always change the properties for the whoole Tablix not only for one row. by the way, I am using SQL 2008 R2. Thanks for any help in advance. With kind regards. Tobias

Select Column Visibility instead of Tablix Properties when you right click

HI NElliott01, I see, all my Information are stored in Rows. There for I can´t use the Column Visibilty. I have to try it. Thank you very much! Kind regards! Tobias

Hi, I have created a report in SSRS-2005. It is a matrix report and have 4 groups Group1, Group2, Group3 and Group 4(3 Row group and 1 column group). I am setting Hiddien property of 2/3 row groups dynamically (based on user parameter). It worked fine with 2005, but as I migrated the report to 2008, It started to hide data row of group prior to hidden group. Further I found to re-produce this issue I don't even need to set hidden property dynamically. For example: If I set hidden property of Group3 =True. Group3 will hide correctly... but side effect is severe.It will also hide all the data rows of Group2-- however still it will show Subtotal of those hidden rows of Group 2. While I will remove all the code to control visibility: obviously it will work... but that will ____ the purpose of my report. Please help. I know this was posted a long time ago, but it took me a while to find the answer to this problem about 9 months ago and wanted to post what I found in case it can help someone. In 2008 and 2008R2, if the groups have a parent-child relationship, and you hide the child, it will also hide the parent. If you use the adjacent group setting, you will be able to hide one group at a time using an Iif in the Visibility properties of the group. I have not tried using the Column Visibility in a Matrix with multiple groupings yet. I have only used it with detail columns in a table.Ann Weber