Conditional Formatting in SSRS

You can use conditional formatting on values in any field or fields for both the font in the field or the fill color in the field using the switch function.  To apply conditional formatting on the fill color of a field, right click the text box of this field and select Text Box Properties (you can also use “Color” in the Properties window on the right hand side).  Select Fill and then click the Fx function button.   Use the switch function to create the range that you want.  Although green, yellow and red can be used, you may want to use a lighter version of these colors since they are intense to the eye and often not printer friendly (i.e. “LightGreen”, “LightGoldenrodYellow”, “LightCoral”). 

You can also format the entire row of data rather than one text box containing the TRANS_AMT data.  Simply apply this same fill to all text boxes in your matrix or table and the entire row will be colored.  Instead of coloring the background fill color, you can leave this as transparent and use this same switch function on the font.  Select the Font rather than the Fill on Text Box Properties and Fx button to the right of Color and paste in this same switch function.  Light colors don’t show or print as well as darker colors so you may want to bold these values or use darker versions of them. 

One other option is to use a parameter to assign colors.  The parameter can be either visible where to the user plugs in values and the colors are assigned based on these values or internal but listed in the the header or footer of the report.  If you make the parameter internal, the report consumer can request that they be changed.  You can then go  in and change  the parameter value and since the header or footer refers to this parameter, the parameter value listed will change and the report consumers will know that there is a new parameter value. 

To do this, create an internal parameter called Threshold or some other name. Click “Default Values” and “Specify Values”.  Type in a value such as 500 and then OK.  Right click the text box that you want to apply the conditional formatting to and select Text Box Properties.  Note the text box name (i.e. Textbox5) on the General tab. Select Fill and then the Fx to the right of  Fill Colors.  Click the Fx button and paste in the following code.  If the Threshold parameter value is greater than equal to twice the value of the parameter (500 x 2 = 1000) then the fill color becomes Light Green.  If it is greater than or equal to the parameter value making it between 500 and 999, the fill color is Light Golden Rod Yellow.  Otherwise it is Light Coral or less than 500.  Add this parameter to the header or footer as well.  If you ever change the parameter, the definition automatically changes.  This will keep your definitions in sync with your conditional formatting and make it easier to change.

About these ads
This entry was posted in Uncategorized. Bookmark the permalink.

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