0

I'm using SSRS 2008 R2 and building a report with Report Builder 3.0. I need to query 2 databases, a Central one here at headquarters and a Satellite one in Australia. They both have a table called Buyers. The satellite site just contains a smaller set of data that we will return to central at a later date. They have the following fields; idBuyer, IdCustomer, DateRegistered Right now I want to query all the records in the Satellite site for Customers (idCustomer) that are not already in the same table in the central datasource I’m using a LookUp using idBuyer to join the datasets in a Tablix. Now I just want to filter for results where idCustomer from the Satellite dataset is not in the Central Dataset

Any pointers would be appreciated as I assume this would be pretty simple but I’m not getting it to work

KD84
  • 15
  • 1
  • 8

1 Answers1

0

Ideally you should do this in SQL. It would make the report a lot quicker.

SELCT ID FROM SATELLITE WHERE ID NOT IN (SELECT ID FROM CENTRAL)

However, that's not always possible so you can filter the Satellite db where the ID is not in the CENTRAL ID using the InSTRing funtion.

For your filter, set the Expression to idBuyer 

=IIF(INSTR("|" & Fields!idBuyer.Value & "|", "|" & JOIN(LookupSet(1, 1, Fields!idBuyer.Value, "CENTRAL"), "|") & "|"), 0, 1)

This will create a string of the IDs from CENTRAL db and look if the ID from the Sattelite DB is in it. If the ID is in the CENTRAL db, the row gets a 0 else 1. Then set the Value to 1.

enter image description here

The Pipes ( | ) is used to separate the values so it doesn't find 25 when you have 125 or 251 in the list.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39