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

Question

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?

Answer 1

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.