SSRS 2008 query : Table name from parameter selected

0
votes

I have database containing table from my sensor name. First I try to get my table name with query like in bottom to get specific table sensor name

SELECT name AS TableName FROM sys.tables AS tbl WHERE (name LIKE 'ATRH.%')

Then the result is like this on SSRS:

enter image description here

But then how do I use that parameter as the table name in my query datasheet?

I already tried a simple query like in the bottom, but I get an error message, SSRS didn't recognized @PARAMETERTABLENAME

SELECT TOP (1000) [SensorID], [Time] ,[Value] FROM @PARAMETERTABLENAME where (Time BETWEEN @Start AND @End)

How can I fix this?

3
sql-serverreporting-servicesssrs-2008
Create a stored procedure, pass the tablename to it, then build a dynamic SQL query and return the results. - Dale K
Go back, start over - there is a mountain of problems you will face using this schema. Someone has decided to make a table for each object rather than entity. Even if you could do what you desire, your code assumes that all of these tables have the same set of columns. - SMor
@SMor in my DB, i have 20 different sensor table name now, but it will grew up again until 100 and more. some table have same set column. i just want make it simple, like 10 table handled by 1 Reports, so then i just only make 10 reports. that's why i need this method. i didn't want make too much Reports file. it will be make user confuse. - herdin jaya
@DaleK i will try your suggest coz it make a sense how exactly to out from this case. i will learn it coz it first time for me learn SSRS in my entire life. thanks - herdin jaya

3 Answers

0
votes

You can use

EXEC sp_executesql @sql_cmd

I've tried like this:

DECLARE  @table varchar(50)
@table='[tmp_in_tashilat'+'_'+@rmz+']'

set @sql_cmd='select   COUNT(*)as cnt,ki into ki  from '+@table+' where (ki is not null) and ki<>'''' and knd='''+@compare+''' group by  ki  having COUNT(*)>1 ' 

EXEC(@sql_cmd)

you can use this template for your own query

0
votes

I think it use full first you must write this in Query=>Query section of Dataset

EXEC('SELECT TOP (1000) [SensorID], [Time] ,[Value] FROM '+@PARAMETERTABLENAME+' where 
(Time BETWEEN '''+ @Start +''' AND '''+ @End+''')'

and after that set each parameters that use in Parameters section of Dataset and set to parameter that you declare in Parameter section of Report

0
votes

i have the answer after doing many time in this week, i try select database name for parameter with this query :

Select name AS SchemaName from sys.schemas
where name not in (
'guest',
'INFORMATION_SCHEMA',
'sys',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter')

and then i try select tablename using dbname parameter with this query :

SELECT DISTINCT TABLE_NAME AS TableName
        FROM            INFORMATION_SCHEMA.TABLES
        WHERE        (TABLE_TYPE = 'BASE TABLE') AND (TABLE_SCHEMA = @SchemaName) AND (TABLE_NAME LIKE 'ATRH.1.%' or
                                 TABLE_NAME LIKE 'ATRH.2.%' or
                                 TABLE_NAME LIKE 'ATRH.3.%' or
                                 TABLE_NAME LIKE 'ATRH.4.%')
        ORDER BY TableName

after then i add more parameter with date "start" and "end" to get data in that 2 range date

in sqlserver i create a stored produres with this query

ALTER PROCEDURE [dbo].[testAtrh]
@TableName nvarchar(128),
@Start nvarchar(128),
@End nvarchar(128)

AS
BEGIN
declare @sql NVARCHAR(MAX)

    SET @sql = N'SELECT [SensorID], [Time], [Value]
    FROM' +quotename(@TableName)+ 
    'WHERE ([Time] BETWEEN '''+CONVERT(nvarchar(128), @Start, 121) +''' AND '''+ CONVERT(nvarchar(128), @End, 121)+ ''')ORDER BY Time desc'
    EXEC sp_executesql @sql
    
END

it work on my ssrs report with sql server 2008, thanks for help from u all