3 Replies Latest reply on Jan 23, 2014 9:25 AM by Matt Lutton

    dynamic parameter driven database connection

    Carlo Bertolino

      I have multiple MS SQL servers with multiple databases on each server that are broken into regions. Each database on each server and across the environment are the same but with very different data content with each of the databases. I would like to create a dynamic connection string at the beginning of my report that requests two parameters to be filled out, franchise and region and from there, it would go to a source database and find the server name where those parameters are true.

       

      I would then like to have those parameters to be put into a connection string similar to this:
      ="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Region.Value

       

      With this, my users would be able to jump from region to region without jumping from report to report and I would not have to write one report for each database on each server. (Not an Option) Also, not an option, writing one big sql query with a "linked servers" all database together and filter by region.

       

      Is this doable?