Conditional Formating in SSRS Report Builder 3.0


I am building reports in SSRS and delivering those reports via SharePoint Integrated Mode reports library. Most of it is relatively standard stuff, but I recently had cause to do some conditional formatting. When I hear conditional formating of data, I think of things like color coding data based on the value of the data in a table. And a good discussion of this is here.  That does a terrific job of explaining the concepts, but my client set requirements to format an element’s size.  Specifically the element must not extend the box it is contained in.

In this case I had an expression in a Text Box.  The expression returns the values of a parameter as it is rendered when the report is run.  Problem is when the parameter value is multi-selected the value extends the text box down beyond the graphical layout and messes up the entire report.  I searched the inter-tubes for an answer but could not find on…so I finally just crafted it myself.  As first I thought that counting the values passed as parameters ought to work, i.e. if there were 1 value passed, then set the font size to 48pt, if there were 2 values passed, then set the font size to 36pt, etc…well that failed miserably, but with some collaboration with a collegue, I tried it out with len(Join(…

=Switch(Len(Join(Parameters!DRS.Value,”, “)) < 18, “48pt”, Len(Join(Parameters!DRS.Value,”, “)) < 36, “36pt”, Len(Join(Parameters!DRS.Value,”, “)) < 48, “24pt”, Len(Join(Parameters!DRS.Value,”, “)) < 100000, “18pt”)

and low and behold it worked, like a champ.  Problem is that now I have a client who knows I can figure out how to fix / work around these problems.  While SSRS Report Builder was not built with this specifically in mind, some creative engineering makes it work.

Advertisements

Published by

wattersbill72

Dad, Husband, Brother, Son , Soccer Player/Fan, SharePoint Person, All Around Geek

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s