I have a web service data source that I need to merge with a primary SQL data source to form into one report. The webservice obtains two parameters (AccountID and BalanceDate) and returns several calculated balances. I can get this to run when there is this single data source. However, I need to tie the AccountId to the ClientID in a different data source so I can match account and client info. Is there a way to tie the two data sources together into one list or table? Any information is appreciated. Thanks. James.

This might help you http://technet.microsoft.com/en-us/library/aa964129(SQL.90).aspx http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8da56859-4e84-46ad-a76e-32c31a1ad883 Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

Hi James, If I understand correctly, you have a report, which has two or many datasets. One of the dataset come from Web Service, and the others come from other data sources. Now, you want to merge these datasets. If I am not correct, please don't hesitate to let me know. In SQL Server Reporting Services(SSRS) 2008 R2, there are a few new functions, which are used to merge data from two datasets. They are known as Lookup, Lookupset and Multilookup. With these functions, we can data from multiple datasets in a tablix control. For more information about these functions, please see "Looup Functions" in the following article: http://msdn.microsoft.com/en-us/library/ms170438.aspx#Aggregates If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT

Hi, Thanks for your reply. Yes, I need to merge two datasets one from SQL and the other from a WebService call. Dataset1 contains AccountId, LoanId, ClientId, etc. Dataset2 is the Webservice call that passes Accountid/Balancedate and returns calculated account balances for that date (past or future). We can get this to run as a stand-alone report. The Tablix I have set up in the report contains all the related account, loan and client info required, but I need to list the balance as of the parm date. This example is given>>? =Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product") So, I attempted >> =(Lookup(Fields!AccountId.Value, Fields!Accountid.Value, Fields!Currentbalance.Value, "DataSet2") The error states : The Value expression for the text box ‘Textbox7’ refers to the field ‘Currentbalance’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. Thanks again for all your help, James

Hi James, The expression you used looks fine. The first parameter AccountId should be in DataSet1, the second parameter "Accountid" and the third parameter "Currentbalance" must be in the DataSet2. Please note, the field is case-sensitive. Based on the error message, please make sure the field is exact correct. Thanks, Jin ChenJin Chen - MSFT

JC, I have been able to get the report to run without error using the lookup function. Thank you for your help. Unfortunately, the webservice does not return anything but "#ERROR" when I do run it in conjunction with the other datasource. I think this has to do with the IDs not matching in the webservice query. Thanks, James