report on two database

hi all

i m facing a problem in reporting services. i have a global database, in which i m mainting user inormation like database name, server name and all credentails about user database. i want that when my report is open a user parameter will display, on the basis of that users id report will display data from his database. server name can be different, database name can be different but schema of database will be same for all user. so pls help me

The DataSource string of the dataset is dynmaic which means that you are able to use Expressions within the Data Source, using the syntax ="DataSource=" & Parameter!SomeParameter.Value

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks jens for response

but first time there should be fixed connection so that my user list will display. after selecting a particular customer my connection string will change. how this is possible because all dataset use same connection string. pls suggest me .

|||

Don't forget you can have and use multiple data sources. And these can either be shared or private to the report.

If your customer list and the actual data are coming from different data sources then you should make the dataset that returns the customer list for the parameter based on a static data source connection and the other dataset that brings back the actual report data based on a different data source connection.

If the the data source varies depending on which customer you choose then the second connection can be made expression based as Jens pointed out.

|||

ya adam

i was using same apprach, but when i m changing connection string of one dataset another dataset's connection string is automatically changes. can u show me that how can i change connection string of one dataset so that another dataset's connection string will not change.

|||

Don't use a single shared data source. If all datasets use the same shared data source then naturally having that expression based will affect all datasets that use it. Use multiple data sources, a different one for each dataset. Try not using a shared data source.

On the data tab, when you click the elipsis (...) button next to the datasets dropdown, you will see the properties of the dataset. In there you specify which data source it connects to. Don't choose the shared data source option. Instead define the data connection for that dataset.

If you're already doing this then maybe whats happening is that both data sources happen to have the same connection string and reporting services is trying to be clever and just treating it as one.

|||

thanku adam

actually i was sharing same data source. now i change it and its working properly

thank u again.