Post reply

Creating report model with multiple databases

  • 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

  • January 3, 2006 at 7:06 am

    #612382

    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

  • January 3, 2006 at 10:47 am

    #612445

    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.

  • January 29, 2006 at 9:12 am

    #617651

    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.

  • January 30, 2006 at 6:42 am

    #617696

    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.

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

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