title: merge results from 2 datasets from 2 different databases in SSRS
body: <p>I have two SQL Server 2008 databases and am using SSRS 2008 R2. in DB1 I have a list of ALL rooms with services that occupy those rooms. Some of these rooms do not have a service recorded against them. These rooms (and their services) are recorded in DB2. </p>
  
  <p>In DB2 I have small number of rooms with a list of services that use those rooms. The rooms that appear in the DB2 are present in DB1, but in DB1 they do not have any services recorded against them. </p>
  
  <p>I want to merge the results from both datasets into one tablix so that I show all rooms from DB1 plus the rooms from DB2 so that all rooms show services against them. Just wondering what the best way to do this is? I have a field on both DBs that match. DB1.RESOURCES.ROOM_NAME &amp; DB2.ROOMS.DB2_ROOM_NAME. But this is really the only similarity as both DBs have different table and field names. </p>
  
  <p>Would I just use a join on DB1.RESOURCES.ROOM_NAME = DB2.ROOMS.DB2_ROOM_NAME</p>
  
  <p>Or should I use Lookup function here.  </p>
  
  <p>Any guidance on how to achieve would be appreciated. </p>