Dynamically set data source for SSRS reports without auto run account?

Question

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

Programmatically change server-side SSRS data source

SSRS Dynamic Shared Data Source

Other technologies, such as Crystal Reports, have allowed us to very easily change the data source at will for decades, so it seems strange to me that these hacks are still the best way to achieve this, but provided that it is: is it possible to do this without enabling an automatic run account?

Answer 1

This is how I change the data source at runtime.

• Create a DatabaseServer report parameter.
• Create a DatebaseName report parameter.
• Create a dsTest data source for the report (this is for local testing).
• Install the dsTest data source to point to the test database.

• Create another data source "dsDynamic" and set its connection string expression as follows: ="Data Source="+Parameters!DatabaseServer.Value+";Initial Catalog="&Parameters!DatabaseName.Value

• Create and test the report by installing all the datasets in the test connection created in step 3.

• Define the report datasets for the dynamic dataset that you created in step 5 before real-time deployment.
• Send the database name and server name to your reports from your application to point to a different instance of the database.