Page Break expression 1

[sds814]

I have a table with page breaks set on groups. This way when the report is exported to excel it comes out in multiple sheets. However, business wants another report with the same data to come out in one sheet when exported to excel. I have a parameter that allows the user to export the report in multiple sheets or one sheet. I wanted to have an expression on the grouping that would perform page breaks on the report based on the parameter. However, I'm not sure where the inline function should be placed.

This is from an MSDN article (

http://msdn.microsoft.com/en-us/library/ms157328.aspx):

In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group that contains the groups or detail records you want, add a page break to the group, and then add a group expression to group by a specified number of rows.

The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.
Copy Code
=CInt(Ceiling(RowNumber(Nothing)/25))

But I'm not sure where the group expression is located.

I tried putting the inline function in the "group on" section of grouping and sorting properties, but the report came out in multiple sheets when I chose the one sheet parameter.

Thanks for the help.

 

[SgtJarrow]

Since you did not specify, I am assuming SQL 2005. The same principal should apply in SQL 2008, but I do not use that so I can't give specifics.

Assuming a boolean variable called ShowBreaks.

Open report in design view
Right click on table and select Properties
Click on Groups tab
Select group and click Edit

Put the following in the Expression line (Replace whatever is there), replacing Field1 with your field name:
=IIF(Parameters!ShowBreaks.Value,Fields!Field1.Value,"")

What this basically does is if the user selects true, the field is evaluated and grouped on properly. If the user selects false, ALL the values in the field to be evaluated for grouping are set to an empty string - which means they are all in the same group .

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[sds814]

Thanks for the help.

Yes, I'm using SSRS 2005. I noticed that doing it this way doesn't produce subtotals (group footer row) for the groups as the field becomes "" when the user doesn't want breaks. Any way around this as business just told me know they do need the subtotals for the groups?

 

[SgtJarrow]

I don't know...I found this solution while searching the web. It "suppresses" the grouping, so based on the solution above, I would have to say no. Is there another solution that fits your needs? Again, I am not sure.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer