We have a system where a user will log in through a client and depending on their login, the service will provide data from specific databases. For general data this is handled by our service pointing the queries to the correct database instance. With the reports the connection is within the report or stored in the app.config. We have to use server side rendering so I really need some way of altering the connectionstring in the report before its executed.
Any help will be appreciated
Hi Steve,
As you said, one way of doing that would be by programmatically altering the connection string for each user who logs in (user instance), in some kind of Session_Start() method that you may have in your app, here is a useful thread on how to do this.
However, I wanted to let you know that in NA Reporting 2011.2 you can change report's data sources at runtime using Data Source Providers. This allows you to customize (through code) how data is loaded. In your case, you can switch connection strings or queries according to the current user's permissions or even modify/filter data before being rendered in the report.
Here you have a running sample which uses a Data Source Provider to retrieve data from an ADO.NET Entity Framework Model. And here is a topic on how to use Data Source Providers.
Hopefully one of these alternatives will work for you.
Regards,
Miguel
Hi Miguel
Many thanks for the detailed reply. I have had a look at these options but really our best solution would be to write the reports with a SQL DataSource but have some way at runtime to change the connection string. The report would be the same regardless of the database but only the connection string needs to be changed.
I tried the IReportDataSourceProvider and this would require us to create many GetDataSource responses for every data table or result set.
Is there a way that I can just change the connection string?
Sorry to be a pain...
Thanks for your help
Steve