Dynamically set the data source for SSRS reports without unattended execution account

reportreporting-servicesSecurity

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

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?

Best Answer

Here is how I change a data source at runtime.

Create a report parameter DatabaseServer.
Create a report parameter DatebaseName.
Create a dsTest data source for the report (This is for testing locally).
Set the dsTest data source to point to your test database.
Create another "dsDynamic" data source and set its connection string expression to the following: ="Data Source="+Parameters!DatabaseServer.Value+";Initial Catalog=" + Parameters!DatabaseName.Value
Build and test the report by setting all datasets to the test connection created in step 3.
Set the report's datasets to the dynamic dataset created in step 5 prior to live deployment.
Send in the database name and server name to your reports from your application to point to another database instance.

Related Solutions

R – SSRS Dynamic Shared Data Source

This question is a little old, but if there is already a custom app accessing SSRS reports this Code Project might be a good solution.

Using a shared data source for dynamically generated and deployed reports

Answering my own question here, hoping someone else might find this useful:

I was under the (false) impression that the unique "DataSourceID" given to a data source on the server would be sufficient to identify it uniquely.

So in my generated RDL, I had something like :

  <DataSources>
    <DataSource Name="MyDataSource">
      <Transaction>true</Transaction>
      <DataSourceReference>MyDataSource</DataSourceReference>
      <rd:DataSourceID>6ba7c588-e270-4de9-988c-d2af024f10e1</rd:DataSourceID>
      <rd:SecurityType>None</rd:SecurityType>
    </DataSource>
  </DataSources>

Now this worked once, when my data source was indeed called "MyDataSource" and located in the same directory as my report which I published through the RS WebService API.

As soon as I moved the data source elsewhere, it stopped working.

THE SOLUTION:
This may sound silly, but I really didn't "get it" at first: the DataSourceReference needs to have the full and complete "path" on the Reporting Server to that data source I want to reference. Just specifying the unique ID won't do....

So once I changed my RDL to:

  <DataSources>
    <DataSource Name="MyDataSource">
      <Transaction>true</Transaction>
      <DataSourceReference>/MyProject/DataSources/MyDataSource</DataSourceReference>
      <rd:DataSourceID>6ba7c588-e270-4de9-988c-d2af024f10e1</rd:DataSourceID>
      <rd:SecurityType>None</rd:SecurityType>
    </DataSource>
  </DataSources>

(notice the <DataSourceReference>/MyProject/DataSources/MyDataSource</DataSourceReference>)

since that moment it works like a charm.

Hope someone might find this useful some day!

Best Answer

Related Solutions

R – SSRS Dynamic Shared Data Source

Using a shared data source for dynamically generated and deployed reports

Related Topic