dynamic data source based off table

• n00bDBA

  SSCrazy

  Points: 2986

  More actions

  December 20, 2012 at 3:52 am

  #267925

  Hi,

  Id like to create a Data source that is dynamic and based on a table. (Ie user opens the report and has a drop down with a list of all the Servers. Clicks on the Server they want and a Second list is populated with a list of the databases on that server then click report an query is ran to populate a table with information)

  I believe all i have to do is write the Expression correctly but i cant seem to get it working for the dynamic data source.

  I created a static data source that gets the table and lists them out in a parameter.

  I then created another data source that will be populated with the parameter from the first.

  My Expression is:

  ="Data Source=" & Parameters!Server.Value & ";Initial Catalog=master"

  If i set the dynamic data source to a static one the rest of the report runs.

  Is this functionality not possible (Ie having a data source that is not static. ) i found a lot of examples of making dynamic database choice in the connection string but couldn't on the Server side..

  Hope someone can help

• n00bDBA

  SSCrazy

  Points: 2986

  More actions

  December 20, 2012 at 5:23 am

  #1570496

  Resolved it.

  Found this on SQLCentral. (didn't pop up in my google searchs 🙁 ) which is 80% of what i wanted. I redid my report again using this and it worked...

  http://www.sqlservercentral.com/blogs/sqldbauk/2011/07/22/ssrs_3A00_-dynamic-data-sources/