We help IT Professionals succeed at work.

SQL Server reporting services (SSRS color code expression)

3,544 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.
Comment
Watch Question

ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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?

Author

Commented:
Please note that, I have to change Background color in cell not Text color using expression in SSRS reports (MS SQL 2005/2008).

Author

Commented:
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.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
>> [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

Author

Commented:
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)
)
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Author

Commented:
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)
)

Author

Commented:
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)
)
BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks ValentinoV.

Its working perfect, You're the awesome.

Increasing points from 250 to 500.

Author

Commented:
--
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Cool!  Thanks for your nice comment, and the points ;)

Have a nice weekend!

VV
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.