Today’s post is the 3rd post in a series about the new lookup functions in SQL Server 2008 R2 Reporting Services. I covered the Lookup function in Part I of this series and the MultiLookup function in Part II.  The third and final lookup function is LookupSet.

First, a quick review. The Lookup function evaluates one value per row in the source to find one value for that row in the destination and the MultiLookup function evaluates one set of values per row, finds each matching value in the destination, and returns the matches in a set. LookupSet evaluates each row in the source and looks for all matches in the destination, returning the resulting matches as a set.

To illustrate how to use LookupSet, I want to produce a list of the sales by state and include a count of customers so that I can compare the average sales per customer by state. In Dataset1, I have a list of the states’ two-letter codes and total sales. In Dataset2, I have a list of customer IDs and the state associated with the customer. You can see a sample of data in each of these datasets below in Figure 1.

Figure 1

If you look at the report layout and a portion of the report shown in Figure 2, you can infer that I have a combination of lookups in play. The report is a simple table that includes StateProvinceCode in the first column. In the State column, I  use the Lookup function in an expression to get the state name from a third dataset. In the Customer Count column, I have an expression that includes the LookupSet function to retrieve the set of customers for the state on the current row and then I use a Count function on that set. Of course, if I wanted a list of those customers, I could use the Join function to convert the array that returns from the LookupSet function into a string. Continuing on with this example, though, I have SalesAmount from Dataset1, and then another expression for “Avg Sale Per Customer” that divides SalesAmount by Customer Count.

Figure 2

The expression in the Customer Count column uses the LookupSet function and looks like this:

=LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value,
Fields!CustomerID.Value,"Dataset2").Length

LookupSet takes four arguments, much like the Lookup function except that Lookup returns a single value per row whereas LookupSet returns an array:

  1. The first argument, Fields!StateProvinceCode.Value,  is a field in the dataset that is bound to the data region (Dataset1 in this example).
  2. The second argument is the StateProvinceCode field in Dataset2 that you use as the lookup field. The LookupSet function matches each value in the Dataset1 field with a value in the Dataset2 field. The values to match must have the same data type.
  3. The third argument, Fields!CustomerID.Value, is the field in Dataset2 that you want to return as the function result. The difference here between the Lookup and LookupSet functions is that the Lookup function returns the first value it finds, but LookupSet returns an array of all matching values that it finds.
  4. The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.

LookupSet returns an array, so you can’t use the results directly in the table but must use some other function to operate on the array to get a single value.

I like pictures to help me understand how things work, so I created Figure 3 below as another way to visualize what the LookupSet function does.

Figure 3

And that concludes this series on the new Lookup functions in SQL Server 2008 R2 Reporting Services. I hope you find these explanations useful. I admit that I haven’t had a compelling need for them, but that’s because I do the necessary integration in the back-end first – either by querying a data mart/warehouse or by joining the data in the dataset. However, I can imagine scenarios where those two options aren’t available in which case having the Lookup functions as an alternative is helpful. What do you think?