SQL Server 2008 R2 Reporting Services is loaded with features for the report author. In today’s post and two posts that will appear next week, I’ll explore the lookup functions which enable you to combine data from two datasets in a single data region which not possible in earlier versions of Reporting Services. If all of the data in a report comes from a single data source, then it’s probably easier to create a single dataset by using a SQL query that joins the data from multiple tables. However, there might be situations when you can’t write the query to join the data in advance. For example, you might have some data coming from a relational source and other data in a CSV file. Or perhaps you’re taking advantage of shared datasets in Reporting Services 2008 R2 and need a way to link together data from two shared datasets in one data region. That’s where the new Reporting Services lookup functions come in handy.

There are three lookup functions available:

• Lookup
• MultiLookup
• LookupSet

My focus today is on the Lookup function. This function is in effect a join between two datasets where there is a one-to-one relationship between the source and destination. Let’s say that I want to produce a report of sales by state, but the dataset that I have for sales provides the two-letter code for state. Let’s say also that I find another dataset that provides a list of states and the two-letter code for each state. Now I have two datasets available, shown below in Figure 1, for my report.

Figure 1

Next, I create a simple table, add the SalesAmount field from Dataset1 to the table, and add an expression in the first column. The layout and a portion of the report are shown in Figure 2.

Figure 2

The expression uses the Lookup function and looks like this:

=Lookup(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value,Fields!Name.Value,"Dataset2")

The Lookup function takes four arguments:

1. The first argument, Fields!StateProvinceCode, is a field (or expression based on a field) from the dataset that is bound to the data region, which is Dataset1 in this example.
2. The second argument is a field (or expression) from the dataset that you want to use for the lookup. In this example, the second dataset also contains a field called StateProvinceCode, but it’s okay if the field names are different in each dataset. They should, of course, contain data that matches up and they must have the same data type. You can think of the first and second arguments as analogous to two columns that you want to use to define a join between two tables.
3. The third argument, which is Fields!Name.Value in this example, is the field (or expression) in the second dataset that you want to return as the function result.
4. The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.

If you need to match two fields in the first dataset to two fields in the second dataset, that’s okay. You simply create an expression to concatenate the pair of fields and use that expression in the argument in the Lookup function. Your expression cannot include report or group variables, however, nor can it include another lookup function.

Figure 3 shows another way to visualize what the Lookup function does.

Figure 3

Note that Reporting Services processes the Lookup function on the scope of the expression, which is in this case is the detail rows of the table which correspond to the rows of Dataset1. Using the Lookup function, Reporting Services uses a single value for the current row in the first dataset to find the first match in the second dataset and returns a single value. By contrast, the other two lookup functions, MultiLookup and LookupSet work with arrays. Next week, I’ll continue with Part II and Part III to explain how to use these functions.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

Filed under:
Reporting Services by Stacia Misner