Problem formatting in SSRS

I am trying to do some conditional formatting to values in a text box based off the value. I want to format the values a different color depending on what the amount is. If the amount in the textbox is 0 then the color is red, but if the value is anything other than 0 the color is black. I'm not sure what I'm doing wrong.

I have attached a copy of the code I'm trying to use. Any advice would be great.

Thanks.
This is the Function I'm using...
 
Public Shared Function SetColor(ByVal value As String) as String
 
   SetColor = "Red"
   
   If Value >= 21 and Value <= 34 Then
     SetColor = "Black"
   ElseIf Value >= 35 Then
     SetColor = "Green"
   End IF
 
End Function
 
This is the code i'm trying to use on the "Color" property of the textbox...
 
=IIF(Fields!YtdSales2.Value = 0 and Fields!PtdCOGS2.Value = 0, 0, 
FormatPercent(CDbl((Fields!YtdSales2.Value - Fields!YtdCOGS2.Value)/Fields!YtdSales2.Value)))

Open in new window

LVL 1
ochnessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
I believe what you want to use in the color property box is this
=iif( me.Value = 0, "Red","Black" )

Open in new window

0
Chris LuttrellSenior Database ArchitectCommented:
I was basing that on the text in the question saying Red if = 0 anything else should be black.  If you need the more complex formula from your code snippet try this
=IIf(me.Value >= 21 and me.Value <= 34,"Black",
   IIf(me.Value >= 35,"Green",
   "Red"))

Open in new window

0
ochnessAuthor Commented:
CGLuttrell....The second set of code looks like a better way then I was trying to do it, but everything is still black unless the value is 0. It must have something to do with the way I'm getting the value. I have attached the code again that I'm using to get the value in that textbox. Is it because I'm doing a FormatPercent?

Thanks again....
=IIF(Fields!YtdSales2.Value = 0 and Fields!PtdCOGS2.Value = 0, 0, 
FormatPercent((Fields!YtdSales2.Value - Fields!YtdCOGS2.Value)/Fields!YtdSales2.Value))

Open in new window

0
Chris LuttrellSenior Database ArchitectCommented:
Oh, I see, a couple of thing to change here.  First, your value is going to be between 0 and 1 not 0 and 100 so we need the decimals in the formula.  I just tried the code below and it worked for me when I made another change with it.  In your formula above, take off the FormatPercent here and instead edit the TextBox propery and set Format to P2 there.  See if that does not work better.
=IIf(me.Value >= .21 and me.Value <= .34,"Black",
   IIf(me.Value >= .35,"Green",
   "Red"))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ochnessAuthor Commented:
This works now. Once I added your code and changed the format to P2 it all works now. Thanks for your help on this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.