none
Nested SWITCH statements for coloring table rows depends on field value RRS feed

  • Question

  • I'm new in SSRS reports. I have a problem, it drives me crazy for several days.

    There is a set of categories and subcategories, like this:

    A - categoryA

    A - categoryA.A01 - subcat A01

    A - categoryA.A01 - subcat A01.A01A - subcat A01A

    A - categoryA.A02 - subcat A02

    B - categoryB

    C - categoryC

    D - categoryD

    ...

    I created a report, where each row represents category or subcategory. I need to set unique background color for each row. I've already tried so many combinations of SWITCH and IIF statements, and none of them works like I need.

    Considering that category name can change, and each subcategory in its name contains the name of all parent categories separated by a dot, I use Split() and Left() functions to get the code at the beginning of the category or subcategory name, only this code will not change.

    This expression works better than others, but not quite as it should. It sets the same color for subcategories of the same level.

    =Switch(Left(Fields!Category.Value,1) = "A",
    IIF(Split(Fields!Category.Value, ".").length = 1,
    "#e4daf3",
    IIF(Split(Fields!Category.Value, ".").length = 2,
    "#8db4e2",
    IIF(Split(Fields!Category.Value, ".").length = 3,
    "#ebf1de","#00ff00"))),
    Left(Fields!Category.Value,1) = "B", "#fdd9d9",
    Left(Fields!Category.Value,1) = "C", "#fcffd4", 
    Left(Fields!Category.Value,1) = "D", "#8de2de", 
    Left(Fields!Category.Value,1) = "E", "#c6e6fd"
    )

    I've tried to make this expression better, in so many ways, like this:

    =Switch(Left(Fields!Category.Value,1) = "A",
    IIF(Split(Fields!Category.Value, ".").length = 1,
    "#e4daf3",
    IIF(Split(Fields!Category.Value, ".").length = 2 AND Left(Split(Fields!Category.Value, ".")(1),3) = "A01",
    "#8db4e2",
    IIF(Split(Fields!Category.Value, ".").length = 2 AND Left(Split(Fields!Category.Value, ".")(1),3) = "A02",
    "#ff0000",
    IIF(Split(Fields!Category.Value, ".").length = 3 AND Left(Split(Fields!Category.Value, ".")(2),4) = "A01A",
    "#0000ff","#00ff00")))),
    Left(Fields!Category.Value,1) = "B", "#fdd9d9",
    Left(Fields!Category.Value,1) = "C", "#fcffd4", 
    Left(Fields!Category.Value,1) = "D", "#8de2de", 
    Left(Fields!Category.Value,1) = "E", "#c6e6fd"
    )

    But this expression somehow set background color only to subcategories, root categories are clear, no background at all.

    Maybe I didn't see something, or maybe there is some rules that forbid using nested SWITCHes and IIFs. Help me, please.

    Friday, August 16, 2019 4:30 PM

Answers

  • Hi iPetrus

    Sorry for the late reply. 

    i was post the reply in early day (the next day after you post) ,while today when i review the case , the reply was gone .

    May be the network issue . the following is about your issue :

    According to your expression , based on my test , seems it is not possible to refer the array in iif or switch function.

    In your expression you used the array in expression “Left(Split(Fields!Category.Value, ".")(1),3)” return the second split value ,right?

    Seems it is not available , you could try to use the following expression to instead .

    =Switch(Left(Fields!Category.Value,1) = "A",
    IIF(Split(Fields!Category.Value, ".").length = 1,
    "#e4daf3",
    IIF(Split(Fields!Category.Value, ".").length = 2 AND Left(right(Fields!Category.Value,len(Fields!Category.Value)-InStr(Fields!Category.Value,".")),3) = "A01",
    "#8db4e2",
    IIF(Split(Fields!Category.Value, ".").length = 2 AND Left(right(Fields!Category.Value,len(Fields!Category.Value)-InStr(Fields!Category.Value,".")),3) = "A02",
    "#ff0000",
    IIF(Split(Fields!Category.Value, ".").length = 3 AND Left(right(Fields!Category.Value,len(Fields!Category.Value)-InStrRev(Fields!Category.Value,".")),4) = "A01A",
    "#0000ff","#00ff00")))),
    Left(Fields!Category.Value,1) = "B", "#fdd9d9",
    Left(Fields!Category.Value,1) = "C", "#fcffd4", 
    Left(Fields!Category.Value,1) = "D", "#8de2de", 
    Left(Fields!Category.Value,1) = "E", "#c6e6fd"
    )

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.



    • Edited by Eric Liu001 Wednesday, August 21, 2019 2:58 AM
    • Proposed as answer by Eric Liu001 Thursday, August 22, 2019 2:20 AM
    • Marked as answer by iPetrus Thursday, August 22, 2019 7:00 AM
    Wednesday, August 21, 2019 2:56 AM

All replies

  • Hi iPetrus

    Sorry for the late reply. 

    i was post the reply in early day (the next day after you post) ,while today when i review the case , the reply was gone .

    May be the network issue . the following is about your issue :

    According to your expression , based on my test , seems it is not possible to refer the array in iif or switch function.

    In your expression you used the array in expression “Left(Split(Fields!Category.Value, ".")(1),3)” return the second split value ,right?

    Seems it is not available , you could try to use the following expression to instead .

    =Switch(Left(Fields!Category.Value,1) = "A",
    IIF(Split(Fields!Category.Value, ".").length = 1,
    "#e4daf3",
    IIF(Split(Fields!Category.Value, ".").length = 2 AND Left(right(Fields!Category.Value,len(Fields!Category.Value)-InStr(Fields!Category.Value,".")),3) = "A01",
    "#8db4e2",
    IIF(Split(Fields!Category.Value, ".").length = 2 AND Left(right(Fields!Category.Value,len(Fields!Category.Value)-InStr(Fields!Category.Value,".")),3) = "A02",
    "#ff0000",
    IIF(Split(Fields!Category.Value, ".").length = 3 AND Left(right(Fields!Category.Value,len(Fields!Category.Value)-InStrRev(Fields!Category.Value,".")),4) = "A01A",
    "#0000ff","#00ff00")))),
    Left(Fields!Category.Value,1) = "B", "#fdd9d9",
    Left(Fields!Category.Value,1) = "C", "#fcffd4", 
    Left(Fields!Category.Value,1) = "D", "#8de2de", 
    Left(Fields!Category.Value,1) = "E", "#c6e6fd"
    )

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.



    • Edited by Eric Liu001 Wednesday, August 21, 2019 2:58 AM
    • Proposed as answer by Eric Liu001 Thursday, August 22, 2019 2:20 AM
    • Marked as answer by iPetrus Thursday, August 22, 2019 7:00 AM
    Wednesday, August 21, 2019 2:56 AM
  • Thank you very much, it works just like I need! I've tried to mark you reply as answer, but site shows me "unknown error" every day, whether I left comment or mark reply as answer. Anyway, your answer is very helpful, thank you again.

    P.S. I like grouping you made. I'm new in SSRS reports. Is it hard to make grouping like you did?

    Thursday, August 22, 2019 7:07 AM
  • Hi 

    The group expression is below:

    Main group:

    =LEFT(Fields!Category.Value,1)

    sub group:

    =iif(
    Split(Fields!Category.Value, ".").length >1,
    right(left(Fields!Category.Value,30),16)
    ,nothing
    )

    detail group

    =iif(
    Split(Fields!Category.Value, ".").length >2,
    right(Fields!Category.Value,18)
    ,nothing
    )

    Here , i just use the detailed number to set the group , while in fact ,it seems not allowed , because you do not know the detailed string length of the value . while if you use the

    =iif(
    Split(Fields!Category.Value, ".").length >2,
    Split(Fields!Category.value,".")(2)
    ,nothing
    )

    as the group expression, seems it not possible . when the length is less than 3 , it would still calculated the expression Split(Fields!Category.value,".")(2) , which the index is out of range , then you could get error . 

    So, i was just assume you have already do the group ,then i just try to simulate the group with the above group expression . in your case , seems it is not good to find a common group expression ,as least i could not .

    Any way, thanks for your support and understanding.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Thursday, August 22, 2019 9:21 AM