Lookup from 2 Data Sources

[abcfantasy]

Hi,

I'm trying to use the lookup function from 2 datasets that get data from 2 different data sources. The result of the lookup function is just blank, no error or any value.

It only worked for me when using it on 2 datasets from the same data source. Is it restricted this way?

Below is simplified information on my implemention:

* DataSet1 contains fields Country, using DataSource1.
* DataSet2 contains fields Country and CountryCode, using DataSource2.
* A table is bounded to DataSet1, showing just Country. In another column, the cell has the expression:
=Lookup(Fields!Country.Value,Fields!Country.Value,Fields!CountryCode.Value, "DataSet2")

I don't have any parameters, filters, calculated fields or dynamic connection strings. And I am using the Report Designer and SQL Server 2008 R2.

Any help would be much appreciated.
Andrew.

Andrew
Research Assistant

 

[ousoonerjoe]

What about creating a VIEW or Stored Procedure with a UNION of the 2 different data sets? Then you can simply pull from a single source. If the source ever needs to be adjusted, you can change it on the fly and not have to adjust the report directly and redeploy.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[abcfantasy]

That is a possibility, true. Thanks for the response.

But I wonder why my scenario would not work. Isn't the lookup function intended to work on datasets with different data sources? Why wouldn't it work that way?

Andrew
Junior Analyst