| Author |
 Topic  |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
 Posted - 11/20/2012 : 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) |
Edited by - sz1 on 11/20/2012 08:28:36
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/20/2012 : 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 |
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/20/2012 : 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"))) |
Edited by - sz1 on 11/20/2012 09:00:27 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/20/2012 : 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"
)
)
) |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/20/2012 : 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")))) |
Edited by - sz1 on 11/20/2012 09:17:02 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/20/2012 : 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"
)
)
)
)
) |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/20/2012 : 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")))) |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/20/2012 : 09:22:30
|
| This seems fine syntactically - but the Yellow will never appear. For what range of values did you want yellow to appear? |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/20/2012 : 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?
|
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/20/2012 : 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", "")))) |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/20/2012 : 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. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/20/2012 : 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",
)
)
) |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/20/2012 : 10:20:43
|
| Brilliant thanks again...:) |
|
|
| |
Topic |
|
|
|
Conditional Formatting
