I need some help figuring out how to dynamically set a SSRS report data source connection when generating reports on my ASP.Net page. We have a lot of (100+) clients that will all use the same set of reports, except that each client has its own server/database. Therefore I need to be able to specify that specific server and database based off the ClientID.
Can I pass along the datasource connection parameters when requesting reports from SSRS? I would think I should be able to set the connection string much like you would for any SQL connection something similar to:
"server=" + myServer + ";database=" + mydbName + ";user id=" + myUsername + ";password=" + myPassword + ";Encrypt=YES;TrustServerCertificate=true";
I have not had any success with this so far. I may have the wrong syntax for the connection string. Is this possible? Please advise.