SSRS Reports: Toggle page break option when grouping data

Allow User to Toggle Page Breaks in SSRS 2005 Reports

Scenario:

When developing SQL Reporting Services Reports, you surely are aware of the checkbox to allow the “Page break at start” option when grouping data. However, you cannot allow a user to toggle whether or not they want a page break after each group.

Solution:
Three things you need to page break option for each Group by

1. You need a parameter type boolean with default value false that will give user to select if user want page break or not
2. You need to add parent Group: Group by - that will Group by the Report
3. You have to add parent Group that will be on the top of the Parent group created in Step2 (This is basically page break group) and in the Group on expression: =iif(Parameters!PageBreak.Value,Fields!PERS_Staff_Code.Value,"")

Our starting point is a simple Contact report that groups Contacts by the Account name. The initial report layout would look something like the one below. Please note that the group we currently have does not have the “Page break at start” option checked.

 

The first thing we need to do is to create a report parameter for the user to set as they please. Click theReport menu and select Report Parameters. Create a parameter similar to the one below. Click the Add button and name it “PageBreak.” Set the Data type to “Boolean” and enter a Prompt of “Put a Page Break after each group?” Set the Default Values to “Non-queried” and type in “False” without any punctuation. Click OK.

 

Next, we need to add another group above our existing group. This group will contain a formula based on our parameter and display the page break or not. Right click the left most cell of the header row (left of the cell showing FullName) and choose Insert Group. Enter values as in the screen shot below.

Name it PageBreak, Expression value is: =IIF(Parameters!PageBreak.Value,Fields!SomeField.Value,”")

Make sure to check the “Page break at start” checkbox. Lastly, click the Sorting tab and be sure to sort on Account Name (=Fields!accountidname.Value). This should be the same sorting as on the original group in your report.

Your report should now look like the screen shot below. The new group is highlighted.

 

Now, preview your report. You will see your Page Break parameter at the top of the report defaulted to false. Take a look at the page count.

 

Now, change the parameter to True, and click View Report. You will notice the page count will increase quite a bit.

 

That will do it.