Microsoft SQL Server 2008 R2 810-08230 Benutzerhandbuch
Produktcode
810-08230
166
CHAPTER 9
Reporting Services Enhancements
Combining Data from More Than One Dataset
To display data from more than one source in a table (or in any data region, for that matter),
you must create a dataset that somehow combines the data because a data region binds to
one and only one dataset . You could create a query for the dataset that joins the data if both
sources are relational and accessible with the same authentication . But what if the data comes
from different relational platforms? Or what if some of the data comes from SQL Server and
other data comes from a SharePoint list? And even if the sources are relational, what if you
can access only stored procedures and are unable to create a query to join the sources? These
are just a few examples of situations in which the new Lookup functions in the Reporting
Services expression language can help .
you must create a dataset that somehow combines the data because a data region binds to
one and only one dataset . You could create a query for the dataset that joins the data if both
sources are relational and accessible with the same authentication . But what if the data comes
from different relational platforms? Or what if some of the data comes from SQL Server and
other data comes from a SharePoint list? And even if the sources are relational, what if you
can access only stored procedures and are unable to create a query to join the sources? These
are just a few examples of situations in which the new Lookup functions in the Reporting
Services expression language can help .
In general, the three new functions, Lookup, MultiLookup, and LookupSet, work similarly
by using a value from the dataset bound to the data region (the source) and matching it to
a value in a second dataset (the destination). The difference between the functions reflects
whether the input or output is a single value or multiple values .
a value in a second dataset (the destination). The difference between the functions reflects
whether the input or output is a single value or multiple values .
You use the Lookup function when there is a one-to-one relationship between the source
and destination . The Lookup function matches one source value to one destination value at a
time, as shown in Figure 9-1 .
time, as shown in Figure 9-1 .
Month-to-Date Sales
State/Province
British Columbia
Oregon
Washington
Sales Amount
1,225
750
1,000
StProvName
British Columbia
Oregon
Washington
StProv
BC
OR
WA
StateProvinceCode
BC
OR
WA
SalesAmount
1225
750
1000
Dataset1
Dataset2
FIGURE 9-1
Lookup function results
In the example, the resulting report displays a table for the sales data returned for
Dataset2, but rather than displaying the StateProvinceCode field from the same dataset, the
Lookup function in the first column of the table instructs Reporting Services to match each
value in that field from Dataset2 with the StProv field in Dataset1 and then to display the
corresponding StProvName. The expression in the first column of the table is shown here:
Lookup function in the first column of the table instructs Reporting Services to match each
value in that field from Dataset2 with the StProv field in Dataset1 and then to display the
corresponding StProvName. The expression in the first column of the table is shown here: