SQL Server 2008 R2: Reporting Services – Look Up, Look Down, Look All Around (Part III)

April 7, 2010

Today’s post is the 3rd post in a series about the new lookup functions in SQL Server 2008 R2 Reporting Services. I covered the Lookup function in Part I of this series and the MultiLookup function in Part II.  The third and final lookup function is LookupSet.

First, a quick review. The Lookup function evaluates one value per row in the source to find one value for that row in the destination and the MultiLookup function evaluates one set of values per row, finds each matching value in the destination, and returns the matches in a set. LookupSet evaluates each row in the source and looks for all matches in the destination, returning the resulting matches as a set.

To illustrate how to use LookupSet, I want to produce a list of the sales by state and include a count of customers so that I can compare the average sales per customer by state. In Dataset1, I have a list of the states’ two-letter codes and total sales. In Dataset2, I have a list of customer IDs and the state associated with the customer. You can see a sample of data in each of these datasets below in Figure 1.

Figure 1

If you look at the report layout and a portion of the report shown in Figure 2, you can infer that I have a combination of lookups in play. The report is a simple table that includes StateProvinceCode in the first column. In the State column, I  use the Lookup function in an expression to get the state name from a third dataset. In the Customer Count column, I have an expression that includes the LookupSet function to retrieve the set of customers for the state on the current row and then I use a Count function on that set. Of course, if I wanted a list of those customers, I could use the Join function to convert the array that returns from the LookupSet function into a string. Continuing on with this example, though, I have SalesAmount from Dataset1, and then another expression for “Avg Sale Per Customer” that divides SalesAmount by Customer Count.

Figure 2

The expression in the Customer Count column uses the LookupSet function and looks like this:

=LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value,
Fields!CustomerID.Value,"Dataset2").Length

LookupSet takes four arguments, much like the Lookup function except that Lookup returns a single value per row whereas LookupSet returns an array:

  1. The first argument, Fields!StateProvinceCode.Value,  is a field in the dataset that is bound to the data region (Dataset1 in this example).
  2. The second argument is the StateProvinceCode field in Dataset2 that you use as the lookup field. The LookupSet function matches each value in the Dataset1 field with a value in the Dataset2 field. The values to match must have the same data type.
  3. The third argument, Fields!CustomerID.Value, is the field in Dataset2 that you want to return as the function result. The difference here between the Lookup and LookupSet functions is that the Lookup function returns the first value it finds, but LookupSet returns an array of all matching values that it finds.
  4. The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.

LookupSet returns an array, so you can’t use the results directly in the table but must use some other function to operate on the array to get a single value.

I like pictures to help me understand how things work, so I created Figure 3 below as another way to visualize what the LookupSet function does.

Figure 3

And that concludes this series on the new Lookup functions in SQL Server 2008 R2 Reporting Services. I hope you find these explanations useful. I admit that I haven’t had a compelling need for them, but that’s because I do the necessary integration in the back-end first – either by querying a data mart/warehouse or by joining the data in the dataset. However, I can imagine scenarios where those two options aren’t available in which case having the Lookup functions as an alternative is helpful. What do you think?

29 Responses to “SQL Server 2008 R2: Reporting Services – Look Up, Look Down, Look All Around (Part III)”

  1. [...] This post was mentioned on Twitter by Jamie Thomson, Wesley Backelant, topsy_top20k_en, topsy_top20k, Derek Comingore and others. Derek Comingore said: RT @StaciaMisner: Blog: SQL Server 2008 R2: Reporting Services–Look Up, Look Down, Look All Around (Part III) – Lookup function in #ssrs http://bit.ly/bF7sB3 [...]

  2. Hi Stacia,
    Like yourself I don’t have a need, right now, to use these things right now as I also do these types of lookup operations during an ETL phase. Nonetheless it helps to know that these things exist (I didn’t know about them until I saw your tweet this morning) so thank you for putting this series together.

    -Jamie

  3. Would it be accurate to say that the Lookups constitute joins across Data Sources, even if those Data Sources point to different servers?

  4. I guess it depends on how precise you want to be with terminology. A join implies a single query executes to combine data if we think in relational database terms. In Reporting Services, each dataset that participates in the lookup executes independently of the other. The lookup occurs after the data is retrieved from the data source. That is, the lookup is a separate action from the query execution. Now ordinarily I would want to do joins in the relational query if I could because I want the database engine to do what it does best. I haven’t done benchmarking to prove it, but I would suspect that the database engine could outperform Reporting Services on very large datasets. However, there might be instances when I have completely different types of data sources that I cannot join using a relational query, in which case the lookup functions are a perfect solution.

  5. Is there anyway these lookup can refresh value on click on a field.

    I try Action of text box, only jump to report can pass parameter and refesh data.

    Is anyway to trigger data refresh on screen to sync muptilpe data regions ?

  6. Hi Annie – not directly. You’d have to refresh the report to get the queries to re-execute. Reporting Services 2008 R2 now has dynamic refresh – if there was some clever way that you could come up with an expression to trigger that refresh, that might be the solution.

  7. hi,

    are these Lookup / MultiLookup / LookupSet functions performing a left join?

    i need to do the following.

    dataset1 returns 3 columns.
    dataset2 returns 3 columns.

    2 of the 3 columns on each are ‘primary keys’ to be matched, joined on.

    i need to do a Full Join though, as i have values missing and/or present in both, so i want a full view across both datasets…

    which option to use? is this possible..?

    thanks !!

  8. The lookup functions don’t really perform a join in a T-SQL sense. All the work is performed on datasets after the query is performed. The behavior is more like a left join though because the results for dataset1 will display, although you might get an error if dataset2 doesn’t have a match for the lookup. You can add some error-trapping to your expression to deal with that. Handling a full join might be possible with some creative implementation of lookup functions using a combination of expressions, but it’s not handled natively by the lookup functions.

  9. Hi, The LookupSet returns number of elements. I want take the Last element of that. How to do that?
    Please help me.

    Thanks..

  10. Hi Mohamed,

    Well, the example in the post shows how to get a count of the elements:

    =LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value, Fields!CustomerID.Value,”Dataset2″).Length

    And since the results is an array, we know that the count of elements less 1 is the index to the last element in the array. So to get the last element, you would use this expression.

    =LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value, Fields!CustomerID.Value,”Dataset2″)(LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value, Fields!CustomerID.Value,”Dataset2″).Length -1)

    I hope that helps! — Stacia

  11. Anyway to Sum() the values returned? I am looking to combine current year and last year sales into one tablix that are currently in different datasets. Tried doing a:
    =SUM(LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value, Fields!CustomerID.Value,”Dataset2″)). Didn’t work.

  12. Never mind. Figured it out by passing the lookupset values to a custom function to convert and summarize.

  13. Hi Marko

    Can you let me know how you did that please.
    Im really stuck.

    Thanks

  14. Very nice article! I was hoping this would provide a solution to my issue which similar but different. I am trying to stack two MDX datasets together, very similar to a sql Union. Can lookups be used for this purpose?

  15. Hi Mike, You can think of lookups as working like a left outer join in T-SQL, so using a lookup to stack two MDX datasets together wouldn’t work. If it’s possible for you, I would recommend approaching the problem by using an OPENQUERY function as I describe in this post: http://blog.datainspirations.com/2010/11/30/creating-a-linked-server-for-analysis-services/. Then you can have two select statements to get the results for each MDX dataset and perform a union on the two result sets.

  16. [...] my next post, I’ll explain the last of the lookup functions, [...]

  17. Hi,

    The above worked well in SSRS(2008 version) but gave a deployment error saying that field from a data set not within the current scope cannot be used without applying an aggregate function. Am I getting anything wrong?

    Thanks.

  18. Hi Sandy – this feature is available only in SSRS 2008 R2.

  19. Thank you very much, I am creating some reports from data that is stored in SharePoint lists and this technique will be very helpful

  20. Hi, I am adding a column to a tablix that needs to draw its data from a different source, based on 2 columns in my tablix (e.g. select new_value where id1=col1 and id2=col2).
    Is it possible to do that within one of the lookup functions?
    If not, is there another way to do it within reporting services?

    thanks.

  21. Sorry for the tardy reply, Kelvin. I’m knee deep in finishing up a new book AND I’m moving in just a few days so it’s been a VERY busy month for me. Perhaps you’ve already worked this out for yourself, but if not… You can use expressions with the lookup functions so I would recommend that you create an expression that concatenates the two fields like this id1 + “-” + id2 and the target col1 + “-” + col2. Then the lookup should work for you.

  22. Hi,

    I need to get 4 columns from CubeDataSetA and 3 columns CubeDataSetB that can be associated via a common key. I think this is possible by including the 4 columns from CubeDataSetA directly in the report and then perfoming 3 separate lookups into CubeDataSetB to populate three expression based columns added to the report.
    Does that sound correct?
    Can it possilby be done with a single lookup to imporove performance?

    Thanks

  23. Actually it looks like I might be able to add three calculated fileds to CubeDataSetA which derive their values via LookupSet() into CubeDataSetB.

  24. Hi,

    I need to get 4 columns from CubeDataSetA and 3 columns CubeDataSetB that can be associated via a common key. I think this is possible by including the 4 columns from CubeDataSetA directly in the report and then perfoming 3 separate lookups into CubeDataSetB to populate three expression based columns added to the report.

    Also it appears that three calculated fields could be added to CubeDataSetA (which derive their values via LookupSet() into CubeDataSetB) or accomplished by simply adding the three as expression fields directly in the body of the report.

    Does that sound correct?

    Can it possilby be done with a single lookup to improve performance?

    Thanks

  25. And that’s another option. :)

  26. Your idea of the three calculated fields is a good one. The addition I would make is a fourth calculated field to do your lookup as one big result, and then use string functions in the three calculated field to parse out the relevant section of the string returned by the lookup. Try it and see!

  27. Found out calculated fields can’t use Lookups so it has to be an expression on the report.

    Turns out I really have a (zero or one) to (zero or many) IOW there can be orphans on either side.

    As such I am exploring the use of SSIS with MDX queries to temp tables and then using join, excepts, and unions to populate a working table to provide the source for the report. Perhpaps I can even use an SSIS dataset as the source. This is reather complicated though and requires SSRS to trigger an SSIS package. The reason is that the data comes from 2 separate cubes.

    Since my knowledge of MDX is not strong yet I may be I missing a better way to do this such as openquery and/or linking.

    If you think of somethig it would be great if you would point me in the right direction.

    BTW I am enjoying you SSAS tutorial on PluralSite – Nice Job!

    Thanks,
    Carl

  28. Thanks for letting me know how things worked out. It’s hard to say what the best approach should be without knowing your data architecture and testing query performance. There’s usually more than one way to tackle something. I have used openquery to bring data together from different places. Have you seen this post: http://blog.datainspirations.com/2010/11/30/creating-a-linked-server-for-analysis-services/?

    Also, glad to hear you’re like the Pluralsight course!

  29. How do I combine three datasets together. I have a project tracking report I’m trying to display. On my Subtask chart I’m trying to display the Project Name – Task Name – Subtask in that order. However, when i try to select the Project Name and incorporate it into the Subtask Chart it only allows me to select the first Project, when it should show the different projects associated to each task. If anyone knows how to combine three datasets like this please let me know. Thanks!

    Project Dataset
    -Project Name
    -ID

    Task Dataset
    -Task Name
    -ProjectID(Connected to Project Name)
    -Percent Complete

    Subtask Dataset
    -Subtask Name
    -TaskID(Connected to Task Name)
    -Percent Complete

Leave a Reply