Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

SSRS Page Break After X Number of Records

Posted by jeffprom on May 11, 2012

In SQL Server Reporting Services you can add a page break after a desired amount of records. Here is how you do it.

1. Add a new Row Group and give it a name.
In our example we named our group:
LimitNumberOfRowsPerPageGroup

SSRS Row Group

2. Go to the properties of the Row Group.

SSRS Row Group Properties

3. Use the following expression to group the row on:
=Floor((RowNumber(Nothing) – 1) / 250)
In this example, 250 is the number of records we want per page.

SSRS Row Group Expression

4. On the Page Breaks tab, check the following:
‘Between each instance of a group’
‘Also at the end of a group’

Page Breaks Tab

Go ahead and run the report.
You should now have 250 records per page.

Advertisements

7 Responses to “SSRS Page Break After X Number of Records”

  1. nish said

    what if there are row groups exists already in the report

  2. Jean Paul said

    It returns back only on row, what I am doing wrong?

  3. Jean Paul said

    I find what was wrong, when I use the expression(“=Floor((RowNumber(Nothing) – 1) / 250)”) it does update the sorting expression as well to =Floor((RowNumber(Nothing) – 1) / 250), if you face the same issue, delete the sorting expression and you should be fine.
    Thanks

  4. Mukesh said

    Even I have the same question as NIsh

  5. jeffprom said

    If you already have a row group, you would simply add another row group that would only be used for the number of records.

  6. Bonnie Santantonio said

    This was really helpful. Do you have any ideas on adding two page breaks? I am working with duplex paper, and if the page goes beyond the second page, a prior customers info will be on the back of the next customer record. I have tried everything to get it to work. Any Ideas? Thank you in advance

  7. Ted Smith said

    I tried your solution and the following happens. If you have a grouping below the Limit Number grouping, it counts all records in the child group, therefore it is not consistent in the number of rows it displays. Also, for some reason, it shows duplicate rows. Help please?
    Thank you in advance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: