SSRS - dynamic DataSource problem

• StillaLearner

  SSCrazy

  Points: 2636

  More actions

  June 13, 2018 at 4:32 pm

  #351848

  Hi All
  I'm having problems getting a dynamic datasource configured correctly to use two parameters with the second parameter depended on the result of the first one.
  

  Above is what my dialogue box looks like for the dynamic datasource.
  Select ServerName is a drop down with a list of server names and depending on the select the ConnectionString will contain one or more options in terms of the sqlServerInstance. port. some nodes have more than one(default) instance of SQL.

  Some of the SQL instances in PROD have non default ports. The data comes from a table that contains the servername and connectionstring - which looks like - either nodename or nodename\sqlinstance,55431 or nodename,55431
  I'm not getting this to work correctly. I have followed this article - https://www.mssqltips.com/sqlservertip/4302/implement-dynamic-data-sources-in-sql-server-reporting-services/    and I can get this to work with one parameter

  Query for the ServerName parameter is select distinct ServerName from [dbo].[ConnectionString_dataLoad]
  Query for the ConnectionString parameter is select ServerName,  ConnectionString from [dbo].[ConnectionString_dataLoad] where servername = @servername

  I can get this to work OK but when I select the servername and connectionstring from the drop down I get an error saying the connection string is invalid
  The rest of the report uses a datasource that looks like this

  

  Can anyone tell me where I am going wrong please

  thanks

• StillaLearner

  SSCrazy

  Points: 2636

  More actions

  June 13, 2018 at 4:58 pm

  #1993923

  ok - this is sorted
  the connectionstring parameter had the "allow multiple Values" box ticked
  I unticked this and it is now working

  this linked sorted it our for me - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca630fce-7796-43d3-b4ab-37066926733a/error-during-processing-of-the-connectionstring-expression-of-datasource-ssrs-2005-?forum=sqlreportingservices

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  June 13, 2018 at 5:46 pm

  #1993927

  StillaLearner - Wednesday, June 13, 2018 4:58 PM

  ok - this is sorted
  the connectionstring parameter had the "allow multiple Values" box ticked
  I unticked this and it is now working

  this linked sorted it our for me - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ca630fce-7796-43d3-b4ab-37066926733a/error-during-processing-of-the-connectionstring-expression-of-datasource-ssrs-2005-?forum=sqlreportingservices

  Thanks for posting back! That will help as someone else is likely to stumble upon the same issue and find your answer.

  Sue