Ramdas writes to ask about a visual effect, something we usually call the "greenbar style" report: alternating row colors.  The name comes from the old greenbar printout paper, as you probably know.

I have a question regarding background colrs in SSRS, I have a report which has three groups on the row groups section. I want to alternate the colors for the rows in the report. Any thoughts on this…

I was nonplussed to be asked about this greenbars are pretty simple to do, usually.  They are a staple pattern in report design, and people come up with all kinds of simple ways to manage it (for the record this article overcomplicates, you can often do it with a modulus operator and the RowNumber(<proper scope>) RDL function, with no code at all, but it presents a common method, read it for a complete discussion).

But Ramdas' case was a little different.  As you see here, he's got two adjacent row groups, one of which has a child group (don't ask me the significance of the group names, these come from the actual report). 

The report row layout contains group summary lines, on the different levels.  In the next screen shot I'll make the rowheight a little higher than it really is, to make this a bit easier to read:

The problem that Ramdas wants to solve is to make all these different types of lines (not including the two empty separator lines you see above [RulePriority] in the layout) form a single, seamless greenbar table.  To do this, he created some group variables, one for each row group level, and filled each one using a separate Code Variable and function (i'll show the entire technique for one of the three groups, with every place it "touches" the RDL in one not-very-attractive montage, to save time):

 So, okay.  What to do?

Wait. What exactly are group variables for?

Group variables are relatively new, and they're .... nice but nubbly, in the immortal words of Rudyard Kipling.

Incidentally I was thrilled to see an http://www.nicebutnubbly.com/ and even more thrilled to read and see its lovely content, especially since it looks like the proprietress is a neighbor of mine here in Northern California. I do wish she'd name her source, though; that really is one of my favorite Just So Stories.

Ahem.   The Help file has the following fascinating description of Group Variables:

"A group variable is evaluated when the first group instance is processed and can be referenced from an expression in the scope of the group or its child groups. Because the variable is only calculated one time, each reference to the variable always uses the same value. "

When exactly is the first group instance processed? And where exactly is this processing, in relation to other processing that is done at runtime to calculate and run the report?

Do you know the answer?  Because, so far, I don't.  Even when I'm using groups, I have a hard time using group variables.

The sequence at play is the thing

Being the Humpty Dumpty of report authors, I'd rather use Code variables, and set their value exactly when I want to.  At least I know what is going on, and I've done a fair amount of testing to verify it.

So, for iteration #2, I removed all the code in Ramdas's report and left only a single function: 

Public EvenRow As Boolean

Public Function AltRow() As String
   EvenRow = Not  EvenRow
   Return ""
End Function

Then I overloaded the textboxes in column 1, attaching this function to it before the "real" label in the textbox, like so:

=Code.AltRow() & Fields!RulePriority.Value

Then I put a slightly different expression on BackgroundColor for the textboxes in the relevant rows:

=iif(Code.EvenRow,"Gainsboro","White")

As you can see, it didn't exactly work, but it told me exactly what was going on:

Yuck. The text for the textboxes in column 1, including the function, was being evaluated after the background color for that textbox, but before the background color for the rest of the columns.

Right?

No problem, sez I...

... All we have to do is reverse the expression for the textboxes in the first column, for all relevant rows:

=iif(Code.EvenRow,"White","Gainsboro")

... and it was the morning and the evening of the third iteration.  And we looked at what we had created, and saw that it was good.

Not so fast.

(Be patient, you're learning something important here.)

When you count on the sequence of events in a report, you'd better make sure that the sequence will work the same way in all relevant renderers, or at least the ones you get in-the-box with SSRS.  In this case, since it's a visual effect, we don't care what it looks like in CSV or XML; background color simply won't translate.  PDF looked good when I tested... but Iteration 3 gave the same unfortunate results in Word and Excel renderers as you see above for Iteration 2. 

What can we count on, what do we know will be evaluated at the right moment?  That is: what will be evaluated by all renderers that have any use for background color, before any background color is rendered?

In this case, I chose the Visibility expression.

Overloading the Visibility expression is as easy as overloading a textbox

Here's the simple Visibility expression I used on the three rows that need to toggle the value of Code.EvenRow:'

=Code.AltRowVariant()

 ... and here's the simple variation on the code function:

Public Function AltRowVariant() As Boolean
   EvenRow = Not  EvenRow
   Return False ' no rows are hidden
End Function

... and our background color expressions go back to the consistent instruction we used in iteration 2, for all textboxes in all relevant rows:

=iif(Code.EvenRow,"Gainsboro","White")

And whaddya know, Iteration 4 works fine in all the appropriate renderers: Word, Excel, Tiff, PDF, MHTML, the works.

The moral of this Just-So Formatting Story

You knew I was going to say this, I hope, but just in case: I happened to be working in RS 2008 R2 here, and I don't guarantee the results in 2005 or even 2008 R1.  Why not?  Because I have not tested it there, that's why not.  And the sequence of events in a report is not something to guarantee lightly or without testing.

Until then, with apologies to Edwin Starr, I'll be singing

Var, huh, group var
What is it good for
Absolutely nothing
Listen to me 

And that, my children, is why I don't use group vars very much.  At least for now.

RamdasGreenbar.zip (30.64 kb)

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.