1

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?

  • Correct me if I'm mistaken, but I think SSRS will check all data sources upon execution, to check if they're 'valid'. That would mean having multiple data sources will be a performance hit. You can use dynamic SQL to change source tables as needed and then control this parameter from SSRS. – Mashchax Dec 11 '18 at 13:50
  • It appears to work that way, @Mashchax, so my question would be how do I use dynamic SQL to change the source tables? I tried using a parameter as the table name (i.e. SELECT x FROM (@TableName)) but got Must declare the table variable "@TableName" – tutigan Dec 12 '18 at 1:39
1

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
  • This is correct, however now I'm starting to recall (not sure if it actually happened, lol) that I might have tried this in SSRS at one point, and it had problems returning the results of executing a string as a dataset. @op let us know if this worked! – Mashchax Dec 12 '18 at 9:39
  • 1
    @Mashchax - I've used this method before when needing to dynamically determine source tables. The key is making sure your return columns don't change. Then you can use an example parameter value to set the data set properties in SSRS at design time, and at runtime everything still matches. – HandyD Dec 12 '18 at 23:32
  • Forgive my naivety, but does this all go in the SSRS query? If so, do I encapsulate every SSRS parameter with the quote and plus signs? i.e. WHERE at.UserId = ' + (@UserId) + ' OR ' +(@UserId) + ' IS NULL – tutigan Dec 13 '18 at 4:46
  • Never mind that last comment - I worked it out! I constructed the query as you suggested, and executed the whole block in SQL to create the stored procedure (which makes sense now that I worked that out!). I now have the issue of having NULL parameters... my stored procedure has the following: WHERE at.UserId = '''+@UserId+''' OR '''+@UserId+''' IS NULL If I specify the UserId parameter in SSRS, great, it searches with it. If I leave it blank, I get no results... what am I missing here? – tutigan Dec 13 '18 at 5:25
  • You need to parameterise your dynamic SQL - docs.microsoft.com/en-us/sql/relational-databases/… Example- ` DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX), --@OuterParam VARCHAR(255) = 'master' @OuterParam VARCHAR(255) = NULL SET @SQL = 'SELECT [name] FROM sys.databases WHERE [name] = @InnerParam OR @InnerParam IS NULL' SET @Params = '@InnerParam VARCHAR(255)' EXEC sp_executesql @SQL, @Params, @InnerParam=@OuterParam` – HandyD Dec 13 '18 at 5:33

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.