Multiple SSRS datasets all with same columns, needing best approach to join and use parameters

Question

I have 14 SSRS datasets which all contain the same columns (Name, Date, Code etc).

What would be the best way to merge them all together that would allow me to add a parameter or two which would allow filtering of the combined data?

I have tried using report parts, but found that the parameters were not working, and also that there were gaps in the reporting once exporting into excel.

Any help gratefully received. Thanks Glenn

Answer 1

I think your best approach would be to do this in SQL. You would create a stored procedure that would union the data together. If the data is on different servers, you would need linked servers. Is this approach feasible in your environment ?

CREATE PROCEDURE [dbo].rsp_XYZ
    @param1 varchar(100) = NULL
    ,@param2 varchar(10) = NULL
AS
BEGIN

    Select * From 
    (
        Select Name, Date, Code, etc...  From X1
        UNION ALL
        Select Name, Date, Code, etc...  From X2
        UNION ALL
        Select Name, Date, Code, etc...  From X3
    ) a

    Where param1 ...
    and param2 ...

END