Dynamic datasource connection expressions

• hans.johnson

  Mr or Mrs. 500

  Points: 556

  More actions

  January 14, 2011 at 1:49 pm

  #230253

  We have five SSRS 2008 reports that we will need to have multiple databases using for various clients. Our backend db is MSSQL 2008 R2. Rather than duplicate the reports and have a single datasource for each client we want to have one set of reports and have a dynamic datasource that the front end application passes in the datasource connections using predefined parameters.

  I can get this to work successfully within my BIDS designer just fine. But, I cannot get it to work from my web Report Server. The expression I'm using is:

  ="Data Source=" & Parameters!dbserver.Value & ";Initial Catalog=" & Parameters!dbname.Value & ";User Id=" & Parameters!dbuser.Value & ";Password=" & Parameters!dbpassword.Value & ";"

  Does anyone know how to set up the web Report Server datasource properties so this will work from the report server? I've literally tried everything to get it working I could but no luck. If I use Windows Integrated Security I keep getting this error; Cannot create a connection to data source 'DynamicDataSource'. (rsErrorOpeningConnection) Format of the initialization string does not conform to specification starting at index 0.

  I've also tried using single ticks ' rather than double " quotes but no luck. I'd greatly appreciate any feedback that could possibly help.

  Thank you for looking