?
Solved

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

Posted on 2013-11-24
6
Medium Priority
?
1,587 Views
Last Modified: 2013-12-02
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
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Accepted Solution

by:
QuinnDex earned 800 total points
ID: 39672624
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
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 200 total points
ID: 39672662
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
 

Author Comment

by:sqldba2013
ID: 39673687
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39673775
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39677116
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
 

Author Closing Comment

by:sqldba2013
ID: 39689291
--
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question