Posts Tagged ‘Toggle Page Breaks’

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. I didn’t like the idea of creating a second report that had the Page break option checked, so here is another solution starting with an existing report.

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 the Report 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.