How to filter a SSRS report based on 2 datasources

Question

I have created a stored proc where I have data coming from 2 data sources. I have created a Union between source A and source B and then feeding this as a dataset to the SSRS report. Now the user wants to see the report based on a source parameter which will give them the ability to select/filter the report based on the data sources either "source A", "source B" or "both". So if user selects source A , then report should show data from source A only and if "both" then it will show results from both source A and source B.

One way , I am trying to tackle this problem is creating 3 separate data sets and then using a parameter. I am not sure if that's the best design approach. Please share if anyone has any better ideas.

Thanks in Advance !

Answer 1

You could simply tag an extra column onto your query with the source identified something like

SELECT *, 'A' as Src FROM myFirstDataSource
UNION
SELECT *, 'B' as Src FROM mySecondDataSource

Then just apply a filter on the Src column.

Alternatively just do this in the SP itself by passing a parameter in from the report to determine what data to fetch, something like.

CREATE PROC myProc (@myParam varchar(1) = '') AS
DECLARE @t TABLE (Src varchar(1), ColumnA varchar(10), ColumnB int, ColumnC int)

IF (@myParam = '' OR @myParam = 'A')
    BEGIN -- get data from first datasource
        INSERT INTO @t
        SELECT 'A', someCOlumnA, SomeColumnB, SomeColumnC FROM myFirstDataSource
    END

IF (@myParam = '' OR @myParam = 'B')
    BEGIN -- get data from second datasource
        INSERT INTO @t
        SELECT 'B', someCOlumnA, SomeColumnB, SomeColumnC FROM mySecondDataSource
    END

SELECT * FROM @t

You don't really need the Src column in the second option but it might be useful in case you want to highlight where data comes from in the report.