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):
|
With me so far? Unfortunately, the group variables aren’t behaving as planned. Each of the groups is alternating rows on its own schedule, they’re not all orchestrated as one greenbar list. To make this easier for you to see, I doctored Ramdas’s report simply by replacing the required columns with some random data from master.sys.all_objects and uploaded the 4 RDLs. In the first iteration I’ve only removed the nonessentials, Ramdas’s original code and variables are intact but I’ve recreated the necessary data. You can see the issue in Group1, which uses the column RulePriority, here represented by type_desc in the all_objects view: … while each Group1 row alternates with the last Group1 row, it doesn’t flow smoothly with the Group3 rows that are its children. |
|
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.