Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Conditional Formatting

Author  Topic 

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 08:10:09
Hi

I want to add an expression for colours based on numbers I assign, when I look at the expression in a table cell it already has the filed value sa the expression, how do I add the conditional formatting code without removing the actual field its looking at?

Something like this (tried adding the actual field name at the end but get error)

=iif( (Fields!Open_Days.Value < 10), "Green", ( iif(Fields!Open_Days.Value >10 , "Yellow", iif(Fields!Open_Days.Value > 20 "Blue", Fields!id.Value) ) ) )

Also tried this...Fields!id.Value is the actual field the cell is reading...

=Switch(Fields!Open_Days.Value < 10, "Green", Fields!Open_Days.Value > 11, "Blue", Fields!Open_Days.Value > 21, "Red", Fields!id.Value)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-11-20 : 08:34:32
If you want to display the numbers in green, yellow or blue based on the values, insert your expression in the Color property of the properties window rather than the expression - something like this:
=iif( 
		(Fields!Open_Days.Value < 10), 
		"Green", 
		( 
			iif
			(
				Fields!Open_Days.Value > 20 
				"Blue", 
				"Yellow", 
			) 
		) 
	)
Leave the expression for the value unchanged as Fields!id.Value
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 08:48:36
Yes thats what I'm after, new to SSRS...I'm getting an error for argument not specified for FalsePart?

=iif((Fields!Open_Days.Value >= 1), "Green", iif(Fields!Open_Days.Value >= 21), "Blue", (iif(Fields!Open_Days.Value > 30) "Red", "DimGray")))
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-11-20 : 09:01:56
Looks like there are some misplaced comma's. See if this works?
=iif
(
	Fields!Open_Days.Value < 10, 
	"Green",
	(
		iif
		(
			Fields!Open_Days.Value > 20,
			"Blue", 
			"Yellow"
		) 
	) 
)
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 09:10:13
Ace that works...

I want one more iif:
must be missing a comma somewhere gettinmg error?

=iif(Fields!Open_Days.Value >= 1), "Green",(iif(Fields!Open_Days.Value >= 21), "Blue", (iif( Fields!Open_Days.Value > 30, "Red", "Yellow"))))

This works but I need to change my ranges as > 1 is doing all green so need some < nodes:

=iif((Fields!Open_Days.Value >= 1), "Green",(iif(Fields!Open_Days.Value >= 21, "Blue", iif( Fields!Open_Days.Value > 30, "Red", "Yellow"))))

Got it:
Thanks form your help again...:)

=iif((Fields!Open_Days.Value <= 20), "Green",(iif(Fields!Open_Days.Value <= 30, "Blue", iif( Fields!Open_Days.Value > 30, "Red", "Yellow"))))
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-11-20 : 09:16:19
Syntax-wise, the following should work, BUT it may not do what you want it to do
=iif
(
	Fields!Open_Days.Value >= 1, 
	"Green",
	(
		iif
		(
			Fields!Open_Days.Value >= 21, 
			"Blue", 
			(
				iif
				(
					 Fields!Open_Days.Value > 30, 
					 "Red", 
					 "Yellow"
				)
			)
		)
	)
)
The "BUT" is that, if I am not mistaken, the expression is evaluated from left to right, so based on Fields!Open_Days.Value >= 1, it would assign Green to anything greater or equal to 1, including those between 21 and 30 and greater than 30. What you probably want is something like this:
=iif
(
	Fields!Open_Days.Value >30 
	"Red", 
	(
		iif
		(
			Fields!Open_Days.Value >= 21, 
			"Blue", 
			(
				iif
				(
					Fields!Open_Days.Value > 1, 
					"Green",
					"Yellow"
				)
			)
		)
	)
)
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 09:18:26
Got it, this does the same thing...near abouts
Thanks form your help again...:)

=iif((Fields!Open_Days.Value <= 20), "Green",(iif(Fields!Open_Days.Value <= 30, "Blue", iif( Fields!Open_Days.Value > 30, "Red", "Yellow"))))
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-11-20 : 09:22:30
This seems fine syntactically - but the Yellow will never appear. For what range of values did you want yellow to appear?
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 09:24:54
I'm not bothered with the last colour, so can remove that...one thing would be good to apply the formatting to the whole tablix rather that going thru all the fields, can this be done?
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 09:30:11
is this ok?

=iif((Fields!Open_Days.Value <= 20), "LimeGreen",(iif(Fields!Open_Days.Value <= 30, "Green", iif( Fields!Open_Days.Value > 30, "Red", ""))))
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-11-20 : 10:05:34
I don't know if you should apply the color to the whole tablix - that probably would include the row labels, column labels and everything else. You can select all the cells in the tablix and apply one formatting to all of them in the properties window, which would then affect only the data regions.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-11-20 : 10:06:15
quote:
Originally posted by sz1

is this ok?

=iif((Fields!Open_Days.Value <= 20), "LimeGreen",(iif(Fields!Open_Days.Value <= 30, "Green", iif( Fields!Open_Days.Value > 30, "Red", ""))))
=iif
(
	(Fields!Open_Days.Value <= 20), 
	"LimeGreen",
	(
		iif
		(
			Fields!Open_Days.Value <= 30, 
			"Green", 
			"Red", 
		)
	)
)
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts
Posted - 2012-11-20 : 10:20:43
Brilliant thanks again...:)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site