Conditional Visibility and Page Breaks with SQL Server 2005 Reporting Services

Question

I know there's a bug with conditional visibility and page breaks with SQL 2005, but I wonder if anyone has come up with a work around.

I have a table that has a conditional visibility expression, and I need a page break at the end of the table.

• If I set the PageBreakAtEnd property to true. It is ignored no matter what. Remove the visibility condition and it works.
• If I place the table inside a rectangle with the conditional visibility on the table, and the page break on the table. Same result. The page break property is ignored.
• If I set the rectangle with the PageBreakAtEnd property and the table with the visibility condition, then I still get a page break even when the table isn't shown.

Any other ideas on what to try? I'm almost at the point where I need a separate report rather than conditional visibility :(

Edit: @Josh: That has the same problems. If the second table has conditional visibility it doesn't work. If it doesn't have the visibility expression, then I get the page break all the time.

@Erick: I really wanted that to be the answer but unfortunately it doesn't work. When the visibility expression evaluates to hidden, there's a big gap where the rectangles would be (which I can live with), and when it evaluates to visible, the page breaks still don't work.

Answer 1

Add a second (empty) table immediately after the first. Page break after that.

Answer 2

Place two rectangles, one inside the other.
Place your table inside the inner rectangle and set it to always be visible.
Set the inner rectangle's Page Break to Insert After Rectangle.
Set the outer rectangle's visibility to use your conditional expression.

The page break and the conditional visibility are now separated, and the inner rectangle's page break won't be processed if it is not visible, but it will if it is visible.

Edit: When I tried this, it did not appear to work in the Preview tab in Visual Studio, but it did work in the Print Preview and when I exported the report to PDF.

Answer 3

The above solution works. But I am not sure why do we need to have nested rectangle, it works fine with single rectangle instead.

Answer 4

Hi This is Bala samsnai, Me too came accross the same type of error.

I soveled this with out using Rectangle.

Instead of giving the expression to the complete(whole table)

select the one row in that tabe,give the visibilty expression. like that repeat it for all the rows (like Header, Detailed, Footer) and give the visibilty expression.

By that we can get work both the Visibilty and Pagging both at a time

Bala samsani

Answer 5

Hi But its applicable only for Pdf, Excel it doesn't Work...

if any one got the soltion for excel Output......

Answer 6

I tried Bala Samsnai solution and it works. Will explain more later. Erik B's solution of uisng two rectangles kind of worked when I hit a snag that I cannot embed a table in the Detail row of another table. So that was a bummer.

I followed Bala's solution with my report, which contains only one table and two groups within the table. Instead of adopting and applying an expression to control the Visibility of Groups, I just left that as Visible and applied the Visibility condition expression to each row's Hidden property. Right click on the Row Handle on the far left and you will properties window popup on the right or left (usually as a tab next to Solution explorer). In the Visibility grouping, you will see a property called "Hidden" which will have a default value of FALSE. click on the value and in the dropdown, first option is an expression. Viola and you can setup you condition when the row is hidden. It worked like a charm for me.

Hope this helps others. In my case, I had to not show the details section when some of the values where 0.

Answer 7

I struggled with this problem for quite a few hours until I discovered that the my layout was to wide to fit on print (A4). I had used the extra width for commenting the different field in text boxes with Hidden=false, and as a result twice as many pages as neccessary were generated to display whitespace.

So, you might want to check page width as well.