Issuing SSRS score
I have a problem with expressions in reports. I am coloring the background of a textbox inside a table based on the value inside it. The text in the field refers to backups for SQL Server. The value is the date or the text "Not yet Taken". If the date is more than two days old, I want the background to be yellow. If it's more than a week old, or if the date value is "Not yet accepted", I want the background to be red. Otherwise, it will be green.
The problem I've run into since I started reporting for SSRS (a few weeks ago) is that my expressions seem to be fully evaluating. The IF statement will have both true and false values, even if only one of them is used.
This becomes a problem because "Not yet Taken" is clearly not a date and works with the dates I need to convert a date string to a date. Here is the code I have:
=IIF(Fields!LastBackUpTaken.Value = "Not Yet Taken","Red", IIF( IsDate(Fields!LastBackUpTaken.Value) = true,
IIF( CDate(Fields!LastBackUpTaken.Value).AddDays(Parameters!DaysTillExpiry.Value).CompareTo(NOW()) = 1,
"GreenYellow",
IIF( CDate(Fields!LastBackUpTaken.Value).AddDays(7).CompareTo(NOW()) = 1, "Yellow", "Red")),
"Red"))
Basically, the expression reads "If LastBackUpTaken.Value =" Not yet Taken ", return the color" Red. "If it is not" Not yet Taken ", check if the string is a date. 'T date, return the color Red. If it is date, do the calculations and return the appropriate color.
These expressions work for all text fields that do not have "Not yet Taken" in the text. For fields that have "Not yet Taken" because its text does not have a set of colors.
EDIT: I also get a conversion error that I forgot to mention whenever the text "Not yet Taken"
Any ideas?
source to share
Write a VB function to return the color string on the Code tab of the report properties. Here you can use language constructs that are convenient for you (case case, regular if statements, etc.). The logic is also easier to read.
Public Function GetBackgroundColor(ByVal DateString as String) As String
'plain old vb syntax here
End Function
In the expression for the background color property:
=Code.GetBackgroundColor(Fields!LastBackUpTaken.Value)
source to share