Display resultset from two different db servers into one report in SSRS with parameter selection

Question

I've two tables in two db servers with same structure. I'm preparing a SSRS report with tablename & joining date as parameter selection. If I select table1 [dropdown] and joiningdate from/to date filter then table1 data will display in SSRS report and if I select table2 then it should display table2 data.

table1 cols: eid, ename, eno, joiningdate table2 cols: eid, ename, eno, joiningdate

N.B: there should be one dataset and one report

Answer 1

One approach is to have 2 datasets one from each server. Then have 2 tablix for the corresponding dataset. Depending on the parameter selected, you show the corresponding tablix and hide the other one..

If your @source paramameter had a value of 1 (for source one) and 2 (for source 2)

You could go one step further and pass that to your query.. like for dataset1 you can include where @source = 1 and your dataset2 says where @source = 2

This would prevent the code from running unnecessarily on the server that it is not intended for.. makes sense?

Answer 2

If you have the 2nd server linked from the first server then you can just do something like this from the first server ..

IF @Server = 1
    BEGIN
        SELECT eid, ename, eno, joiningdate FROM table1
    END
ELSE
    BEGIN
        SELECT eid, ename, eno, joiningdate FROM [MyLinkedServerName].[myDatabaseName].[mySchemaName].[table1]
    END

... where @Server is the parameter name passed in from SSRS