Using a partial lookup of a value to conditionally set the color of a textbox
-
Hi there
I have an SSRS 2016 report which set the colour of the text in a cell.
The look up expression uses a dataset called 'StatusColour' which is defined as follows:
SELECT 'Available' AS [Status], 'Black' AS [Colour]
UNION SELECT 'Allocated' AS [Status], 'Blue' AS [Colour]
UNION SELECT 'Complete' AS [Status], 'Green' AS [Colour]
UNION SELECT 'InProgress' AS [Status], 'Blue' AS [Colour]
UNION SELECT 'Phase 2 - Completed' AS [Status], 'Green' AS [Colour]
UNION SELECT 'Phase 2 - Completed' AS [Status], 'Green' AS [Colour]
UNION SELECT 'FileArchived' AS [Status], 'Green' AS [Colour]
UNION SELECT 'Failed' AS [Status], 'Red' AS [Colour]
UNION SELECT 'Error' AS [Status], 'Red' AS [Colour]
UNION SELECT 'Superceeded' AS [Status], 'Grey' AS [Colour]
The Color is currently set using the folloiwing expression
=Lookup(Fields!RunStatus.Value, Fields!Status.Value, Fields!Colour.Value, "StatusColour")
Where RunStatus relates to one of the values of [Status] in the Dataset called 'StatusColour'
How can i modify the above to look for the word 'Failed' which would be contained in the
[RunStatus] field without having to code of all these statuses out into the dataset 'StatusColour'
'Phase 1 - Data Transformation (Failed)'
'Phase 1 - File Reconciliation (Failed)'
'Phase 1 - Filtered Data Reconciliation (Failed)'
How can i change the lookup expression to search for partial words in Status ie 'Failed'?
I've attached some screenshots
-
Something like this?
=Lookup(IIf(Fields!RunStatus.Value Like "%(Failed)%", "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
tHAT
Jeffrey Williams wrote:Something like this? =Lookup(IIf(Fields!RunStatus.Value Like "%(Failed)%", "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")
Hi Jeffrey
Im afraid that didnt work for me
-
What does that mean?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I tried the expression you suggested in the Lookup expression for Color and statuses which featured 'Failed' are not being shown as red.
Please see the attached screenshot of the report and the underlying data
-
this is the underyling data
-
Sorry - the wildcards should be '*' instead...but you can also try a few other methods:
=Lookup(IIf(Fields!RunStatus.Value.Contains("(Failed)") , "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")
=Lookup(IIf(InStr(Fields!RunStatus.Value, "(Failed)") > 0, "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")
=Lookup(IIf(Fields!RunStatus.Value.IndexOf("(Failed)") > 0, "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey
I tried the first of those statements you suggested:
=Lookup(IIf(Fields!RunStatus.Value.Contains("(Failed)") , "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")
And that worked perfectly!! Thank you so much for your help
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply