Dynamically change background color in SSRS reports using custom code

Question

Dynamically change background color in SSRS reports using custom code

Stan Kulp-439977

SSChampion

Points: 10062
More actions
October 26, 2014 at 9:48 am

#300505

Comments posted to this topic are about the item Dynamically change background color in SSRS reports using custom code

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

High Marks for you, Stan. Low marks for SSRS, though. I can't believe that there's nothing built in for this very common requirement. It's amazing how much code you had to write in at least three languages to get this done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tango66 SSC Enthusiast Points: 104 More actions · Answer 2

This is a good example of creating a custom code in a report, but for this, particular task - to set a "greenbar" effect on a list, this approach IMHO a bit complicated. You can achieve this by simply adding in the row properties -> Background colour expression something like:

=IIf(RowNumber(nothing) mod 2 = 0, "No Color", "SomeColor")

Spiff SSCertifiable Points: 7826 More actions · Answer 3

tango66, I think the point was to allow alternate colouring to distinguish groups of values rather than the classic line by line greenbar. However this is still possible with an expression in the Background Color:

=Iif(

RunningValue(Fields!Ticker.Value,CountDistinct,"DataSet1") Mod 2 = 1,

"White", "WhiteSmoke"

)

To be fair, this version wont work correctly if you have a different sort order than ticker. Stan's will work with different sort ordering, so if you add, say interactive sorting by Date, you'll still get the alternating bars when the ticker changes.

jbowers Old Hand Points: 369 More actions · Answer 4

Good article, and I agree with the earlier replies that the code to conditionally set the background color of a row can be easily achieved with one expression on the detail row's background color property.

What I was hoping this article was going to explain was how to toggle the color of rows in a grid on the client side, after the report was rendered. (Think of a javascript solution using mouseover and mouseout, or a CSS style using "hover"...) This sort of row highlighting is very helpful to our users, and I've searched in vain for a way to accomplish it in a .rdlc report. Many thanks in advance to anyone who can point me to a working example.

w.herbert SSC Enthusiast Points: 107 More actions · Answer 5

Great little tool - thanks Stan!

I was looking for a very similar treatment on a recent BI project; a bit of a tweak to this would have gotten me there - and saved a lot of time.

Agreed - funny that MSFT doesn't just create a library of this stuff - maybe it's too "useful" or something.

That's why I use Tableau whenever the client has it installed - so easy to get colorful reports and highlight / differentiate to your heart's content.

dmbreth SSC Enthusiast Points: 127 More actions · Answer 6

Where is GetColor() defined? Could you not set the entire row at once instead of textbox by textbox? Thanks.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 7

dmbreth (10/27/2014)
Where is GetColor() defined? Could you not set the entire row at once instead of textbox by textbox? Thanks.

Yes. I mainly wrote this as a demonstration of writing custom code. When I haven't written any custom code for a couple of years I forget how to do it. I am sure there are lots of better ways to toggle the colors.

marty.seed SSCrazy Points: 2661 More actions · Answer 8

Thanks for this, it's something I have been trying to accomplish for a few weeks with a report that the normal greenbar code couldn't handle.

trevor.adams SSCommitted Points: 1505 More actions · Answer 9

It's all good. Sadly on my monitor I can't distinguish white from whitesmoke. I think for the purposes of this demo, I would have gone for less subtle colours:-)

w.herbert SSC Enthusiast Points: 107 More actions · Answer 10

Solution -

Simply type in "yellow" or "magenta" where it says "whitesmoke" and you'll see the contrast.

gaurav.rawat90 Valued Member Points: 63 More actions · Answer 11

Excellent article thanks!! now i've resolved the issue.

Bert-701015 SSCommitted Points: 1615 More actions · Answer 12

Great article. I modified it to cycle through 6 different color changes:

Public Dim Color As String = "#C6E0B4"

Public Function ToggleColor(ByVal PreviousTriggerColumnValue As String, ByVal CurrentTriggerColumnValue As String ) As String

If PreviousTriggerColumnValue <> CurrentTriggerColumnValue Then

Color = GetNextColor()

End If

Return Color

End Function

Public Function GetColor() As String

Return Color

End Function

Public Function GetNextColor() as string

Select Case Color

Case "#C6E0B4" 'Green

Return "#B4C6E7"

Case "#B4C6E7" 'Blue

Return "#D9D9D9"

Case "#D9D9D9" 'Grey

Return "#F4B084"

CASE "#F4B084" 'Peach

Return "#FFD966"

CASE "#FFD966" 'Peach

Return "#FFF2CC"

CASE "#FFF2CC" 'Peach

Return "#C6E0B4"

End Select

end function