How to perform a LEFT JOIN (lookup) function in SSRS RRS feed

  • Question

  • Hi all,

    I have two datasets in my SSRS report. One dataset containing an MDX query, another dataset containing a T-SQL query.

    Imagine the first dataset returns :

    Shop A : 500 sales

    Shop B : 600 sales

    Shop C : 200 sales

    Imagine the first dataset returns : 

    Shop A : 200 tickets

    Shop B : 400 tickets

    Shop D : 100 tickets

    I know how to perform a lookup function in SSRS, but how can I perform a LEFT JOIN / UNION between the different datasets? The lookup function will not add shop D in this case.

    Any help/suggestions?

    I know there are other options such as integrating the T-SQL query in MDX and integrate it in the other dataset, but I would like to keep this solution for now.


    Tuesday, October 11, 2016 8:15 AM


  • Hi Yvanlathem,

    According to your description, your report is containing two DataSets. In the two DataSets there exists same columns with different values. And you want to use expression like LookUp() function to combine the two DataSets with all values into one tablix. Right?

    In your scenario, as you said you don't want to do the integration at DataSet side. However as I know there's no built-in function in current Reporting Service that can achieve the requirement like yours. Because currently if we want to combine different DataSets into one table, the built-in functions are LookUp, LookUpSet or MultiLoopUp. All the three functions are based on one DataSet and find a matching field in some other DataSet. So as in your sample data, the DataSet1 isn't containing the shop D thus the LookUp function will not add shop D if the function is based on DataSet1.

    Also, there's a little trick that you can refer to. Since in your sample data, the Shop D is at the last row of DataSet2. So you can add new row in the table then give it value by using Last() function like =last(Fields!Shop.Value, "DataSet2"). Please refer:

    If you still have any questions, please feel free to ask.

    Xi Jin.

    • Proposed as answer by Xi Jin Monday, October 17, 2016 8:32 AM
    • Marked as answer by Xi Jin Wednesday, October 19, 2016 7:11 AM
    Wednesday, October 12, 2016 7:02 AM