I was excited to see that RS 2008 R2 offered some new properties associated with pagination. While my unintended long-running series on renaming Excel Tabs using XML output and XSLT has received a gratifying amount of attention, it’s great that Microsoft has improved the product by providing a native way to do this simple task without heroic measures.  “It’s time,” I thought, “to put this baby to rest forever, and let everybody know about it.”

First: the magic works!

As you remember, the Excel rendering extension chooses to recognize explicit page breaks that you set, on a group or container object level, and to interpret them as separate tabs in the exported workbook.

If  you don’t recognize, or don’t understand, the term “explicit page break”, read another post first, such as this one.  I’ll assume that’s water under the bridge. in the current context.

So, now you can define a page name expression at the same time as you are defining a page break.  Brilliantly appropriate.

In the example I’ll use to illustrate, I have a report on answers to questions in a survey.  There is a container group on Question ID, and I am setting a page break on that group level.  You can see the expression I’m using “attached” to the other Page Break attributes for the group, in this screen shot (which uses Report Builder 3.0, but you get the same thing in R2 BIDS Report Designer):

 

Great!  And here’s the result in the RB Preview export to Excel:

Second look: uh-oh…

Hmmm.  There are a couple of things wrong here.

First, you notice that there is a QID42 (2) tab.  It’s cool and all that the Export renderer “knew” that tabs have to have unique names, and added the (2) without direction.  But why that name at all?

This isn’t because Question #42 actually spans two pages; remember that Excel will only deal with explicit page breaks, and no matter how much data QID 42’s group had, no matter how many pages it took to print or show in HTML, that group would all be on one Excel tab.

There does happen to be a second container, outside the one housing these question groups, which provides summary data.  Here’s what’s actually on the QID42 (2) tab:

… so the first thing to remember is: be very careful to assign PageName information for every page that is going to exist, or you’re going to end up with some strange behavior.

In some quick tests, I saw some strange and inconsistent behavior even when I did remember to assign this information throughout — in the test above I had an Initial PageName assigned, which I hoped would provide a global default for regions with no PageName assigned explicitly. Figuring out whether nested groups will concatenate their PageName info, etc, will take some time, and there may be some bugs in complex scenarios. 

Next, of course you notice the weird effect of part of the table being “dropped” below the rest.  While you can’t see it in the page layout, the “Answers” table is actually two overlaid tables.  (The initial values and percentages are in a separate table for reasons that have no bearing on our current discussion — just take it as read that there is a good reason.)

Here’s the intended result, as you would see it in HTML Print Layout display and also in a PDF, which is this report’s intended output:

We’ll always have plaster of paris

So what went wrong here? The truth is that there are LOTS of things, not just tab names, that we need to do better than the Excel default renderer.

Luckily, XML output + XSLT are still going to provide the correct behavior for us when we need it. Getting the Initial values to show in the correct rows in the Answer table is no trick at all in SSML.

I guess I’ll continue to use my original technique for its original purpose in this report. For others, of course, if tab name is the only issue, I’ll be happy to switch.

Reconsidering in depth

If you are planning to use the new PageName feature, let me caution you about a couple of additional things.

1) As mentioned above regarding unique names, the Excel renderer is going to try to keep you out of trouble with tab names.  In addition to a tie-breaking digit, it’s going to ensure that characters not allowed in a tab name are removed from your expression, and also ensure that your tab names are not longer than allowed by Excel.  Here’s a bad example of a PageName expression, plus what you’ll get in Excel as a result of this expression:

 

 

 … I am particularly interested in how StrDup(20,”This is a Test”) became what you see above in the tab names.  Really, I haven’t a clue what logic they’re using and am not in the mood to test exhaustively to find out.

2) It’s a little annoying that the global PageNumber is not available in these expressions. 

… since “page number” would equate to “sheet or tab number” in Excel, really, I see no reason why the Excel renderer couldn’t use this information safely.  (Does anybody know a different way that PageName gets used outside the Excel renderer, so far, btw? Why give this error message, why not just provide a caveat for renderers that can’t use it?)

Since, in my example, report, a page number equates to a group number, there are workarounds, of course.  There are always work arounds.  Wah.

A small tear drop in a very big bucket of smiles

R2 brings lots of goodies. I’m glad MS found time to throw this one into the mix, and it’s for sure a welcome addition.

Not a game changer, as it happens.  But R2 does bring us some of those.