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
Many thanks for the workaround but in a scenario where we will be creating and updating a large number of reports for our customers, this whole process seems time consuming and open to human error. I really appreciate the efforts you have gone into though to find us a workaround.
Could you tell me if an easier method is going to be planned into future releases? Really need some intermediate class etc where we can just alter the connection string.
Really appreciate your help.
Yes, we will have a better solution like the one you are suggesting, but we won't have it until our next major release (12.1).
We can make it less prone to human error ;). What if instead of specifying the report file in the .tt we change it so it scans all the reports in the folder/subfolders and generates a single file with all the DataSource providers?
It's also possible to execute the .tt templates at build time (though a little tricky to make it work), and that will eliminate the chance of forgetting to run it.
Andres
That sounds like a great work around. Very much appreciated.
In terms of the full enhancement, when do you think 12.1 will be released or is there no definite date as yet?
We are currently evaluating your reporting for our product but are really looking at having our product released early next year.
Many thanks
The *.1 releases are usually around mid-Q2. Reporting ships with the rest of the NA suite products.
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
Thanks Steve. Let us know if you find any issue.
Merry Christmas to you too!
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
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.
I'll have it this week, probably in the next couple of days.