CODEWITHSUNDEEP
reporting-servicesparameters
tomdemaine
tomdemaine

Reputation: 758

SSRS Report Builder 3.0 Can I use a parameter for a table name?

I have a report that pulls from a SQL server database. The current FROM looks like

from wrapuptime 
inner join 
[iData3].[dbo].[N158] on [iData3].[dbo].[N158].AGENTNAME = wrapuptime.Agentname

I want to paramatise this report so that I can have multiple Idata3 tables and pull from different ones depending on which option the person running te report picks.

I have tried creating a parameter called teamleader and having the Value field as

="[idata3].[dbo].[N241847]"

with the code becoming

from wrapuptime 
    inner join 
    (@Teamleader) on (@Teamleader).AGENTNAME = wrapuptime.Agentname

but this doesn't work. Have I messed up the syntax or is this not possible. Thanks.

EDIT :

After a helpful comment below I went through the dynamic SQL tutorial and ended up with the following code

DECLARE 
    @table NVARCHAR(128),
     @sql NVARCHAR(MAX);
    SET @table = N'idata3.dbo.N241847';
    SET @sql = N'





select 
distinct callref , 
actid,
--[StartTime],
[StartTime] as [StartTime],
convert(date,format([StartTime],''dd/MM/yyyy''),103) as [Date],
 Calltype,
 inbtot As [Inbound Time],

actTime-(inbtot+IsNull(outtime,0)) as [WrapTime],



outtime,
 wrapuptime.Agentname,
 Agent_Site,
 Client_name, 
 Teamleader

from wrapuptime 
inner join ' +
@Table + ' on ' + @table+'.AGENTNAME = wrapuptime.Agentname
LEFT JOIN 
inboundcallsview ON [wrapuptime].[callref]  = inboundcallsview.[Reference] 

where [StartTime] between ''2019-07-01'' and ''2019-07-07''

--Where StartTime between DATEADD(d, -1, DATEDIFF(d, 0, GETDATE())) and DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))



and Agent_Site = ''Nottingham''



AND [account] IN (

SELECT Accountnumber from idata3.dbo.[N242024]

)


order by starttime'

EXEC sp_executesql @sql

Which despite being a huge mess does work perfectly in SSMS.

However when I copy and paste it into my query window in report builder 3.0 and hit Run to test it I get the option to put in a value for @table and when I do I get the following error message

error

I thought the DECLARE at the top would have sorted this issue. Am I now missing something regarding the transfer of the query from SSMS to RB 3.0?

Upvotes: 0

Views: 454

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

I normally get the data from the 'dynamic' table into a temp table and then the rest of the dataset query does not need to change.

For example something like this.

/* uncomment for testing - comment these two lines in SSRS dataset query
DECLARE @schemaName varchar(256) = 'dim'
DECLARE @tableName varchar(256) = 'country'
*/

DECLARE @sql nvarchar(max)

DROP TABLE IF EXISTS #t
CREATE TABLE #t (ColID int, ColName varchar(75), ColAnother varchar(20)) -- <= this should be able to handle any of your dynamic tables

SET @sql  = 'INSERT INTO #t SELECT CountryID, CountryDesc, CountryCode FROM ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)

EXEC (@sql)

SELECT * 
    FROM MyOthertable a
       JOIN #t t on a.CountryID = t.ColID

In the example above I'm just doing some dynamic sql to populate the temp table and then I use the temp table in the main part of the query. Just make sure your query that populates the temp table always returns the same data types as the temp table.

Upvotes: 2

Related Questions