Walkthrough, Part I: Dynamic interactive paging and another fierce look at parameters

It's hard to believe how many questions come my way involving dynamic page breaks and parameters.

(Including yours, Philippe.  You're next.  And yours was 'way easy compared to this one… <g>)

Karen Grube came up with an interesting scenario and some specific constraints, requiring some special handling. I'm going to paraphrase a bit, to try to bring out the generically "interesting" bits, and walk through the whole problem, and I hope you find that some parts of it are useful to you.

Here's the deal:

• She's using RS 2008 and an ASPX page with a ReportViewer control on it to display reports to users 
• She has hundreds of reports, with a common setup, as follows:
  • a standard landscape-orientation page size for rendered output (such as printing, Excel, PDF)
  • a standard expectation on users' parts to see the HTML content, hosted in the ReportViewer control, un-paginated, but
  • a rule that the unpaginated content must be forced to paginated output if the dataset has > a certain number of rows, and
  • an inability to extrapolate a probable number of output rows from the user-chosen parameter values. 

The last condition is really the killer.  Presumably, she has a large number of parameters, and they may be quite different for each report.  She is not allowed to do a preprocess call to the db-backend from her web tier in order to decide whether the html-rendered content should paginate or not.  She won't know until she's actually in the report.

So, previous to talking with me, she had settled on a methodology something like this:

• Use a parameter called ShowAll for the user to indicate their preference for browser-hosted display 
• Set the Interactive Page dimensions the same way as the export/standard page dimensions 
• Create two overlaid tablix layout controls, one with "Keep Together" set true and one with the same property set False
• Bring the data into the report, and, at that point, evaluate ShowAll and also whether there are too many rows to allow un-paginated display in the browser,
• Hide/show the two tablix controls based on this evaluation.

This technique works, but causes a couple of problems:

• Two tablix controls to maintain, for each report, is kind of a PITA.
• There are two textbox controls following the table-format content, and these get forced to the next "page", which is not optimal.

What Karen would really like to do is set either "Keep Together" or Interactive Page Height dynamically.  She knows that the right way to get un-paginated content in the HTML display is to set Interactive Page Height to 0 — and I hope you do too.

Unfortunately, neither KeepTogether nor InteractivePageHeight can take an expression.  And, remember: she can't adjust the RDL dynamically in this case, because she doesn't know how many rows will end up in the report before actually gathering the data into the report.

So… what to do?

Stand the problem on its head.

Since the right way to get "flowing", un-paginated content in the HTML display is to set Interactive Page Height to 0… let's do that.

But wait.  What happens when the user doesn't want ShowAll to be on (the user prefers pagination)?  And, more significantly, what happens if the number of rows is greater than the allowed maximum?

We can use dynamic page breaks for that.

But, wait.  Won't dynamic page breaks interfere with the correct pagination for standard  export content?  (Karen's rows aren't all the same height, so we shouldn't try to figure out a particular number of rows per PDF or printed page.)  Well… they might.  But we can control the export content ourselves and make sure that the export content always behaves as if ShowAll is False, so our explicit page breaks don't interfere with the "natural" ones in each renderer. 

And how are we going to do that?  Aye, there's the rub.  Karen thinks it's easy to mess around with parameters at exactly the right moment.  It turns out, it's not easy at all.   That's why this walkthrough has two parts.

At least the textbox problem completely goes away without any work at all.  Small comfort.

So, bear with me, and we'll start our walkthrough now.

Mise en place

Karen's a C# programmer, so I'll use C#.  I'm not great at it, don't laugh. I'm sure you'll do better.  But I'm going to fully work through this problem, no matter how silly the code I use, because I think you will miss some niceties if I don't.

I'll also use the RS SOAP API here, for no good reason except that I rarely do, but I figure Karen will probably be happier with it than REST.

My simple report of course has only one tablix in it, not two, followed by a couple of text boxes with aggregated values to match Karen's scenario.  It has four parameters, as follows:

I'll use my usual simple SQLWorld database borrowed from the mySQL community, because the data really isn't the point in this walkthrough. Here's the query I used; you can see how RowLimit stands in for Karen's query requirements when I test:

                SELECT TOP (@RowLimit) * FROM SQLWorld.dbo.City

Ça marche… 

Now the fun begins.  The sample report with the parameters above is simplicity itself.  This screen shot is from ReportBuilder 2.0; there is really nothing in this that would be easier to do in Report Designer than the Builder.

The only weird thing you'll see in the report — and it isn't really pertinent to Karen's scenario in particular — is that I have replicated the tablix header row in the page header. These two extra textbox controls each have the hidden/Visibility expression =Globals!PageNumber=1, so they stand in for the tablix header on subsequent pages. 

What are they for? Frankly, sometimes I get tired of trying to figure out how various renderers will "react" to the various layouts and attributes that tell a header whether it should re-appear on additional pages, and when I'm in a hurry I just use this dodge instead of testing all the possibilities.

Also frankly, I tend not to use KeepTogether; I generally don't understand some of the logic behind some of these options. I'm impressed to see that Karen was comfortable with its behavior and is using it successfully. I'm not sure how each renderer will treat it, what small variations in page size will do, and rarely rely on it if I don't have time to test exhaustively. As you'll see, it's not relevant to my version of the solution.

The "regular" page layout size for my solution matches Karen's. We plan not to mess with exported renditions, which follow those dimensions as appropriate to each format, remember? My Interactive Page Width is the same as Karen's too. (Don't be fooled because the layout you see in the layout window is much narrower than a landscape report; as long as your laid out width is narrower than your intended result, with a comfortable buffer for margins, you should always be fine. But the Interactive Page Height is 0.

A soupçon of saffron… 

You also see — and the "tip" textbox in the page header calls your attention to it — that I've added a group to this report. Here's where we control the pagination.

I've deleted visible columns from this group, and set it to page break "between each instance of the group".  I've used the following group break expressinon:

=IIF(Parameters!PerPageLimit.Value =-1 OrElse Parameters!ShowAll.Value,
       Nothing,
      Code.BreakGroup(RowNumber("Tablix1") Mod Parameters!PerPageLimit.Value =0))

Okay, let's parse this instruction: if our current number-of-rows per page has not been explicitly limited or the user has indicated a preference for un-paginated content, never break.  Otherwise, decide when to break based on a simple code function, which I've embedded in the report. (It's completely generic, so there's no reason you couldn't put it into an external DLL for use by all your reports if you prefer.) Here's all the code:

Dim MyGroup As Integer = 0  

Function BreakGroup(ByVal TimeToBreak As Boolean) As Integer
   If TimeToBreakThen
      MyGroup = MyGroup + 1 
   End If
   Return MyGroup

EndFunction  

The simple logic above isn't 100% perfect.  For one thing, it's based on a number of rows per page, and we already know that Karen's rows-per-page could vary based on stretching row heights.  For another thing, I haven't bothered accounting for the fact that the Mod break expression will probably have one less rows-per-page on the first page than others; you could add Globals!PageNumber.Value into the mix here, and correct as needed, if you want.  But it really doesn't matter.

This logic is only there for interactive page breaks in the browsed HTML version and, as such, the exact height of the page isn't important.  We just want to limit it.  If the limit is, say, 250, but the first page only has 249, or if the height of 250 rows is a couple of pixels more or less on different pages, we don't really care.

For rendered pages in other formats, we're simply going to make sure that the value of PerPageLimit is always -1, so the renderer's natural behavior for the page size will "take over" and control the page breaks.

How are we going to do that?  Aha.  This is where life gets really interesting.

Define interesting.

OK, I will.  But it's going to take a whole 'nuther walkthrough (I write long posts, but this would be ridiculous) and a lot more code. 

It's still the right way, and the code is in the right place for Karen to use her own tier (and her type of skills) to take care of business, for all reports at once.