Set page break after a number of rows

Question

I am trying to set the page break of a table to occur every 200 rows. By default it generates a page break once the report size grows larger than the page length. When I set the property KeepTogether = true, it no longer generates the page breaks but then I have the issue of over 2000 lines on a single page.

I created a group based on the following function:

=CInt(Ceiling(RowNumber(Nothing)/200))

I added a page break between each group instance. The first page now shows 200 lines but all remaining pages continue to page break once the length of the page has been hit  (around 22 rows).

Answer 1

Check out the following links. Let us know if helpful.

BOL 2008:

Page Breaks
In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group in a data region (typically a group immediately outside the detail), add a page break to the group, and then add a group expression to group by a specified number of rows. 

The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.
 
 
=Int((RowNumber(Nothing)-1)/25)

http://www.spacefold.com/lisa/post/2009/03/08/Dynamic-Page-Breaks-in-SSRS-Going-to-Eleven.aspx

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

I did not see anything in the link that addressed my problem directly. I do not have the problem grouping or setting the page breaks. Here is an example of my problem:

Group | row number | value

1  | 1   | A
1  | 2   | B
:
1  | 200 | Z
----PAGE BREAK----
2  | 201 | AA
:
2  | 222 | ZZ
----PAGE BREAK----
2  | 223 | AAA
:
2  | 244 | ZZZ
----PAGE BREAK----

 

Answer 3

Hi,

Try this

Step 1 : Create a similar dataset with below sample code

SELECT A.* ,
FLOOR(((RowID -1) / 94))  PageNo FROM  
(
SELECT 
Row_Number() Over (Order By IDColumnName) RowID,
Column1,
Column2,
Column3,
Column4
FROM  TableName
) A

Step 2: Do grouping on PageNo Column

           Apply page break on the PageNo Group

---

Rajesh Jonnalagadda http://www.ggktech.com

Answer 4

I tried putting my page break groupings in the dataset and I get the same results.
It appears that it is correctly generating page break on each grouping but I am getting additional page break once the length of the page is hit. If I hit the end of the group in the middle of a page, the page may only have a few records on it. Once row 400 is hit, it correctly inserts a page break. The problem isn't with the groupings but rather SSRS inserting additional page breaks after the first page.

Answer 5

Has anyone successfully managed to get the KeepTogether property to work correctly when they have page breaks on groups within the table? It would be helpful to know that someone has this working correctly. If it just doesn't work, I don't want to spend time trying to make it work.

Answer 6

Hi,

I think the problem is that the report body length definition is too short. Though you have set page breaks, it can't get rid of report length definition. Try to set the page longer than 200 rows need.

Thanks. 

---

Yao Jie Tang -Microsoft Online Community

Answer 7

http://dineshasanka.spaces.live.com/blog/cns!22A79FCE82651673!407.entry

---

Blog: http://dineshasanka.spaces.live.com

Answer 8

Yao, you solution seems to resolve the problem of additional page breaks being inserted. When I create a custom page size, it creates other issues. One of my goals was to prompt the user to select the number of rows per page. If a user selects anything less than the largest size, it will result in a bunch of blank pages when printed.

Dinesh, your solution is similiar to other solutions already posted on this thread. A couple of problems with this solution:

1. Only works with a small number of rows. Try it with 200 rows and you will see page break in the middle of a group.
2. Exporting to Excel results in multiple worksheets, one for each group. Users will be less forgiving of this issue.

 

Answer 9

I know it is very late, but will help for someone,

http://jeffprom.com/2012/05/11/ssrs-page-break-after-x-number-of-rows/