SSRS Lookup Variable when the common key between 2 datasets requires 2 fields


  • Hello I have a newbie question about using a lookup variable (SSRS 2012).

    Lookup(source_expression, destination_expression, result_expression, dataset)

    The challenge for me in creating a lookup variable this time is that there is no single key between my 2 datasets. However, if I could use 2 fields to define my source and destination expressions, then I would be able to accurately link.

    For example, in this case, the Patient_ID field is shared in both datasets, but it alone is not necessarily unique since different facilities could have the same Patient_ID for a different people . Thus, to avoid this error, I would need to use patient Patient_ID along with Facility_ID in order to establish a key between my two datasets.

    Thus the question: Is it possible to create a lookup variable that defines source and destination expressions using 2 fields from each dataset in order to establish the relationship between two datasets?


    Friday, December 20, 2013 9:00 PM


  • Hi DaveDVF,

    Based on my research, we cannot specify two fields as source_expresion or destination_expression in a lookup expression. If you want to specify two fields to establish a key between two datasets, I suggest that you can use two lookupset functions like below to return the correspond results, then use custom code (Right-click report>Report Properties>Code) to write a function to return the same values from the two arrays.
    =join(lookupset(Fields! Patient_ID.Value,Fields! Patient_ID.Value,Fields!col.Value,"DataSet2"),",")
    =join(lookupset(Fields! Facility_ID.Value,Fields! Facility_ID.Value,Fields!col.Value,"DataSet2"),",")

    Hope this helps.

    Katherine Xiong

    Katherine Xiong
    TechNet Community Support

    Sunday, December 22, 2013 11:55 AM