SSRS 2008 R2 LookUp and LookUpSet Functions

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")

LookUp

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.

LookUp Report Preview

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.

LookUpSet Report Preview with 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.

LookUpSet Report Preview

Pretty cool, huh?

Advertisements

6 thoughts on “SSRS 2008 R2 LookUp and LookUpSet Functions”

  1. the LookupSet function doesnot work for me. It keeps sayind end of statement expected and I tried to fix the brackets aswell. it doesn’t work

  2. Very helpful post!
    Though it leaves me hoping for way of doing this, but have the set use/create multiple records, instead of using a join in one record only, something which I’m afraid is not possible?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s