1

I have a remote SSRS 2016 server with some reports uploaded. These reports are to be shared by the Dev, QA and Production environments.

The way I do this is by having a DataSource with a dynamic connection string using an expression:

="Data Source="& Parameters!DatabaseServer.Value & 
";Initial Catalog="& Parameters!DatabaseName.Value

The db name and sql server address are passed in a parameters from an MVC project view.

ReportParameter p1 = new ReportParameter("UserId", CurrentUserId, false);
ReportParameter p2 = new ReportParameter("DatabaseServer", ReportDbServer, false);
ReportParameter p3 = new ReportParameter("DatabaseName", ReportDb, false);
var params = new ReportParameter[] { p1, p2, p3 };

reportViewer.ServerReport.SetParameters(param);

This exception is thrown on the SetParameters call:

ReportServerException: Error during processing of the ConnectString expression of data source ‘dsDynamic’. (rsDataSourceConnectStringProcessingError)

2 additional points:

  1. In another older project, this dynamic Datasource approach works, albeit using MVCReportViewer (https://www.nuget.org/packages/MvcReportViewer/)
  2. In this project, the report renders if I change the expression-based connection string to the vanilla "Data Source=127.0.0.1;Initial Catalog=AdventureDB" type.

Has anyone here ever used this dynamic conn string approach with success using ReportViewer?

callisto
  • 4,921
  • 11
  • 51
  • 92
  • 1
    Your dev, qa, and prod environments all share one collection of reports? And your web apps are required to pass in their own data connection info? I've not heard of setting things up that way before. Sounds like a maintenance nightmare. Do you have flexibility in this environment to set up Dev, QA, and Prod root folders in SSRS, each with their own set of environment-specific data sources? This would be the much preferred way to approach things, for several reasons. – DBro Jun 14 '19 at 00:49
  • Valid point, thanks. In this case though I've used this approach with another system I developed about 4 years ago: It is less of a nightmare than managing multiple versions of the same report in SSRS. It was like that before, but I prefer DRY. – callisto Jun 14 '19 at 06:32
  • 1
    How do you develop, test, and promote your reports up through the tiers? Essentially, your Production environment is also your Dev and QA environment, which can backfire on you if something goes wrong... sometimes pretty expensively depending upon the usage of your report(s). Regarding your error above, I'm just guessing, but if you remove the line break from the connection string expression, does that resolve the issue? `="Data Source=" & Parameters!DatabaseServer.Value & ";Initial Catalog=" & Parameters!DatabaseName.Value` – DBro Jun 14 '19 at 11:15

1 Answers1

1

I found your question while trying to implement the same pattern. Using Report Builder I was having trouble creating a connection string from parameters. I finally gave up on trying to interpolate the connection string from multiple parts. Instead I got it working by simply passing in the entire connection string, and then simply using that parameter to populate the ConnectString of the embedded data source.

Set expression for: ConnectionProperties.ConnectString as follows...

=Parameters!DB_Connection_String.Value

Also, because some of my other parameters are dropdown lists that depend on the data source to get populated, I had to make the @DB_Connection_String parameter the very first one. I moved it up to the top of the parameter list by selecting it and then pressing ctrl + up-arrow.

CowboyBebop
  • 701
  • 7
  • 10