Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Consecutive execution of n parameters in a report Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 4:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 3, 2014 7:29 AM
Points: 11, Visits: 79
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?
Post #1375957
Posted Friday, October 26, 2012 7:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:53 AM
Points: 1,293, Visits: 1,557
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
Post #1377555
Posted Friday, October 26, 2012 7:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:53 AM
Points: 1,293, Visits: 1,557
gsc_dba (10/26/2012)
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.



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
Post #1377569
Posted Monday, October 29, 2012 1:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 3, 2014 7:29 AM
Points: 11, Visits: 79
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.
Post #1378144
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse