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.
The prerequisites are that the report is designed and the data is added and only the pagination is left to do.
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)
Floorreturns the biggest integer number that is less than or equal to the given number.
- the factor
3here 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
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
+ 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.
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 )
Mod 3 is also the number of rows per page you defined.
To provide you a short chechlist to remember the steps:
- Add parent group
=Floor((RowNumber(Nothing) - 1) / [NumOfRowsPerPage])
- Set group property
page break between each instance of groupon property section Page Break
- Delete sorting on property section Sorting
- Delete automatically created grouping column on tablix
Hope this does not only help me!