October 14, 2010 at 2:13 pm
I am trying to do something that seems very simple, but isn't working properly. A stored procedure is returning data to a matrix report where one of the fields should have a different color depending on the value.
If the value is less than 0, then make it red
If the value is greater than 0, then make it green
if the value = 0, then make it black
The problem is the field always displays the 0 as red instead of black.
Here are the various things I have tried:
=IIF(Fields!Delta.Value < 0, "Red", IIF(Fields!Delta.Value > 0, "Green", IIF(Fields!Delta.Value = 0, "Black", "Black")))
=Switch(Fields!Delta.Value < 0, "Red", Fields!Delta.Value > 0, "Green", "Black")
=Switch(Fields!Delta.Value < 0, "Red", Fields!Delta.Value > 0, "Green", Fields!Delta.Value = 0, "Black")
=Switch(Fields!Delta.Value < 0, "Red", Fields!Delta.Value > 0, "Green", Fields!Delta.Value IS "0", "Black")
=Switch(Fields!Delta.Value <= -1, "Red", Fields!Delta.Value >= 1, "Green", Fields!Delta.Value = 0, "Black")
=Switch(Fields!Delta.Value <= -1 and Fields!Delta.Value <> 0 , "Red", Fields!Delta.Value >= 1, "Green", "Black")
Does anyone know why these various versions of =IIF and =Switch statements do not evaluate the value 0 properly?
What am i missing?
Thanks for the help!
October 14, 2010 at 2:34 pm
Trina Blazek-394828 (10/14/2010)
I am trying to do something that seems very simple, but isn't working properly. A stored procedure is returning data to a matrix report where one of the fields should have a different color depending on the value.If the value is less than 0, then make it red
If the value is greater than 0, then make it green
if the value = 0, then make it black
The problem is the field always displays the 0 as red instead of black.
Here are the various things I have tried:
=IIF(Fields!Delta.Value < 0, "Red", IIF(Fields!Delta.Value > 0, "Green", IIF(Fields!Delta.Value = 0, "Black", "Black")))
=Switch(Fields!Delta.Value < 0, "Red", Fields!Delta.Value > 0, "Green", "Black")
=Switch(Fields!Delta.Value < 0, "Red", Fields!Delta.Value > 0, "Green", Fields!Delta.Value = 0, "Black")
=Switch(Fields!Delta.Value < 0, "Red", Fields!Delta.Value > 0, "Green", Fields!Delta.Value IS "0", "Black")
=Switch(Fields!Delta.Value <= -1, "Red", Fields!Delta.Value >= 1, "Green", Fields!Delta.Value = 0, "Black")
=Switch(Fields!Delta.Value <= -1 and Fields!Delta.Value <> 0 , "Red", Fields!Delta.Value >= 1, "Green", "Black")
Does anyone know why these various versions of =IIF and =Switch statements do not evaluate the value 0 properly?
What am i missing?
Thanks for the help!
A hunch at this point, but I suspect the "delta" field you are trying to evaluate might not be an int datatype, or report builder might not be treating it as an int datatype, and the expression isn't evaluating correctly because of that. Try putting INT(fields!Delta.Value) = 0 and see if that works.
October 14, 2010 at 2:35 pm
What is the datatype of the "Delta" field? I suspect that it is a real or a float. If so, it is possible that what you think of as "0" is being return just a tad bit off "0".
Russel Loski, MCSE Business Intelligence, Data Platform
October 14, 2010 at 2:55 pm
The data type in the text box properties is set to "Number" with decimal places set to 0
adding the INT(Field!Delta.Value) = 0 had no affect.
The data is coming back from the stored procedure as a calculated value where two integer values are added together to determine the delta value.
thank you for the help!
October 14, 2010 at 3:08 pm
A few thoughts:
1) Have you tried putting the [value = 0, "Black"] part in the front of the IIf statement?
2) Does the text box containing the expression have a default font color of black? I'm referring to the field properties, not the IIf statement.
3) Have you tried rounding the result in the expression?
4) Replace the [Fields!Delta.Value] with [0]. Does it still render in red?
October 15, 2010 at 8:45 am
1) Have you tried putting the [value = 0, "Black"] part in the front of the IIf statement?
yes - this had no affect
2) Does the text box containing the expression have a default font color of black? I'm referring to the field properties, not the IIf statement.
The text box properties for color is the expression. I didn't see a way to incorporate a default of black other than putting it as part of the expression.
3) Have you tried rounding the result in the expression?
adding the ROUND() to the value in the expression had no affect.
4) Replace the [Fields!Delta.Value] with [0]. Does it still render in red?
adding 0=0, "Black" instead of the [Fields!Delta.Value] to the beginning of the statement renders all of the values to be black, and putting it at the end still results in the 0 being rendered in the red color.
Thank you for the ideas...
October 15, 2010 at 8:56 am
IIf statements don't simply fail, so it looks like there's one of two things going on. Either the value truly isn't zero, or the statement is not written like you read it to be. For the latter, are you certain there's not a " <= 0" somewhere? I know you posted the code, but you might want to check one more time.
Try changing another property like Hidden to be =IIf(Fields!Delta.Value = 0, True, False)
That way you can confirm it's zero (or not).
October 15, 2010 at 2:09 pm
I think I know what may be causing this issue but not sure how to fix it.
The stored procedure returns multiple rows of data that are summed in the report field.
For the Delta field on the Matrix report in the Text Box Properties on the General tab, the Value is set to =Sum(Field!Delta.Value)
The sample data returned from the sproc has 4 rows returned for Delta:
-1
0
0
1
The summed value is 0, however, the report appears to be evaluating the first value only which is coming in as -1, which explains why it is always showing as red for this test data.
Is there a way to configure a Switch or IIF statement to properly evaluate the summed value being displayed in the field?
October 15, 2010 at 2:21 pm
=iif(Sum(Field!Delta.Value) = 0, ...)
Russel Loski, MCSE Business Intelligence, Data Platform
October 15, 2010 at 2:30 pm
If you have a group, you can use a scope statement that should sum it according to the grouping. Something like
=Sum(Field!Delta.Value,"groupname")
October 15, 2010 at 4:33 pm
Thank you to everyone for the help and ideas!
This statement worked:
=IIF(Sum(Fields!Delta.Value) < 0, "Red", IIF(Sum(Fields!Delta.Value) > 0, "Green", IIF(Sum(Fields!Delta.Value) = 0, "Black", "Black")))
October 15, 2010 at 4:44 pm
This switch statement also worked:
=Switch(Sum(Fields!Delta.Value) <= -1, "Red", Sum(Fields!Delta.Value) >= 1, "Green", Sum(Fields!Delta.Value) = 0, "Black")
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply