Data Source/Instance Parameter - Is this possible?

Question

Data Source/Instance Parameter - Is this possible?

Matthew Brown-402268

SSC Eights!

Points: 927
More actions
May 1, 2007 at 8:19 am

#178058

I have 5 reports that run off 3 identical databases. Is it possible to create a Report Parameter that allows the user to choose the data source?
How would you do this? Multiple shared data sources, a view that selects all rows from all three db's and then limits to the required database?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

moe_mayne SSCommitted Points: 1933 More actions · Answer 1

I dont think you can do that... you must specify a specific data source when you create the report... im not sure.. has anyone else accomplished this...

Moe C

Matthew Brown-402268 SSC Eights! Points: 927 More actions · Answer 2

I haven't tried to do any more with this, but would be interested to see if anybody else gets anywhere.

Thanks.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I wish I could be of more help, than this, but, I do recall reading an article where someone did what you are trying to accomplish, so it can be done. I would guess I saw the article in the SSC Daily Newsletter within the last month.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matthew Brown-402268 SSC Eights! Points: 927 More actions · Answer 4

I'm behind on my subscription....

Thanks for the pointer.

David McFarland SSChampion Points: 11815 More actions · Answer 5

We create the datasource in the code that wraps around our reports, and it gets its connection string from a web.config file, thus allowing us to simply change web.config settings depending on the server we want to access. As long as the new RS datasource has the same name as the one the report requires, it seems to work fine. On a side note, if you do this, you do have to have a datasource of the same name in place when you upload the report to the new server. If a report is uploaded without a datasource, it won't work, even if you add a datasource right away after that, so you'll end up having to manually bind the datasource to the report. Much easier to just have one in place, and then replace it programatically.

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 6

Here's how I would do it:

1.) Create an sp that returns the data on a server that is setup w/ linked servers to all 3 separate instances. (Hopefully this is allowed in your environment)

2.) In the SP accept the @Instance parameter

- Now you have two options -

Opt1.) Use dynamic sql to run the query against the instance selected

Opt2.) Use some nested IF statements to run a normal query for the instance selected

Depending on the data being returned I would probably use Opt2 because dynamic sql is really slow sometimes. So something like this...

/***

create spReport

@Instnace varchar(256)

@AddlParms...

as

if @Instance = 'SQL1'

begin

select ... from SQL1...

end

if @Instance = 'SQL2'

begin

select ... from SQL2...

end

***/

-Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Martin N Ten Centuries Points: 1216 More actions · Answer 7

try this article

http://www.sqlservercentral.com/columnists/bKhawaja/2945.asp

only works with SS2005 though

HTH

Kindest Regards,

Martin