Creating report model with multiple databases

Question

Creating report model with multiple databases

Canute Magalhaes

SSC Rookie

Points: 34
More actions
December 29, 2005 at 9:56 am

#166507

Hi All,
I have hit a road block when using reporting services SQL Server 2005. I created a report model using VS 2005. All is fine, if I have to use one database, However in our database design we have all person/user/etc demographics in one database and a database per application domain. I have not found a way to create a report model with more than one database. Help in any direction is appreciated.
Thanks
January 2, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Eric Brochu-281909 SSC Veteran Points: 240 More actions · Answer 1

Hi,

The data inside a dataset will always be coming from one and only one database if your dataset type is direct query (unless you have created views in your database that access external database).

I always use stored procedure in my dataset, this allows me to easily create dataset that access multiple db.

Select a.name,b.name

from db1.dbo.table1 a

join db2.dbo.table2 b on b.id = a.id

As a said in the beginning of this post, you could also create views in your database to access external db.

HTH,

Eric

Canute Magalhaes SSC Rookie Points: 34 More actions · Answer 2

Hi Eric,

Thanks for the reply, I have views that access the tables in other databases, but even though I have permission in the other databases, when creating the Data source view (in VS 2005 - report model) it would not allow me to choose the views, since a Data source view is tied to one and only one Data source.

Canute.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hi Canute,

Not sure if my test is replicating what you're trying to achieve but I had no issues getting the views into the Model. Basic test was (using my sql2k instance) create a view of northwind products table inthe pubs db. Create new model in VS2005, add pubs as the sole DS, create a DSV over pubs that included my new view, create the model using pubs tables and the view looking into northwind. Admittedly I haven't deployed it so it's not fully tested, but the initial modelling stage definitely let me use the view based on the other DB's table.

Steve.

Canute Magalhaes SSC Rookie Points: 34 More actions · Answer 4

Hi Steve,

I finally managed to get it working, This is how, I created two data sources, to cite your example, one for northwind and one for pubs,I then created only one data view based on the main database, for example on pubs, from then on everything was smooth, I am not sure whether 2005 model requires the two data source created first, after which it could handle the data views and the data model knowing how to map all the fields.

Thanks anyway.

Canute.