Solved

SQL Server reporting services (SSRS color code expression)

Posted on 2013-12-04
15
2,871 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
Comment Utility
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
Comment Utility
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
Comment Utility
Please note that, I have to change Background color in cell not Text color using expression in SSRS reports (MS SQL 2005/2008).
0
 

Author Comment

by:sqldba2013
Comment Utility
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
Comment Utility
>> [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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:sqldba2013
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks ValentinoV.

Its working perfect, You're the awesome.

Increasing points from 250 to 500.
0
 

Author Closing Comment

by:sqldba2013
Comment Utility
--
0
 
LVL 37

Expert Comment

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

Have a nice weekend!

VV
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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