How to select a different dataset depending on a parameter value in Reporting Services

Question

It is possible to select a different dataset (query) depending on the value of a parameter in Reporting Services? Thanks in advance!

Answer 1

if you call a stored procedure to return your result set, just have a parameter determine what version of the query to run. keep the result set columns and their types the same.

create procedure YourReportProcedure
(
     @ReportVersion    char(1)
    ,@filterParam1     varchar(12)
    ,@filterParam2     int
    ....
)

if @ReportVersion='A'
BEGIN
    SELECT
        A,B,C
        FROM .....
        WHERE x=@filterParam1 and y=@filterParam2

END
ELSE IF@ReportVersion='C'
BEGIN
    SELECT
        A,B,C
        FROM .....
        WHERE g>@filterParam1 and r<@filterParam2

END
ELSE
BEGIN
    return 1 --error
END

return 0

go