In today’s post, I’m continuing an exploration of the lookup functions in SQL Server 2008 R2 Reporting Services. I began with the Lookup function in Part I of this series. In general, the lookup functions allow you to combine data from two datasets in a single data region in report. There are three lookup functions available:
Today, we’ll take a closer look at the MultiLookup function. Remember from the discussion in Part I that the Lookup function evaluates one value per row in the source to find one value for that row in the destination. By contrast, the MultiLookup function evaluates one set of values per row, finds each matching value in the destination, and returns the matches in a set. To better understand how to use it, let’s look at an example of how to use this function. In this example, I want to produce a list of the sales of each salesperson and include a list of the states in each person’s sales territory. In my first dataset, I have a comma-delimited list of two-letter codes for the states in each person’s sales territory. In the second dataset, I have a list of states with their corresponding two-letter codes. The two datasets are shown below in Figure 1.
Next, I create a simple table that contains the SalesPerson and the SalesAmount field from Dataset1. I also have an expression in the first column. The layout and a portion of the report are shown in Figure 2.
The expression uses the MultiLookup function and looks like this:
=Join(MultiLookup(Split(Fields!StateList.Value, ","), Fields!StateProvinceCode.Value, Fields!Name.Value,"Dataset2"), chr(10))
The MultiLookup function takes four arguments:
- The first argument, Split(Fields!StateList.Value, “,”), is an expression based on the StateList field found in the dataset that is bound to the data region, which is Dataset1 in this example. The StateList field contains a comma-delimited list which is transformed into an array by using the Split function.
- The second argument is the StateProvinceCode field in Dataset2 that you use as the lookup field. In other words, you are matching each item in the array made from the StateList field in Dataset1 with a StateProvinceCode in Dataset2. The values to match must have the same data type.
- The third argument, Fields!Name.Value, is the field (or expression) in the second dataset that you want to return as the function result. The difference here between the Lookup and MultiLookp functions is that the Lookup function returns a single value but MultiLookup returns an array.
- The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.
Because this function returns an array of values, the Join function is used to convert the array back to a string of values. For presentation purposes, rather than use a comma separator, I used chr(10) to present each state on a separate line.
Figure 3 shows another way to visualize what the MultiLookup function does.
In my next post, I’ll explain the last of the lookup functions, LookupSet.