Microsoft SQL Server 2008 R2 810-08230 Benutzerhandbuch
Produktcode
810-08230
Expression Language Improvements
CHAPTER 9
167
=Lookup(Fields!StateProvinceCode.Value, Fields!StProv.Value,
Fields!StProvName.Value, "Dataset1")
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 .
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 field in
Dataset2. You must first use the Split function to convert the comma-separated list of values
in the StateProvinceCode field into an array. Reporting Services operates on each element
of the array, matching it to the StProv field 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:
Dataset2. You must first use the Split function to convert the comma-separated list of values
in the StateProvinceCode field into an array. Reporting Services operates on each element
of the array, matching it to the StProv field 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 "), ", ")
Fields!StProvName.Value, "Dataset1 "), ", ")