Dynamic Datasource........

  • 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,,,,,,

  • From my research so far (I'm very new to SSRS) I don't think this is possible using the Report Manager.

  • 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