• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1659
  • Last Modified:

ms sql ssrs (Changing Text Color Based on an Expression)

Please guide me how to change Text Color for below mentioned Expression in ms sql ssrs report fields.

Expression 1: Compare CPO value to the Total quote value. If the CPO value is less, then make it red. If the CPO is more, then make it green. If the CPO is equal, then do not add a color.

Expression 2: Compare the Invoice column to the SO column. If the Invoice value is less, then make it red. If the Invoice is more, then make it green. If the Invoice is equal, then do not add a color.

Fields:
'Total quote value',
'CPO',
'SO'  
'Invoice'

I have tried with below expression, but I didn't get proper output.

=IIF(Fields!cpo value.Value < 0, "Red", "Black")

Thanks in advance.
0
sqldba2013
Asked:
sqldba2013
2 Solutions
 
QuinnDexCommented:
you cant use iif effectively for more than 2 options, iff is for 1 choice or the other you can next iif's but never good practice

the following will give red or black
=IIF(Fields!cpo value.Value < Fields!Total quote value.Value <, "Red",  "Black") 

Open in new window


for the choice of 3 red black or green you should be using switch statements

=Switch( (Fields!cpo value.Value < Fields!Total quote value.Value), "Red", Fields!Fields!cpo value.Value > Fields!Total quote value.Value , "Green", Fields!cpo value.Value = Fields!Total quote value.Value, "Black" ) 

Open in new window

0
 
Nico BontenbalCommented:
QuinnDex is right about the Switch being the best option here, and he should get the points. But it is possible to use nested iif's like this:
=Iif(Fields!cpo value.Value < Fields!Total quote value.Value,"Red",Iif(Fields!Fields!cpo value.Value > Fields!Total quote value.Value , "Green","Black))

Open in new window

0
 
sqldba2013Author Commented:
Thanks.

I have copied below code in CPO_value field (properties--background color--expression).

=Switch( (Fields!CPO_Value.Value < Fields!Total_Quote_Value.Value), "Red",
Fields!CPO_Value.Value > Fields!Total_Quote_Value.Value,"Green",
Fields!CPO_Value.Value = Fields!Total_Quote_Value.Value, "Black" )

and I got the below error, please suggest.

[rsFieldReference] The BackgroundColor expression for the report ‘body’ refers to the field ‘CPO_value’.  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.
Build complete -- 1 errors, 0 warnings
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Nico BontenbalCommented:
I think you entered the expression in the wrong place. In your question you mention the text color, but the error message says the expression is entered in the BackgroundColor for the report body.
0
 
ValentinoVBI ConsultantCommented:
That expression is definitely entered in the wrong place, unless the textbox is called "body"? :

"[rsFieldReference] The BackgroundColor expression for the report ‘body’ refers to the field ‘CPO_value’.  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope."

Make sure the CPO_Value textbox is selected, then locate the Color property and enter the expression there.
0
 
sqldba2013Author Commented:
--
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now