In today’s post, I’m continuing an exploration of the lookup functions in SQL Server 2008 R2 Reporting Services. I began with the Lookup function in Part I of this series. In general, the lookup functions allow you to combine data from two datasets in a single data region in report. There are three lookup functions available:
Today, we’ll take a closer look at the MultiLookup function. Remember from the discussion in Part I that the Lookup function evaluates one value per row in the source to find one value for that row in the destination. By contrast, the MultiLookup function evaluates one set of values per row, finds each matching value in the destination, and returns the matches in a set. To better understand how to use it, let’s look at an example of how to use this function. In this example, I want to produce a list of the sales of each salesperson and include a list of the states in each person’s sales territory. In my first dataset, I have a comma-delimited list of two-letter codes for the states in each person’s sales territory. In the second dataset, I have a list of states with their corresponding two-letter codes. The two datasets are shown below in Figure 1.
Next, I create a simple table that contains the SalesPerson and the SalesAmount field from Dataset1. I also have an expression in the first column. The layout and a portion of the report are shown in Figure 2.
The expression uses the MultiLookup function and looks like this:
=Join(MultiLookup(Split(Fields!StateList.Value, ","), Fields!StateProvinceCode.Value, Fields!Name.Value,"Dataset2"), chr(10))
The MultiLookup function takes four arguments:
- The first argument, Split(Fields!StateList.Value, “,”), is an expression based on the StateList field found in the dataset that is bound to the data region, which is Dataset1 in this example. The StateList field contains a comma-delimited list which is transformed into an array by using the Split function.
- The second argument is the StateProvinceCode field in Dataset2 that you use as the lookup field. In other words, you are matching each item in the array made from the StateList field in Dataset1 with a StateProvinceCode in Dataset2. The values to match must have the same data type.
- The third argument, Fields!Name.Value, is the field (or expression) in the second dataset that you want to return as the function result. The difference here between the Lookup and MultiLookp functions is that the Lookup function returns a single value but MultiLookup returns an array.
- The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.
Because this function returns an array of values, the Join function is used to convert the array back to a string of values. For presentation purposes, rather than use a comma separator, I used chr(10) to present each state on a separate line.
Figure 3 shows another way to visualize what the MultiLookup function does.
In my next post, I’ll explain the last of the lookup functions, LookupSet.
I was wondering if there is a link to part 1 of the Lookup series. I have done a lot of reports using SQL Server 2005 SSRS and we are on the threshold of moving to 2008 SSRS with a possible complete redesign of our reports taking advantage of 2008’s new features.
Articles like yours are a real help. Also, could you recommend a class and/or a convention that covers more of the 2008 vs 2005 additions/improvements versus a how to write an SSRS report type of class. I think we’re past that stage.
Thanks in advance for your reply,
Itron Senior Software Engineer
I’m glad to hear you found the article helpful. The link to Part I is a hyperlink on those words (Part I) above. I remember fixing a link a week or so ago, but don’t remember if that was the one. Here’s the link if it’s not working for you: http://blog.datainspirations.com/2010/03/19/sql-server-2008-r2-reporting-services-look-up-look-down-look-all-around-part-i/
Are you planning to move to 2008 SSRS or 2008 R2 SSRS? As for classes or conventions, you just missed me at the SQL Server Conference & Expo last month in Las Vegas where I presented a session on the new features of 2008 R2. But another resource that goes into more depth than that session is a series of screencast lectures, demonstrations, and hands on labs that are focused on 2008 R2 SSRS (and other new BI features in SharePoint and Excel). This resource is the SQL Server 2008 R2 Developer’s Kit – which is currently released but it’s a bit old with recordings from January. I am just wrapping up a new round of recordings, so watch for an update to the kit to get the latest and greatest for the RTM version: http://www.microsoft.com/downloads/details.aspx?FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d&displaylang=en. If instead you’re not ready to move to 2008 R2, there are also resources for the 2008 version here: http://sqlserver2008jumpstart.microsofttraining.com/content/info.asp?CcpSubsiteID=69&infoid=27. Whether you move to 2008 or 2008 R2, you’ll find a lot to like about Reporting Services as compared to the earlier versions.
I am using R2 now and I have a question if these 3 lookups that are available in R2 is possible with my current report.
In my report I am using 2 cubes (I used 2 matrix). In my Matrix (Tablix) I have Geography grouping (the 50 US States) under columns group. What I need is to add the total sales from each state and show it on my first Matrix.
Your input will be appreciated.
If I understand correctly what you’d like to accomplish, I believe it’s possible. You will need to make sure that each dataset that you’re using has a field in common. Then you associate one dataset with the first matrix and use a Lookup function to match the state in that first dataset to a state in the second dataset to return the total sales for that state from the second dataset. I hope that helps!
[…] 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 […]