How to perform a LEFT JOIN (lookup) function in SSRS
Question
Answers
-
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.
Thanks,
Xi Jin.
