Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to reference two data sources in SQL Reporting Servcies

Posted on 2009-07-09
4
Medium Priority
?
208 Views
Last Modified: 2012-05-07
Hi there,

I want to create a report in SQL Reporting where my query references two shared datasources in the report.

I have created the shared data sources and individually they are working fine. lets call them Data1 and Data2.

 What I want to do is something like this

Select CompanyName (Data1), Address(Data1), ContractType(Data2)

where companyID(Data1) = companyID(Data2)

the reason I need this is that some info is in data2 that is not in data1 but I need to create a report that lists it all in one table.

I am not sure if I create two shared DB Datasources if I can reference them from within the same table or do i have to create a linkserver connection from within my query.

If anybody has sample code too that would be really helpful.

Also does anybody know of any good resources for advanced SQL Reporting.

Cheers

Michael
0
Comment
Question by:mickinoz2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24813498
If CompanyID is unique, Create a stored procedure in SQL that returns all data you need, then with your two datasets, have one which returns all customer names and ID's and use that as a parameter to pass into your 2nd dataset which executes the stored proc, but passes in the CustomerID.

HTH
0
 
LVL 8

Expert Comment

by:Hadush
ID: 24814175
One dataset can only refere one database at a time. I think the only way if you want to join data from the datasources you can write in the query  and give it full name when you call the tables. If they are both in one server you can use something like the following code. If they are different servers, you need to use like open query.
 

--You can point to any of the shared databases and will figure out by itself the second database.
Select 
t1.CompanyName 
, t1.Address
, t2.ContractType
FROM Database1.dbo.Table1 t1  
Left join Database2.dbo.Table2 t2  --which ever join works for you
ON t1.CompanyID=t2.CompanyID

Open in new window

0
 
LVL 5

Author Comment

by:mickinoz2005
ID: 24814316
Hiya,

Sorry a little confused on response -

The two DB are on different servers. So if i create two shared DB sources i cannot use them in the query.

So lets say the query I want is something like this

Select D1.CompanyID, D2.Groupname
From data1.company as D1,data2.compgroup as D2
Where data1.company.companyID = data2.compgroup.companyID

How would I write this in the query using the correct syntax to get it to work..

Cheers for your help

Michael


0
 
LVL 5

Accepted Solution

by:
mickinoz2005 earned 0 total points
ID: 24905282
I resolved this myself.

We just created a linked server in our SQL and then just referenced the linked server from within our query.

Thanks

Michael
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question