October 29, 2008 at 2:15 pm
I've searched high and low over the internet and in books to see if this can be done. In our SQL 2005 Standard setup - we have 3 main databases, but I'm new to Reporting Services. I have everything set up correctly to my knowledge (report builder, manager, etc). How - if it's even possible - can I build a Report Model using tables from more than one data source (i.e. table1 from datasource1 and table2 from datasource2)? Basically, I want to achieve the end result of a report pulling data from 2 different databases. Thanks in advance!
October 30, 2008 at 12:53 pm
Okay, I kinda' found the answer to my own problem - both by more searching and by accident. I'll post what I had to do for those who may be looking for the solution still, 'cuz I spent 3 days looking for this. I had to use a named query. In Business Intelligence Design Studio I built a report model project and added 2 data sources. What I ended up having to do (in short) is pull my table from one of the data sources and in the data source view (right click on the .dsv and choose 'open'), in the left hand pane I right clicked and chose "New Named Query". From there it was just like a regular SQL query in extended/advanced view. The trick is (listen closely), I first chose my other data source (NOT the one that says "primary") and pick the table from the other data source that you want to include. THEN - in the SQL syntax part, change the "FROM [tablename] to [database].[schema].[tablename]. THEN - change the data source drop down from your other data source to the (primary) data source. From there, click 'OK' or whatever and do your joins and do an "Autogenerate" on the Report Model. Voila!
January 6, 2011 at 7:37 am
Was interested to know more, did you get a chance to post more information anywhere?
Some links may be.
- Thanks
gkd
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply