Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

More conditional formatting in SSRS

In a prior post, I demonstrated how to use conditional formatting techniques to change the background color of every other row in tabular report. In that example, I used the Immediate If (IIF) function to change the BackgroundColor property of the odd rows to silver.

The IIF function only allows for two states. In my example, the odd rows were silver and the even rows had a white background color.

In the comments section of that post, one reader, Wally, asked to have more than two states, silver, white, and red. Another reader, Darin, replied that you can nest your IIF statements. You can read the comments here. Thanks, Wally and Darin! I love with people comment to posts, especially when it's in the spirit of helping each other.

Nesting IIF functions is commonly done. I've done it many times. However getting more than three states can get rather messy. You have multiple IIF functions nested, each with its own set of commas and parenthesis. It's very easy to mistype something and have the whole thing not work.

Fortunately, SSRS supports other VB functions that we can use to create more complex conditional formatting settings.

Let's consider a rather simple Sales Report as an example. In the following report, we have a report with three columns of information - the Sales Order Id, the Order Date, and the Sub Total for the Sales Order.

SSRS_TriState1-2008-08-20

Now let's say that our requirements for the report dictate that the Sub Total amount must be shown in a different color depending on it's value. If the Sub Total is < $1,000, the figure should be shown it red. If it's between $1,000 and $10,000, the figure should be displayed in blue. Between $10,000 and $30,000, in green. And finally, all Sub Totals over $30,000 should be in black. Yes, I know that these requirements may not make much business sense, but such is the case with some requirements.

Anyway, to implement this more complex formatting requirements, we can turn to another VB function, the Switch function. The Switch function takes a series of argument pairs. It returns the first value immediately after the first condition statement that evaluates to true. It may sound confusing, but it's really rather straightforward.

To implement our requirements, select the Sub Total textbox. In the Properties window, find the Color property and select <Expression...> from the dropdown list.

In the Edit Expression window, enter the following function.

 

 SSRS_TriState2-2008-08-20

The Switch function will return Red, Blue, Green, or Black depending on the value of Fields!SubTotal.Value. The first expression to evaluate to true will be returned.

Press Ok to save the expression and Preview the report.

SSRS_TriState3-2008-08-20

The Sub Total column changes the Color property according to our requirements.

I hope you find this useful. Let me know in the comments section if you've found some other particularly useful VB functions to conditionally format a report.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Wednesday, August 20, 2008 9:42 PM | Filed Under [ Reporting Services ]

Feedback

Gravatar

# re: More conditional formatting in SSRS

I agree entirely that nesting can get messy. Thanks for the tip on Switch - I had forgotten about that.

It is worth noting that most of the Appearance properties support expressions so you can change the font weighting, background color, etc. You could get carried away though, so it is better to keep things simple.

You can also use conditional formatting to display up/down/nochange arrows for a quick and dirty KPI report. To do this drag an image control from the toolbox to a spare column. I make the image source "web" and then use some images on our intranet. However, let's google some, and assume you'll create your own.

e.g.
http://m1.smartmoney.com/framework/images/9x5_up.gif
http://m1.smartmoney.com/framework/images/9x5_down.gif
http://m1.smartmoney.com/framework/images/9x5_noChg.gif

Change the sizing to Autosize

You can then open the expression builder on the "value" column and then use Switch as above

e.g.

=Switch(Fields!SubTotal.Value < 0, "http://m1.smartmoney.com/framework/images/9x5_down.gif",
Fields!SubTotal.Value = 0, "http://m1.smartmoney.com/framework/images/9x5_noChg.gif",
Fields!SubTotal.Value > 0, "http://m1.smartmoney.com/framework/images/9x5_up.gif")
8/21/2008 12:53 AM | Darin
Gravatar

# re: More conditional formatting in SSRS

Also look at "MOD", based on the resullt you can take approprate action

8/21/2008 10:00 PM | PP
Gravatar

# re: More conditional formatting in SSRS

Another option is to write a function to do this and an example below involves a case statement.

In the color property of the textbox, you can reference the function like so:

=Code.GetColour(Fields!SubTotal.Value)

If you had multiple textboxes on a report that required the same colour formatting, this technique would be a bit more reusable.


Public Function GetColour(ByVal InputValue As Double) As String

Select Case InputValue
Case Is &lt; 1000
GetColour = &quot;Red&quot;
Case Is &lt; 10000
GetColour = &quot;Blue&quot;
Case Is &lt; 30000
GetColour = &quot;Green&quot;
Case Else
GetColour = &quot;Black&quot;
End Select

End Function
8/26/2008 5:10 PM | Tim Leung
Gravatar

# re: More conditional formatting in SSRS

Does it work to use the SWITCH statement in the sort of a table or list then create a parameter with a list of options for the user to select? The situation I have is a request for sort orders on a directory style report so interactive sorting doesn't seem the best option. I'm already presenting the user with a drop down list of sort options, but I'm using an IIF statement.
8/27/2008 8:24 AM | kevin
Gravatar

# re: More conditional formatting in SSRS

I'm not 100% sure that I'm following what you're trying to accomplish. But, if you've got it working with a series of nested IIFs, then you should be able to replace those with the Switch function without much effort.
8/27/2008 4:23 PM | Joe Webb
Gravatar

# re: More conditional formatting in SSRS

Nice one! Thanks
9/30/2008 8:13 AM | Kraaitje
Gravatar

# re: More conditional formatting in SSRS

IF each Sale Order ID have got its own tagert, How to I go about getting the conditional formating that does not look on the overall Sale Order ID but on the individual target.

I will highly appreciate your an help
8/19/2009 8:41 AM | Pitso
Gravatar

# re: More conditional formatting in SSRS

I need to display text in anti clock wise format in ssrs 2005. Please anyone help..







Thanks in Advance,
Gayathri :)
11/2/2009 5:24 AM | Gayathri
Gravatar

# re: More conditional formatting in SSRS

I need to change color of a row based on the maximum value. For example-

Row1 100
Row2 200
Row3 500
Row4 50
Row5 300

In the example above, Row4 would be colored differently (for instance red) because it has the lowest value, and Row3 would be colored differently (for instance green) because it has the highest value.

If I use the Min () and Max() functions of SSRS in the expression to color the background, I get the following error-
Aggregate functions can be used only on report items contained in page headers and footers

Any help here please?
Thanks in advance
2/9/2010 6:18 AM | Anita
Gravatar

# re: More conditional formatting in SSRS

this is a nice little tutorial that helped me a lot, thank you!
6/16/2010 12:03 AM | beste
Gravatar

# re: More conditional formatting in SSRS

Thanks beste! I'm glad that you've found it useful.

By the way, I've moved my blog to a new location. I'm now blogging at http://www.webbtechsolutions.com/blog.

Joe
6/16/2010 6:08 AM | Joe Webb
Gravatar

# re: Can we format the parameters textbox size,color, etc..

When we are adding parameters to our reports. by default all the parameters are arranged according the order that too only two parameters textbox on 1 row. Can't we change the format of diaplaying parameters.

Thank in advance
7/21/2010 12:07 AM | mani
Gravatar

# re: More conditional formatting in SSRS

To implement our requirements, select the Sub Total textbox. In the Properties window, find the Color property and select <Expression...> from the dropdown list.
10/16/2010 3:53 PM | columbia jackets
Gravatar

# re: More conditional formatting in SSRS

Fortunately, SSRS supports other VB functions that we can use to create more complex conditional formatting settings.

furry boots | womens snow boots | columbia outlet | columbia sportswear jackets | the north face jackets | north face jacket | cheap mac makeup | discount makeup
10/19/2010 4:18 AM | snow boots for women
Gravatar

# re: More conditional formatting in SSRS

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free video converter for Mac, This video converter for Mac is more stable in converting video files and support more video formats.(even HD videos, such TR, TP,
10/26/2010 1:26 AM | hanly
Gravatar

# re: More conditional formatting in SSRS

Hi,

I need to change the color of individual cell based on the rows

Ex: I will be geting order date, order number, order city, order cost for the reports. I will be ordering the report by order date
in that report if a city appears three ime sequentially then I need to highlight those three cell with different color

please give some suggestions
11/8/2010 12:07 PM | Anvesh
Gravatar

# re: More conditional formatting in SSRS

I need something like this to flag all my exceptions... syntax not correct, but you can see the logic...

It would check the status type, then use criteria accordingly to turn it red.
=IIF(Fields!Status.Value ='FT' AND < 40, "Red", "Black")
=IIF(Fields!Status.Value ='PF' AND < 32 OR > 39, "Red", "Black")
=IIF(Fields!Status.Value ='PT' AND > 32, "Red", "Black")

It would act on a field called Fields!AVERAGE.Value

The data looks like this

Status | Average
FT | 39.5
PF | 31
PT | 29



11/15/2010 5:40 PM | Scott
Gravatar

# re: More conditional formatting in SSRS

put Switch(IIF(Fields!Status.Value ='FT' AND Fields!AVERAGE.Value<40, "red","black")

or Switch(Fields!Status.Value ='FT' AND Fields!AVERAGE.Value<40, "red",Fields!Status.Value ='FT' AND Fields!AVERAGE.Value>40,"black")

Do same for the rest.
1/20/2011 5:09 AM | Kay
Gravatar

# re: More conditional formatting in SSRS

Is there a way to apply and/or to the conditionals? For example I have a condition that if it is nothing or 2 that a certain state exists. I'd rather not do to separate statements.
2/8/2011 11:01 AM | Mike
Gravatar

# re: More conditional formatting in SSRS

NICE
5/23/2011 9:33 AM | Narasimha
Gravatar

# re: More conditional formatting in SSRS

Is there any way to add the images as resource in the report so you don't have to rely on a URL.. I see an Image folder in my 'Report Data' but I don't know how to access it as a variable in a function..
6/28/2011 2:23 PM | Ken
Gravatar

# re: More conditional formatting in SSRS

Hi,

I already formatted my field value with the format expression "###0.00;(###0.00);-".
But my requirement is after getting the formatted result if any value >0 and < 1 gets displayed it should be displayed as "-" dash.

Kindly help me out solving this.


Thank you!

Regards,
Gayusuganth
7/26/2011 6:24 AM | Gayathri Suganth
Gravatar

# re: More conditional formatting in SSRS

How can I get this color expression to show as BOLD?

Thank you for your help...
10/11/2011 5:10 PM | Foolzrushin
Gravatar

# re: More conditional formatting in SSRS

If I use the Min () and Max() functions of SSRS in the expression to color the background, I get the following error-
Aggregate functions can be used only on report items contained in page headers and footers

Can anyone help? Thanks
11/12/2011 12:36 AM | Mike
Gravatar

# re: More conditional formatting in SSRS

Hello,
i am new to reporting. can somebody please help me with this,
i need to set the visibility of header to display only on Page 1. i have checked the Header properties to be visible only for first page but it gets displayed in first 2 pages.
Does writing a conditional statement would solve this?
If so, please guide me how to write
i have a PageNumber textbox in the Footer of my Report

Thanks for your help!
12/15/2011 1:03 AM | Bin
Gravatar

# re: More conditional formatting in SSRS

=Switch
(
(IsNothing(Fields!TAX_AUDIT_ID.Value),"NONTAXABLE OR TAX EXEMPT"),

((Left(CStr(Fields!TAX_CDE_1.Value),3)<>"030" OR Left(CStr(Fields!TAX_CDE_1.Value),3)<>"330")
and
(Left(CStr(Fields!TAX_CDE_2.Value),3)="030" or Left(CStr(Fields!TAX_CDE_2.Value),3)="330"),"TAX MATERIAL ONLY"),





((Left(CStr(Fields!TAX_CDE_1.Value),3)="030" or Left(CStr(Fields!TAX_CDE_1.Value),3)="330")
and
(Left(CStr(Fields!TAX_CDE_2.Value),3)<>"030" or Left(CStr(Fields!TAX_CDE_2.Value),3)<>"330"),"TAX LABOR ONLY"),

True, "NO"
)

TAX_CDE_1 and
TAX_CDE_2 are not equal to "030" or "330", but then also it is returning " TAX MATERIAL ONLY " in the report. please comment.
1/10/2012 6:18 AM | raja
Gravatar

# re: More conditional formatting in SSRS

Ehats wrong with this. I cant get it right. I tried the switch as u said exactly same. BUt in both cases it returns the value in the first condition. Doesnt go further down.

=iif(
Parameters!StartMonth.Value <= 1 AND Parameters!EndMonth.Value >= 1,
ReportItems!txtTotal2.Value,
iif(
Parameters!StartMonth.Value <= 2 AND Parameters!EndMonth.Value >= 2,
ReportItems!txtTotal3.Value,
iif(
Parameters!StartMonth.Value <= 3 AND Parameters!EndMonth.Value >= 3,
ReportItems!txtTotal4.Value,
Nothing
)
)
)
3/29/2012 6:34 PM | Maz
Gravatar

# re: More conditional formatting in SSRS

Hi,
I am having an issue formatting the Textbox forecolor of text to Red if some condition have met but not able to format it

There is no Color property coming to my Report designer. I am using Visual Studio 2008 and created .rdlc file
Color property is not coming.

Plse help

Thanks
mayur
6/22/2012 4:22 AM | mayur nirmal
Gravatar

# re: More conditional formatting in SSRS

Hi guys,
lots of knowledge around here! Could you please give me a hand with something as well? I have a column where i have positive and negative variances. On the bottom of the column i have an overyield and an underyield total. I cant for the life of me add just the - or the + variances.
The function used to aggregate the variances is =Sum(Fields!VAR.Value) as the business only needs one partcode and one variance.
Any help would be greatly appreciated.

Many thanks

Panos
7/3/2012 3:28 AM | Panos
Gravatar

# re: More conditional formatting in SSRS

@mayur
You will need to go to the column that you are after and then go to the properties of it (make sure you are selecting the expressions bit of the column not the header). From the properties select Colour and then expression and paste this in:

=IIF(Fields!MY.Value)<0, "RED","BLACK"

Hope that helps
7/3/2012 3:33 AM | Panos
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET