Nested lookup in SSRS 2008R2

  • Hello Guys, I am facing the following scenario:

    Datasource 1 : MS SQL server

    Dataset 1 : Select patient_uid, patient_id from insurance_info

    Datasource 2 : Oracle server

    Dataset 2 : Select patient_id,member_num from member_info

    Result : Patiend_uid, Patient_ID, Member_num

    As these are on different servers and there are no linked servers or any other form of connectivity between them, I have to join these datasets on the report level only.

    I used Lookup function as follows to get the patient_id:

    =Lookup(CInt(Fields!pat_uid.Value),Fields!PATIENT_UNIQUE_ID.Value,Fields!PATIENT_ID.Value, "Dataset1")

    But, now from this column/row value, I need to use another lookup to get the member_num

    =Lookup(CInt(Fields!PATIENT_ID.Value),Fields!PATIENT_ID.Value,Fields!MEMBER_NUMBER.Value,"DataSet2")

    But the first parameter in the above statement is being calculated on the fly,its not a field, the above statement is not working. Nested lookup is also not allowed. Can anyone help me in this one please??

    Thanks in advance.

  • Any ideas guys..

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply