June 8, 2008 at 10:58 am
Hi All,
I wanted to know if it is possible to join tables from two databases and they come from two servers in SQL 2005 SSRS (They have a common field).
We cant Use Linked servers and Remote Queries for this.
Can we create two datasets in SSRS and link them in a single table
eg server1. Database-A =>
Table - InfoA(id,name)
server2.Database-B =>
Table - InfoB(id,CreationDate)
So can I select to have CreationDate and Name in one table.
please let me know if we can show the data from two databases in a single SSRS table.
TIA,
Raj
June 9, 2008 at 3:43 am
There isn't a straightforward way of doing it, but there were some thoughts here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=218824&SiteID=1
Let us know what you come up with.
June 10, 2008 at 8:19 am
I need to do something similar, and that post was not helpful to me. I have two daatasets. One queries a table from SQL Server, the other from an Oracle box. I need to join the two together and cannot figure a way to do it.
In my case, I don't see how a stored proc will work. Do I store the proc in the SQL database that is going to hit the Oracle database?
The obvious way to handle it would be report/subreport. But in my case, the result set from the report returns 5000 rows, for example. But only 10% will key match with the data in my Oracle table. This leads to my subreport embedded in my table to return mostly blank lines. I cannot figure out how to suppress them in the main report with the Visibility option because there is no field I can use to say "If this is blank, hide it"
If you find a solution to this issue, please post it.
June 11, 2008 at 11:27 am
As far as I know, there is no way to merge or otherwise extract data from within dataset(s) in SSRS.
The best option would be to add a linked server from your sql server to your oracle server. Then created a sproc in our sql server that merges your sql and oracle data the way you want. Then create a report using the sproc in sql.
If you can't (or can't have somone else) add a linked server to your sql server, I'm not sure what your options are.
To answer your question about hide if blank - you can use something like the following in the visibility - hidden expression:
IIF(fields!myfield.value = nothing, true, false)
Hope this helps.
-Megan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply