9

I need to dynamically specify the data source for SSRS reports at runtime. I found these solutions:

Change SSRS data source of report programmatically in server side

SSRS Dynamic Shared Data Source

Other technologies like Crystal Reports have allowed us to very easily change the data source at will for decades so it seems odd to me that these hacks are still the best way to accomplish this but assuming that is the case: is it possible to do this without enabling the unattended execution account?

Community
  • 1
  • 1
BVernon
  • 3,205
  • 5
  • 28
  • 64
  • SSRS development oftentimes comes down to using a series of ugly hacks to trick it into producing what you want. Sometimes though, taking a step back and looking at the overall requirements can reveal other options. What's the reason you need to run for different datasources? Is there a finite number of them? Might it be easier to just deploy multiple copies of the same report, each using a different datasource? – kyzen Jun 05 '14 at 17:49
  • 1
    @kyzen Yeah, I think in the beginning that's what we're going to do (deploy copies with different data sources, that is). There are two reasons why I'd want to change the data source. The 1st is for pointing reports to the development server versus production and the other is for customers who have their data on separate databases. – BVernon Jun 05 '14 at 17:54
  • 1
    You can make it a *little* bit easier on yourself by either writing a completely custom powershell deployment script, or by using multiple deployment configurations in Visual Studio. Visual Studio would be less up-front work, but will be somewhat rigid in terms of deployment structure. With Powershell, you could store configuration data externally (XML, table, etc), which could make managing complex deployment rules much easier in the long run. – kyzen Jun 05 '14 at 18:20

1 Answers1

13

Here is how I change a data source at runtime.

  1. Create a report parameter DatabaseServer.
  2. Create a report parameter DatebaseName.
  3. Create a dsTest data source for the report (This is for testing locally).
  4. Set the dsTest data source to point to your test database.

  5. Create another "dsDynamic" data source and set its connection string expression to the following: ="Data Source="+Parameters!DatabaseServer.Value+";Initial Catalog=" + Parameters!DatabaseName.Value

  6. Build and test the report by setting all datasets to the test connection created in step 3.

  7. Set the report's datasets to the dynamic dataset created in step 5 prior to live deployment.
  8. Send in the database name and server name to your reports from your application to point to another database instance.
Dale K
  • 25,246
  • 15
  • 42
  • 71
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • 1
    Don't even remember what I ended up doing, but marking this the answer since it's the only one and I presume it works for you :) – BVernon Nov 12 '14 at 22:37
  • I've marked this as well as I used it with success. Thank you @lrb. – callisto Jun 03 '16 at 11:28
  • What does the "&" do in step 5? – mcolegro Aug 23 '17 at 14:53
  • 1
    I guess I was being lazy. SSRS accepts both + and & for string concatenation. – Ross Bush Aug 23 '17 at 15:06
  • 1
    found the article : http://www.keepitsimpleandfast.com/2012/08/how-to-use-dynamic-data-sources-in-your.html – dev Nov 07 '17 at 23:38
  • so we cannot test dynamic data source on local before deploying? – HaBo Apr 13 '20 at 10:59
  • You can't run the query by invoking the Query designer in design mode, however, you can set default values for the endpoint parameters and they can be used when running in development mode and later overidden. – Ross Bush Apr 13 '20 at 14:11