Specifications

Expression Language Improvements CHAPTER 9 167
=Lookup(Fields!StateProvinceCode.Value, Fields!StProv.Value,
Fields!StProvName.Value, "Dataset1")
The MultiLookup function also requires a one-to-one relationship between the source and
destination, but it accepts a set of source values as input. Reporting Services matches each
source value to a destination value one by one, and then returns the matching values as an
array. You can then use an expression to transform the array into a comma-separated list, as
shown in Figure 9-2.
StProvName
British Columbia
Oregon
Washington
StProv
BC
OR
WA
Dataset1
Florida
Georgia
FL
GA
BC
OR
WA
FL
GA
Salesperson
StateProvinceCode
Dataset2
SalesAmount
Month-to-Date Sales by Salesperson
David Campbell BC, OR, WA 2975
Tsvi Reiter FL, GA 3000
Saleperson
David Campbell
Tsvi Reiter
Territory
British Columbia, Oregon, Washington
Florida, Georgia
Sales Amount
2,975
3,000
FIGURE 9-2 MultiLookup function results
The MultiLookup function in the second column of the table requires an array of values
from the dataset bound to the table, which in this case is the StateProvinceCode eld in
Dataset2. You must rst use the Split function to convert the comma-separated list of values
in the StateProvinceCode eld into an array. Reporting Services operates on each element
of the array, matching it to the StProv eld in Dataset1, and then combining the results into
an array that you can then transform into a comma-separated list by using the Join function.
Here is the expression in the Territory column:
=Join(MultiLookup(Split(Fields!StateProvinceCode.Value, ","), Fields!StProv.Value,
Fields!StProvName.Value, "Dataset1 "), ", ")