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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics