October 23, 2012 at 4:51 am
Hi all!
I have one dataset called DatabasesOnServer:
SELECT name FROM
sys.databases
Report Parameter is string, from query, and from Dataset above - DatabasesOnServer.
The other dataset in my report is called TablesOnTheSameServer:
= "SELECT * FROM " & Parameters!DatabasesOnServer.Value & ".sys.tables
Basically, in my Table, I would like to show all tables in Server Databases.
Table uses TablesOnTheSameServer dataset.
In a report preview, when I choose one database, everything works fine - I get all tables from
one database selected.
My question is:
Is there a way to show my table n times in the report, depending on the n - number of databases,
without having to choose parameter - exact database name?
For example: If Data set DatabasesOnServer returns three databases - A, B, C - my Table shows A.tables, B.tables, and C.tables?
Thanks everyone in advance!
P.S. I'm aware that I could create different datasets and different Tables depending on those datasets, for each database on the server. But, the structure of the table is always the same, so I thought maybe there's a way to create only one Table that uses something like For Each ParameterName(database), run report consecutively?
October 26, 2012 at 7:02 am
I think you can use the JOIN function in this situation
= "SELECT * FROM " & JOIN(Parameters!DatabasesOnServer.Value) & ".sys.tables
Actually, you need a while loop which takes the parameter selection(s) and iterates over the SELECT * FROM [DataBaseNameGoesHere].[sys].[tables] AS T replacing the [DataBaseNameGoesHere] for each iteration.
gsc_dba
October 26, 2012 at 7:19 am
gsc_dba (10/26/2012)
I think you can use the JOIN function in this situation
= "SELECT * FROM " & JOIN(Parameters!DatabasesOnServer.Value) & ".sys.tablesActually, you need a while loop which takes the parameter selection(s) and iterates over the
SELECT * FROM [DataBaseNameGoesHere].[sys].[tables] AS Treplacing the [DataBaseNameGoesHere] for each iteration.
Something like this:
DECLARE @dbnames TABLE
(
id TINYINT IDENTITY(1, 1)
NOT NULL
, dbnames VARCHAR(150) NOT NULL
)
INSERT INTO @dbnames
(
[dbnames]
)
SELECT
[name]
FROM
sys.[databases] AS D
WHERE
[database_id] > 4
DECLARE
@counter INT = 1
, @maxRecordsToProcess INT
SET @maxRecordsToProcess = (
SELECT
COUNT(*)
FROM
@dbnames
)
DECLARE @dsql NVARCHAR(MAX)
WHILE @counter <= @maxRecordsToProcess
BEGIN
DECLARE @dbname VARCHAR(150) = (
SELECT TOP 1
dbnames
FROM
@dbnames
WHERE
@counter = id
)
SELECT
@dsql = 'SELECT * FROM ' + @dbname + '.[sys].[tables] AS T'
EXEC [sys].[sp_executesql] @dsql
SET @counter += 1
END
gsc_dba
October 29, 2012 at 1:45 am
Thanks for the reply!
I also populated the table with dinamic query, and used grouping in the layout, so I think that this problem will be solved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply