Solved

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

Posted on 2013-11-24
6
1,381 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
6 Comments
 
LVL 9

Accepted Solution

by:
QuinnDex earned 200 total points
Comment Utility
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 50 total points
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
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
Comment Utility
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
Comment Utility
--
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now