Reset-On-Group (Page X of XX in a group, within a total Y of YY for the report), SQL Reporting Services Style

Welcome to another Reporting Convergences chapter…

I wrote about page collections in VFP reporting a couple of days ago. Now let's talk about handling the same scenario in SQL Server Reporting Services.

The problem-to-solve

Recap: you have a report with groups or other data-centric elements that you have indicated should cause an explicit page break.

You can talk to the current page number and the total number of pages in a report by talking to Globals!PageNumber and Globals!TotalPages.  There are some limitations to exactly when (in what report layout areas) you can talk to these values. (The limitations are caused by the fact that report processing of the format-centric areas of the page, the page header and footer, occurs separately from the processing of the report body — which makes some sense, when you consider the fact that each output rendered has to work with different ideas about the page layout.)

But you might also want to know about the current page number within your explicit-pagebreak group, and how many pages are in that group.

Use case, for any of you who think this is a straw man requirement set up for the purposes of demonstrating a "cool-for-nothing" technique:

Suppose you want to print invoices for a bunch of customers, and the invoices typically are more than one page in length.  You could set up reporting runs in a loop, printing one report for each customer.  Or, more efficiently, you could set up one reporting run with an explicit page break on customer.  If you take the more efficient approach, you have to figure out how to page-number each customer's invoice pages.

So what do you do?

This topic comes up fairly frequently in various RS fora.  Chris Hayes came up with one of his Sleazy Hacks (hey — that's his name for the blog, really!) to allow a group-centric page number value, but not the group-centric page total value (the "X" but not the "XX", in other words). I built on Chris's solution to do the other part in response to a forum question.

A central tenet of my part of the solution is that you can preprocess a report to gather all the values you need and save them out to a collection somewhere. In the example you'll see in my posts in that MSDN forum thread, I illustrate by writing out to a text file even though I knew that, in real life, you might want to write these values to a database table and I might prefer an XML format for my collected results.  As a result, you can reference your collection of values at the time you need them, when rendering the "real" output.

What else is this technique good for? 

I can almost hear you saying ">L<, that's a truly wacky idea. Whyever did you think of it?"

Well, as you might have noticed if you looked at that post, the sample output I provided was really very simple.  It only has the group break value and the group page total value in it. Obviously in real life you might choose to dump a whole lot more reference-able information in there. 

If I actually used this technique to provide group X of XX page numbering myself, I would probably have had more polished illustrative custom functions, showing how I gather additional elements as well.  In real life, I haven't ever used this technique for Page X of XX in RS, so I had to make up those functions pretty quickly with the appropriate values.

But I certainly have used it for debugging! Instrumenting a report to find out when things happen, in what order, and what values certain items might have at certain moments, is a priceless strategy. The techniques for doing so are contained in the same basic code you see in those posts.

I often write report textbox expressions that look like this:

= <real report expression here > &
   Code.MyInstrumentingFunc(<params here>) 

The functions I use for performing actions, rather than providing any services to renderers,  like this one, typically Return "". They don't interfere with the actual report expressions at all. They usually don't even interact with the report expressions, although once in a while I do concatenate such a function before the true report rendering expression. This way that the function can adjust some code variables just before those variables are used in creating the report expression.  And of course, you can concatenate more than one such function in with the expression, in whatever order you need.

I find it's useful to create a hidden Debugging parameter in reports to make sure that the custom instrumenting function is not invoked when it's not needed.  That way you can write something like this:

= <real report expression here > &
   IIF(Parameters!IsDebug.Value,Code.MyInstrumentingFunc(<params here>) ,"")

… not quite as efficient as removing the extra part of the report expressions entirely, or as having a true compilation-time strategy, but it's pretty good.

Hello? 

Anybody else do this type of thing? I really don't know if this is a common practice or just me.  

I have some other techniques that I use, requiring this type of Debugging parameter, which I'll share in some future post.