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
Hello Andres
Thanks again for you help.
Just checking in with you as when do you think you will have the workaround for me. Just need to schedule in my time for implementing.
Regards
I'll have it this week, probably in the next couple of days.
Andres
There's a new version here. It will create a single source file with DataSourceProviders for every report in the project.
Here there is a question/answer on how to run T4 as part of the build.
Let me know if this helps.
Many thanks Andres
Will give it a go. Thank you for your help. It has been very much appreciated.
Hope you have a good Christmas
Thanks Steve. Let us know if you find any issue.
Merry Christmas to you too!