April 17, 2008 at 2:07 pm
I have a report which contains 4 datasets. 3 of them query information from one DB on my server and 1 queries information from a 2nd DB on the same server.
I have the report configured to use a datasource that connects to the server via an SQL logon / password.
I guess I'm not sure how to approach this or if the problem is even resolvable.
As mentioned above, the vast majority of the information comes from one DB but I am quering a table in the 2nd DB only to populate one of my report parameters (drop-down list), used to select specific information in my report.
I have 2 datasources setup on my reporting server, one for each DB and when creating reports that only pull information from one DB, using the associated datasource, I have no problems.
However, I'm not sure if I can, or if it's possible, to pull information from 2 DB's in to a single report and how the associated datasources must be configured.
Can you create a custom datasource which can access information in multiple catalogs?
I hope I'm explaining thsi correctly...
Thanks all!
Bob
April 18, 2008 at 7:16 am
I've realized my erroring ways and fixed my problem. I believe this is the right way to do things so if not, please let me know. Here is what I did to fix the problem.
1. I created a new login on my SQL server called rptrunner.
2. I then set thepermissions for rptrunner to have read / write permissions to both DB's in question
3. I then created a new shared datasource in SSRS with DB credentials using the new rptrunner SQL account
4. For each dataset in my report (some containing queries in one DB and others containing queries in the 2nd DB) I associated them with the new shared datasource.
In diong this, all works fine.
Seems so simple... after the fact. 🙂
April 21, 2008 at 1:45 pm
I had a similar situation recently. We use a shared datasource for each report project (containing multiple related reports), but sometimes data from another db on the same server is required.
We just reference the other database with dbname.dbo.object (as opposed to dbo.object) and it works just fine (keep in mind the login has permissions to objects on both databases).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply