Conditional Page break in SSRS

Conditional page breaks in SSRS are always something developer needs to work on in order to get it done. This is especially true when you need to render the same report in PDF.

Recently I need to implement the same and did that with a quick trick. Here is the sample SELECT I am using to demonstrate the same.

select 'Record 1' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 2' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 3' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 4' as record, 1 as PageBreak, 2 as grp
UNION ALL
select 'Record 5' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 6' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 7' as record, 1 as PageBreak, 4 as grp
UNION ALL
select 'Record 8' as record, 0 as PageBreak, 5 as grp

"Record" column is just to show unique records. The most important column here is "PageBreak", which is setting the conditional page break with "1". That means whenever there is "1" in "pagebreak" column you need a page break otherwise keep on displaying the records on same page.

The other very important column is "grp". Well, you really don't need this column and still will be able to implement conditional page break functionality. But then SSRS will club all the records which requires page break into 1 grouping set, probably in the end with this sample data. So, in order to maintain the sequence of records we need to add a grouping column which will create a new group whenever it finds a different value in "pagebreak" column.

Now, lets move on to SSRS report. These steps will work in SSRS 2008. I am not adding standard steps of creating a data source and data set. I know you are all smart guys...

Simple enough, please follow these steps.

1. Add a table with 2 columns, record and pagebreak.
2. Now run the report and you will see all the records are coming in same page.
3. Next, add a parent group to this row, parent to "Details" group in "Row Groups". Please check "Image 1".
4. In the group expression select the "grp" column and the sorting, select "grp" in Sort by. Please check "Image 2".
5. MOST Important, select "Between each instance of a group" of the newly create group under "Page Break" tab. Please check "Image 3".

Yep, that's it, run the report now. And you will see 3 records on page 1, 1 record on page 2, 2 records on page 3 and 1 record on page 4 and page 5.

Image 1

Image 2

Image 3

There are also other ways of doing the same, but I find this one very easy to implement.

Don't forget to hide new group by just minimizing the width and you are good to go. Try to render the same in PDF, and it works like a charm....

- Mohit Nayyar