Colour Code a Range? (Switch with and perhaps?)

Question

Colour Code a Range? (Switch with and perhaps?)

Rob-350472

SSCertifiable

Points: 7197
More actions
November 23, 2011 at 3:52 am

#246655

I'm trying to colour a range of values with the following approximate categories:
= 0.00
+/- 0.01-0.03
+/- 0.04-0.20
+/- 0.21-0.99
+/- 1.00+
As you can see basically distance from zero, zero should be white, +/-1 should be red etc. I'm sure you see the basic rational here.
I tried to do this with the switch statement but I'm not sure how I write an and in this? (I don't know visual basic).
=Switch(
Fields!Test.Value = 0, "Red",
Fields!Test.Value >0 & <0.04, "Green"
)
Just as an example, I tried it with and as well as &. Should this kind of logic be possible, am I just using the incorrect syntax?
Any help much appreciated, I've found tons of articles which cover Switch but none seem to do what I'm after!
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Rob-350472 (11/23/2011)
I'm trying to colour a range of values with the following approximate categories:
= 0.00
+/- 0.01-0.03
+/- 0.04-0.20
+/- 0.21-0.99
+/- 1.00+
As you can see basically distance from zero, zero should be white, +/-1 should be red etc. I'm sure you see the basic rational here.
I tried to do this with the switch statement but I'm not sure how I write an and in this? (I don't know visual basic).
=Switch(
Fields!Test.Value = 0, "Red",
Fields!Test.Value >0 & <0.04, "Green"
)
Just as an example, I tried it with and as well as &. Should this kind of logic be possible, am I just using the incorrect syntax?
Any help much appreciated, I've found tons of articles which cover Switch but none seem to do what I'm after!
Thanks!

I believe switch is short-circuited so you can just do something like this:

=Switch(

Fields!Test.Value = 0, "Red",

Fields!Test.Value <0.04, "Green"

)

If it is not short-circuited you'd want this:

=Switch(

Fields!Test.Value = 0, "Red",

Fields!Test.Value >0 && Fields!Test.Value <0.04, "Green"

)

I'm pretty sure that you need the double ampersand for AND. I haven't been in there for awhile.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 2

Thanks Jack, I've played around with that a little bit, and experimented and it seems something like this:

=Switch(Abs(Fields!Test.Value) = 0.00, “White”,

Abs(Fields!Test.Value) <= 0.03, “Red”)

Would do it, so basically your first response.

I'm now trying to see if I can put this into a Public Function so I can call it for 15 or so columns. Having never done one or had any exposure to .net etc it's a tad tricky!

Possinator Hall of Fame Points: 3422 More actions · Answer 3

You should be able to shift-select all of the texboxes and set the background color property for all at one go.

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 4

I think to do that I'd need to be evaluating the same condition though rather than a different field in each case. At any rate I created a public function to achieve this which I've then called in each case. The user is happy, I'm happy, all good!