SSRS – Dynamically Change Tables in Data Source

reportingsql-server-2017ssrs

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?

Best Answer

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

Related Solutions

Expose Temp Tables to SSRS in Stored Procedure

I believe you can get what you want by doing the following:

(1) combine function and procedure into 1 select statement
(2) add grouping by Year to single SELECT
(3) I added recursive CTE to generate "HOUR" dimension and use this instead of getting hour from actual data; this will show hours with 0 then

WITH HourOfDay ([hr]) AS  
(  
  SELECT 1 as [hr]  
  UNION ALL  
  SELECT [hr] + 1  
  FROM HourOfDay  
  WHERE [hr] < =24       
) 
SELECT   
    DATEPART(YEAR,AC.[FIELDC]) AS [Year]  
    --DATEPART(HOUR,AC.[FIELDC]) AS [Hour],   
    hod.hr AS [Hour],  
    COUNT(DISTINCT(AC.[FIELDD])) AS [CFS_Count_1]  
FROM [LINKEDSERVER].[DATABASE].[dbo].[TABLE] AS AC WITH (NOLOCK)  
JOIN HourOfDay AS hod  
    ON hod.[hr] = DATEPART(HOUR,AC.[FIELDC])  
WHERE (AC.[FIELDA] LIKE 'VALUE' OR AC.[FIELDA] LIKE 'VALUE')    
AND AC.[FIELDB] = 'VALUE'  
AND (CAST(AC.[FIELDC] AS DATE) >= @SDate AND CAST(AC.[FIELDC] AS DATE) <= @EDate)  
GROUP BY   
    DATEPART(YEAR,AC.[FIELDC]),  
    DATEPART(HOUR,AC.[FIELDC])

(4) You can add/use SSRS parameter for number of years, pass that to procedure, but also possibly use it to hide columns for years not desired to show on report.

SQL Server – How to Pass Multi-Valued Characters in SSRS Report

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...

Best Answer

Related Solutions

Expose Temp Tables to SSRS in Stored Procedure

SQL Server – How to Pass Multi-Valued Characters in SSRS Report

Related Question