May 15, 2009 at 12:59 pm
My question is this:
I have two databases, which hold very similar data. I also have many reports that are filled by data from a database. If the reports are to be held constant in format across two databases, is there a way that a report created in Microsoft Business Intelligence Studio can be used with any database file? I would rather not have to make the report file over again with the only difference being the Data Source.
Thanks!
May 15, 2009 at 1:46 pm
The short answer is no, you can't really change the datasource at run time...
you can sort of fake it though...
You could call a stored procedure to which you pass the database parameter. That could then query the database of your choosing, but you'd have to use Dynamic SQL and depending on the complexity of your queries and the differences in your databases you could run into problems with the database reusing plans that could end up hurting performance. Might have to force a recompile with each request. But it could be done.
Of course the other option is to have 2 datasets per report and run the same query on each db. Create a report object for each datasource and show/hide the appropriate output based on a parameter selection. Again this all depends on how intensive your user load and queries are. You obviously wouldn't want to do this with a report that runs a query on each sever that takes 10 seconds to execute...
It'll be interesting to see what some of the other folks here come up with.
-Luke.
May 15, 2009 at 2:17 pm
Luke L (5/15/2009)
The short answer is no, you can't really change the datasource at run time...
Huh - take a look at this article: http://www.sqlservercentral.com/articles/Development/2945/
Also, a simple google search on the terms: ssrs dynamic data source turns up quite a few hits.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2009 at 8:03 am
I stand corrected, apparently it can be done, but according to the discussion from that article there are a number of caveats. One of the more important is the inability to use shared datasources.
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply