Specifications

168 CHAPTER 9 Reporting Services Enhancements
When there is a one-to-many relationship between the source and destination values, you
use the LookupSet function. This function accepts a single value from the source dataset as
input and returns an array of matching values from the destination dataset. You could then
use the Join function to convert the result into a delimited string, as in the example for the
MultiLookup function, or you could use other functions that operate on arrays, such as the
Count function, as shown in Figure 9-3.
Salesperson SalespersonCode
Dataset2
DC
TR
David Campbell
Tsvi Reiter
CustomerName
Salesperson-
Code
DC
DC
DC
TR
K. Gregersen
T. Yee
L. Miller
J. Frank
Dataset2
K. Gregersen
T. Yee
L. Miller
Customer Counts
Salesperson
David Campbell
Tsvi Reiter
Customer Count
3
1
J. Frank
FIGURE 9-3 LookupSet function results
The Customer Count column uses this expression:
LookupSet(Fields!SalespersonCode.Value,Fields!SalesperonCode.Value,
Fields!CustomerName.Value,"Dataset2").Length
Aggregation
The aggregate functions available in Reporting Services since its rst release with the SQL
Server 2000 platform provided all the functionality most people needed most of the time.
However, if you needed to use the result of an aggregate function as input for another
aggregate function and weren’t willing or able to put the data into a SQL Server Analysis
Services cube rst, you had no choice but to preprocess the results in the dataset query.
In other words, you were required to do the rst level of aggregation in the dataset query,
and then you could perform the second level of aggregation by using an expression in the
report. Now, with SQL Server 2008 R2 Reporting Services, you can nest an aggregate func-
tion inside another aggregate function. Put another way, you can aggregate an aggregation.
The example table in Figure 9-4 shows the calculation of average monthly sales for a selected
year. The dataset contains one row for each product, which the report groups by year and by
month while hiding the detail rows.