SSRS SQL 2008 R2: using Lookup to connect 2 AS datasets

By | August 11, 2009

Microsoft released a BI update to SQL Server 2008, aside from Gemini it has some other great new functions like the Lookup Function in reporting services. I was waiting for this one, you can lookup the first matching value for the specified name from a dataset that contains name/value pairs. This gives you the opportunity to join 2 datasets and whats best from 2 different cubes!

One mayor drawback is that you can only join on one key and a SSAS dataset usually doesn’t have a single key since you have data on an x and y axel, but with some custom work we can fix that.

In the following sample I have put data of 2 adventureworks datasets on one tablix. Each dataset contains year and Sales Territory Country and a measure. Lookup uses 2 keys to match the data from 2 datasets, I made a new unique key for each dataset combining the the uniquename of the x and y axel and added it as a calculated field to each dataset, added a field to each dataset with value “Fields!Sales_Territory_Country.UniqueName + Fields!Calendar_Year.UniqueName”.

Add the value of one dataset to a tablix

tablix

Then add a new column with the following expression to add the Internet_Order_Count value from the other dataset to the tablix:

=Lookup(Fields!CombKey.Value,Fields!CombKey2.Value,Fields!Internet_Order_Count.Value,”DataSet2″)

and thus adding the value from the 2nd dataset to the tablix, works like a charm. I even tested putting a filter on dataset 2 to make sure there aren’t an even amount of values and that works too, it just returns a null value.

  • Great post! I do want to point out that you can use expressions in the Lookup() functions arguments so if you have two keys, you can concatentate them directly in the expression and do the match.

  • In addition, you may want to take a look at the MultiLookup function, which can lookup a set of source expressions. http://msdn.microsoft.com/en-us/library/ee210583(SQL.105).aspx

  • Kasper de Jonge

    @Sean Boon
    Thanks great idea, didn’t occur to me to do it in the expression directly

    @Robert Bruckner
    thx i will look into it.

  • hAl

    Thanks, just what I needed.

    For me it was still worth adding the concatenated key to the datasets as I’m using it many times. Also, if the key ever has to change for some reason in the future then I only have to change it in the datasets.

  • Nat

    Thanks a lot!