| Author |
Topic |
|
Joseph Schrag
Starting Member
13 Posts |
 Posted - 2009-04-17 : 18:59:56
|
| I am using SQL Server Reporting Services 2005 to create a report in which the user can select the visible columns. I have succesfully implemented this functionality using parameters. However, when exporting the report to PDF space is taken up for the hidden columns which causes blank pages to be generated. I have read elsewhere that this is a bug in SSRS because the report width is not recalculated. Does anyone know of a workaround for this problem? Perhaps a way to force recalculation of page width after selecting parameters? Note: I must use SSRS 2005, upgrading to 2008 is not an option at the moment. That said, it would be nice to know if this is fixed in 2008 for future reference. |
|
|
justinlinz
Starting Member
6 Posts |
Posted - 2009-04-20 : 12:48:09
|
| I searched for a while on this same problem. One suggestion was to set the width of each of the items to 0 width and make sure they are configured to increase to accommodate contents. This didn't work for me, but it may be worth a try.Thankfully I did find a solution that worked for my report. It was a suggestion to open the rdl in notepad, find the <Report><Width> element and manually modify this to be your desired page width (less the right and left margins). Basically I changed mine to 7.25 in, knowing my R/L margins were set to .5 in already. Once you modify the file, update the report through the report manager using this file.Here is a link to the post with this solution:http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/f17e00b8-b5f3-4f44-8860-0892aefb0f81/Concerning your last question, although I too have not upgraded to 2008, it is my understanding that this bug is fixed in 2008, and hidden objects will not render as whitespace causing blank paging issues. |
 |
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-04-20 : 13:02:03
|
| I have not had any luck with the dynamic column width approach either. I'm not sure that solution is possible as widths cannot be set to an expression and the "can grow" attribute only affects height.As for the XML alteration idea, I am in the midst of trying that approach (without success, yet). I have been working with Robert Bruckner on the Microsoft forum and he suggested the same approach. I just posted my simplified rdl xml over there:http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e8604351-b837-4a0f-850b-0dd16129602f?prof=requiredPerhaps you can spot a difference between my report and yours that is preventing mine from working.Thank you very much for your input. |
|
|
|
justinlinz
Starting Member
6 Posts |
Posted - 2009-04-20 : 15:48:13
|
| If I'm reading your XML correctly, your report width is set to 14 in:... <Width>14in</Width> <Body>...Have you tried reducing this to something like 7.0 inches or lower taking into account your page margins? |
|
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-04-20 : 16:15:43
|
| Sorry about that. Just a posting mistake on my part. In testing, I did change that width number to 2 inches and still had the blank page problem. |
|
|
|
justinlinz
Starting Member
6 Posts |
Posted - 2009-04-20 : 17:02:58
|
| ok, also I noticed this:<TableColumn> <Visibility> <Hidden>= IIF(InStr(Join(Parameters!Columns.Value, ""), "Date"), False, True)</Hidden> </Visibility> <Width>11in</Width> </TableColumn>Is there a reason you have this column width set to 11in?Outside of that I cant see much. The only suggestion I could make would be to try both suggestions in combination. Originally I tried the 0 width solution (i actually did 0.5 in width on a handful of dynamically hidden columns) and it didnt correct, however I did not revert that change. Then I did the manual edit rdl report Width and it corrected. So possibly it was a combination of both solutions.While I was under the impression the can grow/shrink option only affected the element's height, the 0 width suggestion seemed to behave contrarily. I set the elements to 0.5 in width, and in the cases where they were visible, it grew horizontally to fit the contents. |
|
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-04-20 : 17:34:11
|
| The column width of 11 is just to simulate several columns. When I hide that column, the body should be very narrow, but I still get a blank page.As for a combination of solutions, I will certainly try that. Could you possibly share the XML from a report on which you have this working (with any sensative information removed of course)? |
|
|
|
justinlinz
Starting Member
6 Posts |
Posted - 2009-04-21 : 09:37:27
|
| Sorry I can't really post any code because its proprietary. Basically the code was a table with 10 columns (all different widths but none more than 1-2 in). A few cols were hidden depending on the value of a report parameter. Those columns that are dynamically hidden were changed to be 0.05 in width and set to increase & decrease to accommodate contents. Then the width value in the XML was changed to 7.25in (with the L/R margins set to .5in) |
|
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-04-21 : 11:26:43
|
| How did you set the columns to increase to accommodate contents? The only "can grow" type properties I have found are on the textboxes in the columns, not on the columns themselves. |
|
|
|
justinlinz
Starting Member
6 Posts |
Posted - 2009-04-21 : 11:35:57
|
| Thats correct, it wasnt actually on the column, it was on each cell itself. |
|
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-04-22 : 14:58:11
|
| When you say "Those columns that are dynamically hidden were changed to be 0.05 in width", how did you accomplish that (dynamically changing their width to 0.5in)? Does your report spill onto two pages if all the columns are shown?Also, what code did you use to hide the columns based on the parameter. I am using a multi-valued string parameter and simply searching the string for the column name.= IIF(InStr(Join(Parameters!Columns.Value, ""), "CurrentRate"), False, True)Did you do it differently?Thanks again for your help. |
|
|
|
justinlinz
Starting Member
6 Posts |
Posted - 2009-04-23 : 17:16:24
|
| The width of the columns is not set dyamically. The columns that are dynamically hidden (their hidden property is set dynmically) have a width of .05in.I think our reports may be a bit different but there are about 15 cols on my report depending on the data returned in a few of the columns some of the cols may be hidden. So the hidden value for those checks the first record in the dataset to see if its null, if so the col is hidden, else its shown.I guess the difference is that the dataset controls the visibility of my cols whereas it seems you have a parameter that controls this. But im not sure if that would make a difference as far as the whitespace issue is concerned. I will try to create a small, generic example of my report that can be used to demonstrate the solution. |
|
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-04-23 : 17:19:43
|
| That would be very much appreciated. Thank you. |
|
|
|
Joseph Schrag
Starting Member
13 Posts |
Posted - 2009-05-07 : 18:58:20
|
| I used the solution here:http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e8604351-b837-4a0f-850b-0dd16129602f |
|
|
|
|
Preventing hidden columns from taking up space?