SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Consecutive execution of n parameters in a report


Consecutive execution of n parameters in a report

Author
Message
gdebeljak
gdebeljak
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 106
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?
gsc_dba
gsc_dba
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1754 Visits: 2019
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.

_____________________________________________________________________________________
[font=Courier New]gsc_dba[/font]
gsc_dba
gsc_dba
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1754 Visits: 2019
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



_____________________________________________________________________________________
[font=Courier New]gsc_dba[/font]
gdebeljak
gdebeljak
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 106
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search