dynamic data source?

Question

dynamic data source?

Staggerlee

SSCertifiable

Points: 5272
More actions
November 8, 2011 at 7:32 am

#262513

Hi all,
Im new to ssrs and working with 2008 r2.
Is it possible to create a dynamic data source? I want to run the same query against different servers. Is it possible? Or do I have to manually put in every data source (server) separately and duplicate the query for each data source?
Thanks
S

Viewing 5 posts - 1 through 4 (of 4 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Yes, that is possible and actually quite simple. You just need to create a parameter for the servername which you when use in your connections string.

Here you will find a complete description on how to do it:

http://www.sqlservercentral.com/articles/Development/2945/

[font="Verdana"]Markus Bohse[/font]

Staggerlee SSCertifiable Points: 5272 More actions · Answer 2

Hi Markus,

I might be being simple..

but that isnt working for me. I try to deploy it to test and i get an error

The definition of the report '/MYReport' is invalid

If i put in a manual datasource it works again.

Ive put in:

="Data Source=Parameters!server.Value &"

;Initial Catalog=Parameters!db.Value

into the Data Sources. Which is identical to the pic unless ive mistyped and cant see it..

Any ideas

Staggerlee SSCertifiable Points: 5272 More actions · Answer 3

Just as an add on..

Id prefer if i could use a drop down list to pick the server i run the report on.

Is there a way to make the dynamicSource use a parameter taken from another list?

Staggerlee SSCertifiable Points: 5272 More actions · Answer 4

ok..

Got my datasource string wrong.

It is now:

="data source=" & Parameters!server.Value & ";initial catalog=" & Parameters!database.Value

(taken from the thread about the post) and its working.

I set the default db to master just to test.

I also made the server paremeter connect to a query which i just listed out all the servers i wanted it to connect to. and when i run it locally it now works! hooray!

But when i publish it and go to the web page i get :

The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified. (rsInvalidDataSourceCredentialSetting)