I’ve come across situations before and again this morning where I had to compile information in one report from multiple datasets.   Usually this is no problem, however this morning I found myself needing to use the results from one dataset to filter out results from a separate dataset.  My first data set grabbed a distinct list of codes from an access database on a network share and I needed to feed that list as a filter to a second query to a SQL Server table.  My solution was to store that data from the Access query in a report parameter that I could then feed into the second query.  To do this I created a report parameter, set its internal attribute to true (checked), and defaulted its values to that of the query from the Access database.  In my second query I then used the report parameter name in the IN statement of the WHERE clause to provide the filtering, as such:

 

SELECT <column_name>

FROM <table_name>

WHERE <column_name_to_filter> IN (@ReportParameter)

 

This solution works beautifully, but is limited to simple result sets.  I’m still interested in finding solutions to handle more complex data from multiple sources. 

 

Hope this helps.  Happy Reporting!