How to use a IIF with a Lookup expression in SSRS

Question

I have the main dataset named: A, and I need to grab a Percentage column value from dataset named: B but only where the inspection column from dataset B equals "VC". Reminder both the Percentage and Inspection are in dataset B which is different from the main dataset A. Both datasets have a linked/joined column called PhaseTrackingID.

This is what I have currently and it's only returning Nothing:

=IIF( Lookup(Fields!PhaseMaintTrackingId.Value, Fields!FK_PhaseMaintTrackingId.Value, Fields!Insp.Value, "Perc_Comp_By_Phase_INSP_Type") = "VC" And Lookup(Fields!PhaseMaintTrackingId.Value, Fields!FK_PhaseMaintTrackingId.Value, Fields!PhaseInspDesc.Value, "Perc_Comp_By_Phase_INSP_Type") = "INTERMEDIATE 12MO", Lookup(Fields!PhaseMaintTrackingId.Value, Fields!FK_PhaseMaintTrackingId.Value, Fields!percentComplete.Value, "Perc_Comp_By_Phase_INSP_Type"), Nothing )

One part of my problem is that I realize that lookup only return the first value which isn't "VC" so returns false.

Any help will be greatly appreciated!

Answer 1

You can use an expression combining the two columns for your lookup.

=Lookup(Cstr(Fields!PhaseTrackingId.Value) + "VC", Cstr(Fields!PhaseTrackingId.Value) + Fields!Inspection.Value,   Fields!Percentage.Value, "dsB")