Post reply

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

  • 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.

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

     

    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

  • June 7, 2019 at 7:04 pm

    #3651145

    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

  • June 7, 2019 at 7:09 pm

    #3651147

    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

  • 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.

  • June 7, 2019 at 7:20 pm

    #3651151

    this is the underyling data

    Attachments:
    You must be logged in to view attached files.

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

     

    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

  • 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 7 (of 7 total)

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