reporting services - SSRS Compare Two Datasets For Missing Ids

Question

In SSRS report I have two data sources from two different servers. I have a dataset for each data source and would like to return in a tablix the ids that are in dataset 1 but not in dataset 2. So if dataset 1 has ids 1,2,3,4,5 and dataset 2 has ids 1,2,3 the report should display 4 and 5. I cannot link the servers. Thanks.

question from:https://stackoverflow.com/questions/65649053/ssrs-compare-two-datasets-for-missing-ids

Answer 1

There are several ways to do this.

---

Common Lookup method

This is the way most people would probably do this

---

Use the Lookup() function.

Set the tablix row's hidden property to something like

=ISNOTHING(
 Lookup(Fields!IDa.Value,Fields!IDb.Value,Fields!IDb.Value,"Dataset2")
) = False

The above, for clarity, assumes the ID column in dataset1 is called IDa and the ID column from dataset2 is called IDb. It will stil work if they have the same name (e.g. 'ID')

Note: Dataset name must be in quotes and is case sensitive.

Using this method returns all the rows and simply hides the ones that do not match your criteria. This may not be ideal if you're exporting the data. If not, see the alternative version below.

---

Alternative method

For reasonably small datasets - parameter method ... and because I thought it was an interesting approach...

---

This second method uses a hidden parameter and is easy to setup assuming you have a reasonable small number of records.

Using your example, create a parameter called List2 and set its default and available values BOTH to your Dataset2 query (from your example above). Make the parameter multi-value. You can make this parameter hidden once it's working.

Now Your Dataset1 query can be a simple query like this,

SELECT * FROM Table1 WHERE id NOT IN (@List2)

@List2 will contain the values from datset2 (1,2 and 3) so the query will return only the remaining values.

Note I named the datasets to match your example but the datasets must be created in the order above.