Dynamically changing tables in datasource in SSRS I have an SSRS report that I have set up to query two tables, let's call them ABC_Audit and ABC_AuditDetail. I have now added XYZ_Audit and XYZ_AuditDetail to the same database, and want to be able to switch between querying the ABC... or XYZ... tables in the report. The tables are set up exactly the same, so the only part of the query that would need to be changed is the table name in the FROM ... clauses.

In my non-DBA, not-very-SSRS-knowledgeable eyes, there might be two ways to achieve this 


either dynamically change the data source (have one data source for each set of tables to query), 
or use a parameter in the SQL query to choose between the sets of tables to query, either of which is accessed using a drop-down on the report page.


Creating a separate report for each set of tables is not a viable option, as the number of sets will grow over time, and keeping all those reports up-to-date if/when it changes will be grueling.

Does anyone have any advice on how to achieve this, and which option would be the better option to pursue?
Given that you expect the number of data sets to grow over time, your best bet would be to create a stored procedure in the database that uses a parameter value to determine the source tables and dynamic SQL to build the query.

Create an SSRS parameter to match and pass this value through to the data set and the stored procedure will return the results from the table specified in the parameter.

Basic Example:

CREATE PROCEDURE spGetData
(
    @SourceTable NVARCHAR(255)
)
AS
BEGIN

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT [Column1], [Column2], [Column3]
    FROM [dbo].[' + @SourceTable + '_Audit]'

    EXEC sp_executesql @SQL
END