1

I have 2 databases both having same table structures but with different data. I want to display a report by giving a filter to choose the database name. I tried using connection string as expression in Embedded connection still it's not working.

akshay
  • 777
  • 4
  • 15
  • Are databases on the same server? – Anton Jun 14 '17 at 05:07
  • Post your expression for embedded connection – Anton Jun 14 '17 at 05:16
  • Yes databases are on same server. – akshay Jun 14 '17 at 05:50
  • @Anton expression is ="Data Source=servername;Initial Catalog=" + Parameters!dbname.Value – akshay Jun 14 '17 at 05:51
  • what is the error? have you tried SQL Profiler? – Anton Jun 14 '17 at 05:56
  • No. I haven't tried that. – akshay Jun 14 '17 at 06:11
  • @Anton when I try to use data source(where i have put the expression) for a dataset there I'm getting this following error - Connection string expressions cannot be evaluated at design time. You must preview or deploy the report to verify the connection. – akshay Jun 14 '17 at 06:20
  • You need to use a hardcoded datasource during design time, and then change it to expression just before deployment. – Anton Jun 14 '17 at 06:39
  • @Anton How do I change the expression just before deployment? – akshay Jun 14 '17 at 07:03
  • ok,let's start again. What is 'it's not working'? You can't save what you entered into expression, or you can't continue designing it? or it does not work in Production? – Anton Jun 14 '17 at 07:07
  • it's hard to troubleshoot it when you don't see it. Read these links, it may help: https://www.mssqltips.com/sqlservertip/4302/implement-dynamic-data-sources-in-sql-server-reporting-services/ http://www.keepitsimpleandfast.com/2012/08/how-to-use-dynamic-data-sources-in-your.html I'll have to go soon... – Anton Jun 14 '17 at 07:08

1 Answers1

0

Yes you can do this but this has a limitation: the datasource cannot be shared. So the sequence is following:

  • create a report using embedded datasource and "fixed" connection string like Data Source=server;Initial Catalog=db1, using a wizard or how you want, make sure it can be refreshed

  • create a parameter dbname

  • substitute the expression in datasource to by dynamic: ="Data Source=server;Initial Catalog=" &Parameters!dbname.Value
sepupic
  • 8,409
  • 1
  • 9
  • 20
  • I've tried it and it worked perfect. Did you use & this time? Does the parameter exist? – sepupic Jun 14 '17 at 07:06
  • Yes parameter exists and I used &. Still getting the same error. – akshay Jun 14 '17 at 07:11
  • @user7814194 You are getting this error not while creating the datasource but when trying to create a field list for dataset, right? I've just got the same error, so even if we can create such a datasource it's useless... – sepupic Jun 14 '17 at 07:30
  • Yes exactly. I want to use this datasource for another dataset. And I can't hard code connection string because I want to use parameter in report to select between number of databases and display data from that particular database. – akshay Jun 14 '17 at 07:41
  • The point was to create a dataset with all it fields using "fixed" datasource, not dynamic. The dynamic datasource definition was rigth but it is not permitting to evaluate the fields, so it should be done using non dynamic database name, and only then you can substitute your datasource definition with dynamic database name – sepupic Jun 14 '17 at 07:45