0

Microsoft SQL Server Reporting Services Designers Version 13.0.1701.8

I have a calendar report and I'm trying to add an extra row to the bottom when the number of weeks in any given month is less than 6. This is because if there are less than 6 then other parts of the report spill over. I have also tried setting page breaks but it has always added extra blank pages so now I'm trying this.

Example with 6 rows:

enter image description here

Example with 5 rows:

enter image description here

Currently I have added an extra row, grouped it on WeekNumber and set an expression to hide/show it.

enter image description here

Expression:

=iif(CountRows(Fields!WeekNumber.Value, "DataSet1")=6,True,False)

Error:

Severity Code Description Project File Line Suppression State Error [rsInvalidAggregateScope] The Visibility.Hidden expression for the text box ‘Textbox14’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

Severity Code Description Project File Line Suppression State Error [rsInvalidAggregateRecursiveFlag] The Visibility.Hidden expression for the text box ‘Textbox14’ uses an aggregate expression with an invalid recursive/simple flag. The valid values for this flag are ‘Recursive’ and ‘Simple’.

I also tried the following and got the same error as above:

=iif(CountRows(Fields!WeekNumber.Value, "Simple")=6,True,False)

SOLUTION:

Thank you for the answers everyone. So in my problem I had two tablixes, one with a calendar and another with summary data. To solve my problem I ended up setting Add Page Break After on both of the rectangles my tablixes were in.

Jonathan Porter
  • 1,365
  • 7
  • 34
  • 62

1 Answers1

1

You shouldn't have to add an extra row to your table. The whole point of groups is that the table expands for whatever data happens to be there. Besides, this isn't going to solve your paging issue.

To get the page breaks to work properly I suggest you do two things.

  1. Set the page break to begin with whatever you want first on your page. If it's just the table, use that. If you have some sort of header, put it in a rectangle and then set a page break at the start of the rectangle. This ensures that you don't have variable amounts of white space at the beginning of a page.

  2. Make sure your margins are set low and that the table isn't getting too wide. If it's getting too wide for the page, it will create extra blank pages. Especially if you export to PDF.

EDIT: Since you have multiple elements that you want to repeat on each page, you can wrap them all in a grouped rectangle and then set the page break to occur between instances.

See my answer here on how to do that. In your case it looks like you would group this by month.

Community
  • 1
  • 1
StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • I set the page break to before and this helped a lot! Now the only issue is that the first page is blank. – Jonathan Porter Jan 10 '17 at 16:49
  • @JonathanPorter OK, you're getting closer. See my edit. – StevenWhite Jan 10 '17 at 17:01
  • Although I believe your answer would normally be the solution to this problem it didn't quite fix it for me and I ended up messing around and updated my question with the answer that worked for me. Thank you very much! – Jonathan Porter Jan 10 '17 at 18:20