Solved

SQL Server reporting services (SSRS color code expression)

Posted on 2013-12-04
15
2,962 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

713 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