Two datasources in a single query
Hi,
I have a requirement to develop a report which has multiple datasources. The main query should be a combination of
datasources from Oracle and SQL Server.
I am trying in this way (this way is working for me when I have both the table in one datasource:
="select * from ORAtb where "+Parameters!P1.Value(1)+" IN(select "+Parameters!P2.Value(1)+ "from SQLtb)"
Table ORAtb is from Oracle and Table SQLtb from SQL Server.
Please suggest what needs to be done.
Thanks,
Sandy
April 6th, 2011 8:02pm
One way you could do this would be to set up a linked server to Oracle in SQL Server.Or you could use OPENQUERY.
This is because a datasource points at a single location.
This is more of a T-SQL question. Another option you have is to import the data from Oracle into the same database, via SSIS or another method. Similarly, you could import the data into Oracle and query that.
Free Windows Admin Tool Kit Click here and download it now
April 6th, 2011 8:07pm
Take a look at this - http://kennyshu.blogspot.com/2010/04/ssrs-2005-one-table-uses-two-datasets.html
I haven't tried that myself so can't say for sure if it would work.
April 6th, 2011 8:16pm
Please try the below. The steps are provided on the assumption you have a table in SQL database named
SqlTable and field you need to pick is Field1. Please modify the names as appropriate.
Add a dataset named
datSQL that retrieve the column from the SQL Table. eg : Select Field1 from SqlTable
Add a parameter named
SqlFields. Check 'Allow multiple Values'. Set its Available values to be from
datSQL dataset and set the lable and value field as Field1. Similary set its default values to be from datSQL and value field to be from Field.
Now add another dataset that does your main querying from oracle database. Set its query expression as
="SELECT * FROM ORATAB WHERE ORAFIELD IN ('“
+ Join(Parameters!SqlFields.Value,"','") +
"'))"
Please check and let us know the result. Please feel free to discuss if you have nay issues. Hope
this helps.Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
BH
Free Windows Admin Tool Kit Click here and download it now
April 6th, 2011 11:57pm
Hi Bilal Hani,
What if ORAFIELD is also coming from a parameter? ie., Select * from ORATAB where ( parameters!ORAFIELD.VALUE) IN
('“
+ Join(Parameters!SqlFields.Value,"','") +
"'))" and both ORAFIELD and SQL Fields are multi value parameters?
"SELECT * FROM ORATAB WHERE
ORAFIELD IN ('“
+ Join(Parameters!SqlFields.Value,"','") +
"'))"
Please suggest..
April 9th, 2011 6:14pm
Since the ORAFIELD parameter is coming next to the WHERE clause, the parameter value appears like one of the field names in the ORATAB. Please correct if my
understanding is wrong. If so, can this be a multi value parameter? The evaluated value ends up like
SELECT * FROM ORATAB WHERE FIELD1,FIELD2,FIELD3 IN
('SQLVAL1','SQLVAL2','SQLVAL3')
Above syntax is invalid. After the key word WHERE, only one field has to come before the IN key word. As
such, I guess you should not allow multiple values for ORAFIELD parameter; so the evaluated value appear like
SELECT * FROM ORATAB WHERE FIELD1 IN
('SQLVAL1','SQLVAL2','SQLVAL3')
Please check and let us know your findings.
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
BH
Free Windows Admin Tool Kit Click here and download it now
April 12th, 2011 8:29am