We are building an application where the same set of reports hosted on the same report server will need to be able to report data from different database instances based on the user running the reports.
Has anyone ever tackled this problem, and if so what suggestions do you have for handling this
I am guessing we could pass in the database instance to be used as a report parameter, but my guess is that we could then not use data driven parameters in the report because there would be no way to point the data source for the parameter queries to the correct database instance (the one passed via the report parameter for the database instance).
-- Chris
Switching reports between database instances at run time
visiSteve
Robert,
Thank you for the tips, that sounds like just what we need. I will try out your suggestions and see how it works out.
-- Chris
sarahLiang
RS 2005 support expression-based connection strings for embedded data sources (i.e. non-shared data sources) - see this related thread for more details and other options: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=16395&SiteID=1
You could still make some of your parameter being driven from a dynamic database instance by setting up hierarchical parameters, and the first parameter (Parameter1 based on DataSource1) would determine the target database instance. All subsequent parameters could then use a DataSource2 with a dynamic connection string which references the value of Parameter1.
-- Robert
JOhnDSm
The suggestion Robert made above definitely did the trick.
I have a hidden parameter in the report which I populate with the user name of the logged in user. I used the syntax =Mid(User.UserID, InStr(User.UserID, "\") + 1) to strip off the Active Directory domain if present.
Then I have a parameter dependent on the one above which runs a query against a "master" database to populate itself with the list of other databases the user is permitted to run reports against (the user name is used in the Where clause).
Then my "real" report parameters are populated from the selected database by using a Data source which is set using an expression that points to the selected database.
My Data source expression is something like:
=Parameters!SelectedDatabase.Value
and returns the connection string.
The main DataSet for the report uses the same Data source expression to connect to the selected database and the values that were set via the "real" report parameters to filter the data.
-- Chris G.