Saturday, September 4, 2010

Conditional Formatting in SSRS 2008

Alternate Background Color in Tabular Report:
Alternate background color of rows in a table make it easier to read horizontally. Also it makes report look and feel better. This is very common but simple task to achieve in SSRS report using expression.

Select the detail row of the data table in the layout tab.
Go to property window and select expression of BackgroundColor and enter below expression:
=IIF(RowNumber(Nothing) Mod 2 = 0,"Transparent","#cdfcbe")

Now click and Preview the report:

Format Phone number using REGEX function:
Suppose you want to display Phone no in (ddd) ddd-dddd format instead of ddd-ddd-dddd. To do this, Right click on the required textbox --> click expression... and enter below expression:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")


Now click on Preview and see the report:
 
Changing Font Color dynamically:
Sometime we need to format data based on the their values. Below is an example for Base rate color selection:
Base RateColor
<= 15 Green
> 15 AND <=25 Blue
> 25 AND <=40 Black
> 40 Red

To do this, Enter C (Currency format) in Format and below expression in Color property of BaseRate field:
=SWITCH(Fields!BaseRate.Value <= 15,"Green",

Fields!BaseRate.Value <= 25,"Blue",
Fields!BaseRate.Value <= 40,"Black",
Fields!BaseRate.Value > 40,"Red")

Now click on Preview and see the report:

Swapping Font Color and BackgroundColor:
Suppose you want to swap Font Color and BackgroundColor of Gender field. Use below expressions in BackgroundColor and Color properties respectively:

=IIF(Fields!Gender.Value = "M","Tomato","Teal")

=IIF(Fields!Gender.Value <> "M","Tomato","Teal")

Now click on Preview and see the report:

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting Qlikview Online Training India

    ReplyDelete

Here are few FREE resources you may find helpful.