LookUp and LookUpSet Functions New in SSRS 2008 R2

With the release of R2 came a couple of pretty nifty new expressions in SSRS. The LookUp function allows us to perform a look up against a separate data set in our report using a field in our source data set where there is a 1 to 1 relationship. Likewise, the LookUpSet function allows you to do a look up values in a separate data set where there is a 1 to many relationship.

The LookUp and LookUpSet functions are useful when you cannot write a query to join our two data sets together. So maybe we have a shared data source in our report that returns some data that we need to join to an embedded data set in our report. We could use the LookUp and/or LookUpSet functions to bring those two data sets together within our report.

Let’s walk through a couple examples so we can see how to use these new functions. First, lets check out the LookUp function. I’ve got a shared data set in my Report Project that returns the total Reseller sales but in my report I have an embedded data set that has all my Resellers names. I’ll use the LookUp function to bring those two data sets together.

Our expression to look up the TotalSales from our shared data set looks like this:

=LookUp(Fields!ResellerKey.Value,Fields!ResellerKey.Value,Fields!TotalSales.Value,"ResellerSales")

Using the LookUp function, I’m able to look up the Total Sales Amount from the destination data set and bring it together with our source data set.

Now lets take a look at the LookUpSet function. At first glance, it appears to work exactly the same as the LookUp function.

=LookUpSet(Fields!CustomerKey.Value,Fields!CustomerKey.Value,Fields!OrderNumber.Value, "CustomerOrders")

But if we run our report with this expression, we’re going to get an error.

This is because the LookUpSet function brings back a collection of results. We cannot simply display the results in a textbox in our tablix. We need to concatenate the results into a string. To do this, we are going to use the Join function with our LookUpSet function.

=Join(LookUpSet(Fields!CustomerKey.Value,Fields!CustomerKey.Value,Fields!OrderNumber.Value, "CustomerOrders"),"," + vbcrlf)

The Join function takes a collection of objects and delimits them with our specified delimiter. In this case, our delimiter is a comma with the VB carriage return line feed (vbcrlf). Using the Join and LookUpSet functions, we are able to look up all the order numbers from the destination data set and bring those into the same tablix as our source data set.

Pretty cool, huh?