Post reply

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

  • Weegee2017

    SSC Eights!

    Points: 942

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

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

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

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

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

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

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

    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 🙂

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply