Using a partial lookup of a value to conditionally set the color of a textbox

• Weegee2017

  SSC Eights!

  Points: 930

  June 7, 2019 at 4:15 pm

  #3651086

  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

  Attachments:

  You must be logged in to view attached files.

• Jeffrey Williams 3188

  SSC Guru

  Points: 88022

  June 7, 2019 at 5:07 pm

  #3651111

  Something like this?

  =Lookup(IIf(Fields!RunStatus.Value Like "%(Failed)%", "Failed", Fields!RunStatus.Value), Fields!Status.Value, Fields!Colour.Value, "StatusColour")

   

  [font="Verdana"]Jeffrey Williams
  Problems are opportunities brilliantly disguised as insurmountable obstacles.

  How to post questions to get better answers faster[/url]
  Managing Transaction Logs[/url]
  [/font]

• Weegee2017

  SSC Eights!

  Points: 930

  June 7, 2019 at 7:04 pm

  #3651145

  tHAT

  Jeffrey Williams 3188 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

• Jeffrey Williams 3188

  SSC Guru

  Points: 88022

  June 7, 2019 at 7:09 pm

  #3651147

  What does that mean?

  [font="Verdana"]Jeffrey Williams
  Problems are opportunities brilliantly disguised as insurmountable obstacles.

  How to post questions to get better answers faster[/url]
  Managing Transaction Logs[/url]
  [/font]

• Weegee2017

  SSC Eights!

  Points: 930

  June 7, 2019 at 7:19 pm

  #3651149

  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

   

   

  Attachments:

  You must be logged in to view attached files.

• Weegee2017

  SSC Eights!

  Points: 930

  June 7, 2019 at 7:20 pm

  #3651151

  this is the underyling data

  Attachments:

  You must be logged in to view attached files.

• Jeffrey Williams 3188

  SSC Guru

  Points: 88022

  June 8, 2019 at 3:59 pm

  #3651427

  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")

   

  [font="Verdana"]Jeffrey Williams
  Problems are opportunities brilliantly disguised as insurmountable obstacles.

  How to post questions to get better answers faster[/url]
  Managing Transaction Logs[/url]
  [/font]

• Weegee2017

  SSC Eights!

  Points: 930

  June 10, 2019 at 8:49 am

  #3651730

  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 🙂