CODEWITHSUNDEEP
reporting-serviceslookupiif
Curteze
Curteze

Reputation: 17

#ERROR Help, SSRS Report Builder, IIF w/ LOOKUPSET

I'm trying to compare 2 rows of data in the form of %'s. I generate and it "#Error".

=IIF(Fields!Grade.Value = "ONGRADE" > LookupSet(Fields!Grade.Value = "ONGRADE", Fields!grade.Value = "ONGRADE", Fields!grade.Value = "ONGRADE", "Previous3Week"), "UP" ,"DOWN")

There are two DataSets.

Upvotes: 0

Views: 876

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

You are using IIF incorrectly. IIF just looks at a comparison and returns the first value if TRUE and the second value if false.

=IIF(1 = 2, True, False)

Which reads as

If 1 = 2 then return TRUE else return False

You are also using LookUpSet incorrectly. The first LookUpSet argument is your current dataset field that you want to compare, the second argument is the field from the first that you want to compare to - since your using the same dataset, they might be the same. The third LookUpSet argument is the field that you want to return (you know the ONGRADE field, what value do you want back?).

Your expression reads, if Grade = ONGRADE > LookupSet(blah blah) ...

What is the value field that you want to compare? Assuming it's Fields!GRADE_VALUE.Value, your IIF might be like 

=IIF(Fields!Grade.Value = "ONGRADE", 
    IIF(Fields!GRADE_VALUE.Value > 
        LookupSet(Fields!Grade.Value, Fields!grade.Value, Fields!GRADE_VALUE.Value", "Previous3Week"), 
        "UP" ,
        "DOWN"),
"Not ONGRADE")

If you want all GRADE types (not just ONGRADE) compared, it would be simpler: 

=IIF(GRADE_VALUE > LookupSet(Fields!Grade.Value, 
                Fields!grade.Value, 
                    Fields!GRADE_VALUE.Value, 
                        "Previous3Week")
    , "UP" 
    ,"DOWN")

Upvotes: 2

Related Questions