[SSRS] A guide to page breaks on report body

Since I have to keep trying to create a nice page break on a ssrs report body every time, the time was right to find out a way that works almost everytime and make it my own best practice for page breaks.

Prerequisites

The prerequisites are that the report is designed and the data is added and only the pagination is left to do.

Definition

This way defines a fixed number of rows per page to handle the page breaks correctly.
Also nice is that this solution gives the ability to use the page number in the report body by the way which is not accessible via the Globals!PageNumber parameters!

Adding an outer grouping

It doesn't matter if you have a tablix with only a detail group or with a grouping. The first step is to add an outer row group.

Add parent group

Do this by right-clicking the row group and choose "Add group" and then "parent group":

Translation of screenshot:
"Add group" => "Gruppe hinzufügen"
"Parent group" => "Übergeordnete Gruppe"

The group by expression will be the following:

=Floor((RowNumber(Nothing) - 1) / 3)

• Floor returns the biggest integer number that is less than or equal to the given number.
• the factor 3 here defines the number of rows to display per page.
• RowNumber counts the number of rows in the given scope

Set group properties name, sorting and page break

Now right-click the group just created and go to its group properties.
Give the group a self explaining name like "Grp_PageBreakOnly" so the next person trying to figure out how the report works will recognize its purpose fast.

Now on the page break section of the group properties select "between each instance of group" and leave the other two unticked.

SSRS creates a sorting automatically for the same as your group by statement. Go to the sorting section in the properties and delete the sorting.

How it looks for now

Delete unnecessary column

As last step, delete the first column in your tablix that SSRS created automatically when the group was created. Choose the 2nd radio button to “Delete columns only” which preserves the group itself which contains the Floor function.

Additional

Get current page number

Note to use your defined number of rows per page from the grouping as factor (3 in my example here)

=Floor((RowNumber(Nothing) - 1) / 3) + 1

The + 1 is to make it starting at 1 because without it the first page would be displayed as page 0.

Get total number of rows:

"tblLines" is the name of your scope. I used the tablix here since I have only one grouping inside it. You have to find out the scope to use in different constellations by yourself.

=CountRows("tblLines")

Check if on last page

=Floor((RowNumber(Nothing) - 1) / 3) + 1 = CEILING(CountRows("tblLines") / 3)

Display a subtotal row

To make it easy for you to define a hidden expression for objects that should be visible on all pages but the last - for example a subtotal row in the tablix - I give you this expression:

=IIF(
	Floor((RowNumber(Nothing) - 1) / 3) + 1 = CEILING(CountRows("tblLines") / 3) OR RowNumber(Nothing) Mod 3 <> Nothing,
	TRUE,
	FALSE
	)

The Mod 3 is also the number of rows per page you defined.

Summary Checklist

To provide you a short chechlist to remember the steps:

1. Add parent group =Floor((RowNumber(Nothing) - 1) / [NumOfRowsPerPage])
2. Set group property name
3. Tick page break between each instance of group on property section Page Break
4. Delete sorting on property section Sorting
5. Delete automatically created grouping column on tablix

Hope this does not only help me!