I have an SSRS report that I have set up to query two tables, let's call them
ABC_AuditDetail. I have now added
XYZ_AuditDetail to the same database, and want to be able to switch between querying the
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?