Filter dataset if row exists in antoher dataset

Question

Is it possible to filter a dataset if a column value exists in another dataset?

For instance, if I have a dataset (Dataset A) of clients (client_ID) but I want to exclude any clients in Dataset A that exist in another Dataset B.

Answer 1

Not something I have tried, but I think it could be done using data set filters in report builder. Alternative you could do it in the SQL. If you put the 1st data set into a temp table and join it on the the main query using a <> on client I'd

Answer 2

Hi Stoneb,

Yes, we can do it using a parameter, the steps below are for your reference.

    1. Add a parameter to your report and checked the “Allow multiple values” option.
    2. In the available Values tab, select “Get values from a query” like below:
        Dataset:
        DataSetB
        Value field:
        clients_ID
        Label field:
        clients_ID
    3. In the Default Values tab, select “Get values from a query” like below:
        Dataset:
        DataSetB
        Value field:
        clients_ID
    4. In the DataSetA, change the query like:
        select * from tablename where clients_ID not in(@parametername)

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

Regards,
Charlie Liao

---

Charlie Liao

TechNet Community Support