?
Solved

SQL Server reporting services (SSRS color code expression)

Posted on 2013-12-04
15
Medium Priority
?
3,047 Views
Last Modified: 2014-01-09
Hi All,

Need your help on below SSRS expression.

Expression:

=Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, "Transparent",
(IsNothing(Fields!Invoice___to_date.Value)="") OR (Fields!Invoice___to_date.Value <=0),"Transparent")

1. Compare the Invoice___to_date column to the Sales_Order___to_date column.
a. If the Invoice value is less, then make background color as a RED.
b. If the Invoice is more, then make background color as a GREEN.
c. If the Invoice is equal, then do not add a color.

2. Only apply color expression to cells where there is an actual value (If cell doesn't contain any value, display cell background color as a transparent or No Color).

The above expression is not giving proper results for point # 1 & 2.

Please help me to correct my mistake in above expression.
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
  • 9
  • 6
15 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695084
What is the data type of those fields being used?  If not datetime then you may need a conversion for the logical expressions to work:

Switch(
	CDate(Fields!Invoice___to_date.value) < CDate(Fields!Sales_Order___to_date.Value), "Red",
	CDate(Fields!Invoice___to_date.Value) > CDate(Fields!Sales_Order___to_date.Value),"Green",
	CDate(Fields!Invoice___to_date.Value) = CDate(Fields!Sales_Order___to_date.Value), Nothing
)

Open in new window

"Transparent" is not a valid color, you need to use Nothing as shown above.

Also, I removed the following line because it doesn't make much sense (IsNothing returns a boolean, not a string):

(IsNothing(Fields!Invoice___to_date.Value)="") OR (Fields!Invoice___to_date.Value <=0),"Transparent")

Open in new window

I assume it was meant to implement your second condition.  You're probably better using an IIF around the Switch:

IIF(IsNothing(Fields!YourFields.Value), Nothing, 
  Switch(
	CDate(Fields!Invoice___to_date.value) < CDate(Fields!Sales_Order___to_date.Value), "Red",
	CDate(Fields!Invoice___to_date.Value) > CDate(Fields!Sales_Order___to_date.Value),"Green",
	CDate(Fields!Invoice___to_date.Value) = CDate(Fields!Sales_Order___to_date.Value), Nothing
  )
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39695104
Thanks ValentinoV.

Please find the data type details.

[Invoice___to_date] decimal(18,2)
[Sales_Order___to_date] DECIMAL(18,2)

I am new to SSRS technology, Can you please tel me which expression contains logic of point 1 & 2 or let me know which script I have to use (final script) for point 1 & 2 requirement in SSRS report?
0
 

Author Comment

by:sqldba2013
ID: 39695107
Please note that, I have to change Background color in cell not Text color using expression in SSRS reports (MS SQL 2005/2008).
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:sqldba2013
ID: 39695117
The below logic is working fine for point 1.

=Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, "Transparent")

Please let me know how to modify the above expression for point 2.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695216
>> [Invoice___to_date] decimal(18,2)
>> [Sales_Order___to_date] DECIMAL(18,2)

Ow I see, I was under the assumption that these were dates, apparently not.  So my statement using CDate conversion earlier doesn't make any sense...

>> Please let me know how to modify the above expression for point 2.

Assuming your table has a textbox (cell) called TextBox1 and you want to change the background color of that textbox, the following should go into the BackgroundColor property of the textbox:

=IIF(IsNothing(ReportItems!TextBox1.Value), Nothing, 
	Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
		Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
		Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39695405
Hi ValentinoV,

I've tried with below expressions, the output result is correct for Point 1 and for Point 2, the output result is wrong i.e., still it is showing RED background color in cell (these cell doesn't contain any value/data and for these cell(s), we need to set background as a transparent/ no color for these cells )

Please advise on further action item on this issue. I have tried below two options

Option 1:

=IIF(IsNothing(ReportItems!textbox44), Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Option 2:

=IIF(IsNothing(ReportItems!Invoice___to_date.Value), Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695453
What is the name of the textbox?  textbox44 or Invoice___to_date?  In any case, you need to include .Value (it's missing in Option 1)

Let's try this one then (assumes your textbox is called textbox44):

=IIF(Len(ReportItems!textbox44.Value)=0, Nothing, 
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39697454
Hi ValentinoV,

I have tried with above expression & output results are partially correct.

a. If cell contains 0 value (no value in cell): no background color - above expression is working perfect.
b. If cell contains - symbol (minus / hyphen): its showing background color as a RED. We need to set background color as a Nothing/No color where cell contains - symbol.

Please advise me on point b.

Once again thanks a lot for your assistance.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39697521
Is "-" the only character that needs to be taken into account?  And is that coming straight from the dataset or the result of some formatting on the cell?

Anyway, perhaps something like this then:

=IIF(Len(ReportItems!textbox44.Value)=0 or ReportItems!textbox44.Value = "-", Nothing, 
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39697618
Its a result of some formating in cell ('$' #,##0.00;('$' 0.00);'-')

I have changed formating to '$' #,##0.00;('$' 0.00); and applied above expression. Its still showing BG color as a RED & these cell doesn't contain any value/data).

=IIF(Len(ReportItems!Invoice___to_date.Value)=0 or ReportItems!textbox44.Value = "-", Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)
0
 

Author Comment

by:sqldba2013
ID: 39697638
I tried with below expression:

=IIF(Len(ReportItems!Invoice___to_date.Value)=0 or ReportItems!Invoice___to_date.Value = "-", Nothing,
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39697853
Okay, so due to the formatting when the value displayed in the cell is zero you'll get the hyphen instead.

So how about this one then:

=IIF(Len(ReportItems!textbox44.Value)=0 or ReportItems!textbox44.Value = 0, Nothing, 
      Switch( (Fields!Invoice___to_date.value < Fields!Sales_Order___to_date.Value), "Red",
            Fields!Invoice___to_date.Value > Fields!Sales_Order___to_date.Value,"Green",
            Fields!Invoice___to_date.Value = Fields!Sales_Order___to_date.Value, Nothing)
)

Open in new window

If that doesn't work, let me know what field you're displaying in textbox44...
0
 

Author Comment

by:sqldba2013
ID: 39702760
Thanks ValentinoV.

Its working perfect, You're the awesome.

Increasing points from 250 to 500.
0
 

Author Closing Comment

by:sqldba2013
ID: 39702761
--
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39702993
Cool!  Thanks for your nice comment, and the points ;)

Have a nice weekend!

VV
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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