none
Join dataset 1 and dateset 3 together using dataset 2 as a common link. Is this possible using a lookup function? RRS feed

  • Question

  • In the example below I have 3 datasets coming from different data sources. These data sources are not linked so I can't do a join using SQL. I need to join dataset 1 to data set 2 then join dataset 2 to dataset 3 to get final results of 1 column from ds1 and 1 column from ds3. Is this possible using Lookup, LookupSet, Multilookup? I have been searching and only find using these when only using 2 datasets which I have done before just fine.

    Using Report Builder 3.0

    Thanks!


    • Edited by rizz0555 Thursday, September 19, 2019 1:32 PM typo
    Thursday, September 19, 2019 1:31 PM

All replies

  • Hi Rizz0555,

    Not sure what's the data looks like in your datasets, whether there were duplicated data in it for keys you choose.

    From my humble opinion, for three datasets to use LookUp or LookUpSet would not be logically very easy and clear.

    So I suggest you to try to merge these data with dynamic SQL in the query. You could take a look into this tutorial, this will show you a good practice. SSRS: Merge data from different datasources into one dataset inside SSRS report.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 20, 2019 3:10 AM
  • Hi Lukas,

    I appreciate the response. Only Dataset1 in my example would have duplicates. 

    I took a quick look and it claims to be able to join/merge multiple data sources into 1 dataset.  This is the most promising solution I have seen so far. Later in the week when I have time to work on this I will report back if I was able to get it to work.

    Thanks!!!

    Monday, September 23, 2019 12:56 PM
  • OK, I'll wait for your feedback after you work on this case.

    Good day!


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, September 24, 2019 8:11 AM