CODEWITHSUNDEEP
reporting-services
Miguel Figueiredo
Miguel Figueiredo

Reputation: 3

SSRS Data from one dataset not in another

I've been searching for a possible answer to my problem but was unable to find one. Lets assume that I have 2 datasets. dsMediaServerData has two columns: id and channel. I have other dataset (dsCatalogData) with a lot of columns including id and channel. I want to filter dsCatalogData to show only those records that are not in dsMediaServerData.

Upvotes: 0

Views: 880

Answers (1)

Jesse Potter
Jesse Potter

Reputation: 847

My recommendation would be to update the SQL queries used to populate the datasets so that duplicate id/channels are excluded.

If you can't do this directly through SQL because the data sets are coming from different DB's, you can accomplish this with the use of a parameter.

1) Let's start with the first dataset (dsMediaServerData in your example). Since your example shows filtering by 2 columns, but the data will be travelling through a single parameter, you will need to create a new column that combines both pieces of data.

SELECT Convert(VARCHAR(255), id) + '_' + Convert(VARCHAR(255), channel) as 'combined_columns_to_filter'

2) Now create a new parameter (let's call it @filter).

  • Set the data type to text
  • Check "Allow multiple values".
  • Open up "Default Values" and choose "Get values from query". Choose the dsMediaServerData data set and select the new column 'combined_columns_to_filter' as the value field.
  • Set the visibility to "Hidden" so that users don't try to interact with this parameter.

3) Now update the second dataset. Add the @filter parameter to the WHERE clause. 

WHERE Convert(VARCHAR(255), id) + '_' + Convert(VARCHAR(255), channel) NOT IN (@filter)

This should effectively filter the second dataset by removing all records found in the first data set even though the data sets are in separate databases.

Upvotes: 1

Related Questions