Search code examples
reporting-servicesssrs-2012

SSRS IIF Condition

i have one report where multiple condition going to apply like 100% 75% and 50%

i used background colour condition

=iif(count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "8" and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")
= "100%" ,"Green","Red") and iif(
 count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value,Fields!SINFO.Value, "FullTime"))>= "6" and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")
= "75%" ,"Green","Red") and iif(
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4" and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")
= "50%" ,"Green","Red")

i'm getting no colour please let me know what im doing worng as im new in SSRS Thanks 

=Switch(Fields!Date.Value >= CDate("01/05/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("01/10/" & Str(Year(Parameters!EndDate.Value))) and
 count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "8"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "100%",
"Green",
Fields!Date.Value >= CDate("01/11/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("30/04/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "100%",
"Green",
Fields!Date.Value >= CDate("01/05/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("01/10/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value,Fields!SINFO.Value, "FullTime"))>= "6"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "75%",
"Green",
Fields!Date.Value >= CDate("01/11/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("30/04/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "3"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "75%",
"Green",

 Fields!Date.Value >= CDate("01/05/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("01/10/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "50%" ,
"Green",
Fields!Date.Value >= CDate("01/11/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("30/04/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "2"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "50%",
"Green",
true,"Red"
 )
Solution

  • You were letting the flow fall through the wrong way. I modified your formatting for clarity.

    =iif(
    lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")= "100%"
    and    
    (
        (
            IS_SUMMER() 
            AND 
            count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "8" 
        )
        OR
        (
            IS_WINTER()
            AND
            count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4"     
        )
    ),
    "Green"

    ,iif(
        lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")= "75%"
        and    
        (
            (
                IS_SUMMER() 
                AND 
                count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "6" 
            )
            OR
            (
                IS_WINTER()
                AND
                count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "3"     
            )
        ),
        "Green"

        ,iif(
            lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")= "50%"
            and
            (
                (
                    IS_SUMMER() 
                    AND 
                    count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "6" 
                 )
                 OR
                 (
                    IS_WINTER()
                    AND
                    count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "3"     
                  )
            ),
            "Green"

            ,"Red"
        )
    )
 )

    Updated with extra fields.

    At this point you can see how horrible this is getting. It is hard on the eyes and hard to maintain and reuse. There are two ways to clean this up.

    1. Use a function in your code to set the color - 

    textbox1.BackgroundColor=Code!MyFunctionToEvaluate(Percent,Number)

    2. Use Calculated Fields - 

    IIF(
    Fields!CALCPercent.Value=100 AND 
    (
        (Fields!CALCIsSummer.Value AND Fields!CALCCount=8)
        OR
        (Fields!CALCIsWinter.Value AND Fields!CALCCount=6)
     )
    ...
)

    Here is a function you can put in your Report|Options|Code window.

    Public Function CalculateColor(percentage As String, count As String, Season As String) AS String    

    Dim Result As String = "Red"

    If(percentage="100%") Then
        Result = "Gree"
    Else If(percentage="75%") Then
        Result = "Gree"
    Else If (percentage="50%") Then
        Result="Green"
    EndIf

    return Result

End Function

    Once that compiles (I did not check syntax) then you can assign the result to a textbox's BackgroundColor.

    textbox.BackgroundCode=<Expression> =Code.CalculateColor(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"),count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")),GET_SEASON())