As you may or may not know, SSRS 2008 R2 added the ability to automatically create page breaks on group changes. Historically, people attempted to use custom code to accomplish this and, while it worked, the pagination logic we use in SSRS 2008 R2 breaks the standard implementation of this for several reasons. You can see the typical implementation of this at http://blogs.msdn.com/b/chrishays/archive/2006/01/05/resetpagenumberongroup.aspx.
Unfortunately, in SSRS 2008 R2 the pagination engine does multiple passes in some scenarios thus breaking this logic. Also, the logic referenced above makes the assumption that the report is rendered from page 1 to page N and this assumption is not necessarily true either. We recognized that losing that ability to do the pagination at a group level is a key feature so we actually rolled it completely into the product. Robert Bruckner (one of the SSRS developers) gives a really good explanation of all of the ins and outs of the feature at http://blogs.msdn.com/b/robertbruckner/archive/2010/04/25/report-design-reset-page-number-on-group.aspx.
I wish I could say that this feature has been completely without pain, but that is not true. If any of you have been working with SSRS for any length of time, you know that there are all kinds of quirks to the pagination process. Given the correct information, it is always fairly easy to figure out why the pagination engine resulted in a certain pagination, but understanding all the pertinent details is….a bit difficult. In a lot of ways, I view pagination sort of like the query optimizer in the SQL Server engine. It seems like total black magic if you don’t know the rules it follows, but once you understand the rules it generally makes good sense. However, again like the query optimizer, the pagination logic is very complex and is something that we are constantly refining. As you can imagine, people regularly find edge cases where the pagination either doesn’t function as they want or flat out makes a mistake. In addition, layering group level page breaks on top of normal pagination makes the scenario even more complicated. Thus, I have to make the admission that we have had several fixes released in this specific area. Sigh…
The good news is that I have also generally found that a good chunk of my page numbering problems go away by using the latest CU. The bad news is that a simple fix like upgrading to the most recent CU didn’t fix my most recent problem.
So, here’s the situation. My customer wanted to reset the page number whenever the group changed. No big deal in theory. As documented both in the MSDN documentation and Robert’s blog, all they had to do was to add a couple of properties to the target group:
No big deal, right? They completely expected to see the pagination look something like this:
This is what my customer saw instead:
What the heck????!?!!?!?!
Needless to say, this is when they decided to open a case with Microsoft Support. The problem got even weirder when I discovered that under no circumstances could I duplicate the behavior!!! I could reproduce a whole host of problems (2 of 1, 9 of 6), but by applying some workarounds and the latest CU’s I could always get back to normal page numbering resets. At this point, I was completely stumped. I even engaged the SSRS Product Group to ask if there was some sort of race condition that could lead to this behavior. Unfortunately, the answer I got back was that there was no identifiable race condition scenario where 1 of 337 could happen.
Then, as always seems to be the case, I put the problem aside for a couple of days to let it stew. And then, while driving into work the next Sunday, it hit me. Target server version!
You see, it had occurred to me that one of the good things about BIDS 2008 R2 is that it allows you to publish against both a 2008 and a 2008 R2 instance of SSRS. *However*, the thing I had never checked before was how we dealt with a 2008 R2 feature when deploying against 2008. It turns out we strip the feature out during the publish process. When I change my project properties to target a 2008 instance even though my instance is actually 2008 R2, this is what I saw:
Building ‘FailingScenario.rdl' for SQL Server 2008 Reporting Services.
A PageBreak property was removed from the data region, rectangle, or group ‘GroupOnWhichToIterate’. SQL Server 2008 Reporting Services does not support the PageBreak property ResetPageNumber.
The PageName property specified on the data region, rectangle, or group ‘GroupOnWhichToIterate’was removed from the report. SQL Server 2008 Reporting Services does not support the PageName property.
Build complete -- 0 errors, 2 warnings
This led me to ask my customer what version of SSRS their instance of BIDS was targeting as guess what? It was SSRS 2008!
The good news is that getting the desired group level page breaks was as easy as setting the target version to Reporting Services 2008 R2 and deploying the report.