SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Conditional Formatting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  08:10:09  Show Profile  Reply with Quote
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)

Edited by - sz1 on 11/20/2012 08:28:36

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  08:34:32  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  08:48:36  Show Profile  Reply with Quote
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")))

Edited by - sz1 on 11/20/2012 09:00:27
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:01:56  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  09:10:13  Show Profile  Reply with Quote
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"))))

Edited by - sz1 on 11/20/2012 09:17:02
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:16:19  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  09:18:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:22:30  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  09:24:54  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  09:30:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  10:05:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  10:06:15  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 11/20/2012 :  10:20:43  Show Profile  Reply with Quote
Brilliant thanks again...:)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000