December 17, 2008 at 10:13 pm
Hi Guys,
Did anyone worked before on Creating Dynamic Datasource in SSRS 2005....If so can you suggest me regarding this:
I have created two parameters 'server' and 'Database'
and I have given the below statement in the connection strings:
="Data Source=" & Parameters!server.Value & ";Initial Catalog=" & Parameters!Database.Value
When I am viewing the report, it asks me to enter the server and database names.....and when I enter, I am getting the respective correct output....
Now my question is - Lets say I am having around 5 servers and oneach server there are around 7 to 8 databases ...so total around 35 to 40 databases.....
So what I actually want is - When I select any server from the dropdown box of 'Server' (assume tht I have given 5 Non -Queried values to make it drop down).....I want to see the respective databases present on tht server in the 'Database' dropdown box.............
Any ideas of How to do?????
Thanks,,,,,,
December 20, 2008 at 8:34 pm
From my research so far (I'm very new to SSRS) I don't think this is possible using the Report Manager.
December 21, 2008 at 3:02 pm
grkanth81 (12/17/2008)
Hi Guys,Did anyone worked before on Creating Dynamic Datasource in SSRS 2005....If so can you suggest me regarding this:
I have created two parameters 'server' and 'Database'
and I have given the below statement in the connection strings:
="Data Source=" & Parameters!server.Value & ";Initial Catalog=" & Parameters!Database.Value
When I am viewing the report, it asks me to enter the server and database names.....and when I enter, I am getting the respective correct output....
Now my question is - Lets say I am having around 5 servers and oneach server there are around 7 to 8 databases ...so total around 35 to 40 databases.....
So what I actually want is - When I select any server from the dropdown box of 'Server' (assume tht I have given 5 Non -Queried values to make it drop down).....I want to see the respective databases present on tht server in the 'Database' dropdown box.............
Any ideas of How to do?????
Thanks,,,,,,
If you can hardcode the servers in a dropdown they you can use one of the following queries to populate the database dropdown. I would create a datasource with a connection string based on the server selected from the dropdown and set the database to master. The first query will get you all of the non system DB's. It is pretty simple. The second can select only the DB's that you want based on some query. This will allow you to have them only select from a list of valid db's. Just change the 'Select 1' From the Exists clause to be a query that will identify the database as a db that is valid to run this report against. After these two dropdowns are selected you should be able to create a report datasource that uses both the selected server and the selected DB.
----
use [master]
Select name From sys.databases Where name not in ('master','tempdb', 'model', 'msdb')
----
use [master]
Declare dbs cursor for Select name From sys.databases Where name not in ('master','tempdb', 'model', 'msdb')
Create table #Table(dbname varchar(100))
Declare @dbname varchar(100)
Declare @sql varchar(max)
Open dbs
Fetch Next From dbs Into @dbname
While @@FETCH_STATUS = 0
Begin
Set @sql = 'use [' + @dbname + '] If Exists(Select 1) Begin Insert Into #Table Select ''' + @dbname + ''' As dbname End'
Exec (@sql)
Fetch Next From dbs Into @dbname
End
Close dbs
Deallocate dbs
Select * From #Table
Drop Table #Table
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply