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

March 19, 2010

SQL Server 2008 R2 Reporting Services is loaded with features for the report author. In today’s post and two posts that will appear next week, I’ll explore the lookup functions which enable you to combine data from two datasets in a single data region which not possible in earlier versions of Reporting Services. If all of the data in a report comes from a single data source, then it’s probably easier to create a single dataset by using a SQL query that joins the data from multiple tables. However, there might be situations when you can’t write the query to join the data in advance. For example, you might have some data coming from a relational source and other data in a CSV file. Or perhaps you’re taking advantage of shared datasets in Reporting Services 2008 R2 and need a way to link together data from two shared datasets in one data region. That’s where the new Reporting Services lookup functions come in handy.

There are three lookup functions available:

My focus today is on the Lookup function. This function is in effect a join between two datasets where there is a one-to-one relationship between the source and destination. Let’s say that I want to produce a report of sales by state, but the dataset that I have for sales provides the two-letter code for state. Let’s say also that I find another dataset that provides a list of states and the two-letter code for each state. Now I have two datasets available, shown below in Figure 1, for my report.

Figure 1

Next, I create a simple table, add the SalesAmount field from Dataset1 to the table, and add an expression in the first column. The layout and a portion of the report are shown in Figure 2.

Figure 2

The expression uses the Lookup function and looks like this:

=Lookup(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value,Fields!Name.Value,"Dataset2")

The Lookup function takes four arguments:

  1. The first argument, Fields!StateProvinceCode, is a field (or expression based on a field) from the dataset that is bound to the data region, which is Dataset1 in this example.
  2. The second argument is a field (or expression) from the dataset that you want to use for the lookup. In this example, the second dataset also contains a field called StateProvinceCode, but it’s okay if the field names are different in each dataset. They should, of course, contain data that matches up and they must have the same data type. You can think of the first and second arguments as analogous to two columns that you want to use to define a join between two tables.
  3. The third argument, which is Fields!Name.Value in this example, is the field (or expression) in the second dataset that you want to return as the function result.
  4. The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.

If you need to match two fields in the first dataset to two fields in the second dataset, that’s okay. You simply create an expression to concatenate the pair of fields and use that expression in the argument in the Lookup function. Your expression cannot include report or group variables, however, nor can it include another lookup function.

Figure 3 shows another way to visualize what the Lookup function does.

Figure 3

Note that Reporting Services processes the Lookup function on the scope of the expression, which is in this case is the detail rows of the table which correspond to the rows of Dataset1. Using the Lookup function, Reporting Services uses a single value for the current row in the first dataset to find the first match in the second dataset and returns a single value. By contrast, the other two 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 functions.

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

  1. Very well written! This is quite helpful, thank you for taking the time to make this available.

  2. Thank you for the information. I tried it and it worked great. This is something that we had to do in SSRS 2005 with custom code and timing the dataset that loaded a custom code variable in memory just to use custom code to retrieve the info from that same cache in the report.

    I think one thing that is lacking is that one can not add a calculated field to a dataset and call the same lookup function. When one does that then an error occurs stating that it can’t be done for scope purposes.

    Do you know of a way to lookup information from another database and include it in a calculate field on a source dataset by a key code? If it is done at the data set level and not the table executing then one could do cool things like group/filter/sort by the new field.

  3. Hi Marc,

    I don’t have an idea immediately. I’m in travel mode before the long weekend, but I’ll give it some thought next week. If I come up with a solution, I’ll let you know.

  4. Hi Stacia,

    I tried to use the Lookup-Function in exactly this way.
    I my case the Expression-Builder stops me from editing.
    The table you use in Figure 2 to insert the expression, is connected to only one dataset (in your case Dataset1) as you can see in the Table/Tablix properties.
    Fields used in the expression Builder can only be selected from this dataset (Dataset1). All other Fields (e.g. from the Dataset2) are not available and produce runtime errors( e.g. in the Preview). Is this because of the situation: the datasets are build on different Sources ?
    There is something wrong in my scenario, because otherwise the Lookup-Function ( only for one Dataset ) makes no sense.

    Any idea ?

    Stefan

  5. Hi Stefan,

    You are correct that the expression builder will let you select fields only from the dataset that is connected to the data region. You have to know the name of the field from the other dataset in order to use it in the Lookup function. I have successfully used this function with datasets that use different sources – which is the only time this technique makes sense to me because if they used the same source, I could use a join operation in the dataset query which would be preferable for better performance when working with large data volumes.

    If you’re running into an error in Preview mode, then you might not have the right data type or something doesn’t match exactly between the fields that you are using to compare with one another in the first and second arguments of the Lookup function.

  6. very well, wait for part 2, 3 … :)

  7. Hi Annie – Part 2 and 3 are already posted. You can find them here:

    http://blog.datainspirations.com/2010/03/29/sql-server-2008-r2-reporting-services-look-up-part-ii-2/
    http://blog.datainspirations.com/2010/04/07/sql-server-2008-r2-reporting-services-look-up-part-iii/

  8. Awesome…it rocks!!!

  9. Hi Stacia, I was checking the solution and I have a very strange problem, I’m not able to preview the report, it keeps telling me that the scope parameter is wrong (And it is correct). I have two identical datasets (1 & 2) and I just changed a value column in the second one, I want to bring this additional column from the second data set in the tablix linked to the dataset1. It is a really simple test but looks like only if I change the fourth parameter to the dataset1 the preview works, and obviously that is not what I want to accomplish.

    Any Idea?

  10. It certainly sounds like you’re doing a simple test. I have been able to make this work several times myself. The only think I can think of is that your scope argument is not matching the name of the second dataset exactly – it’s case-sensitive. Is it possible you’re not matching case? Another thing to try would be to make some change to the second dataset – maybe add another field so that it’s not identical to the first dataset.

  11. Hi Stacia, I added another field in the second dataset and worked fine, the strange thing is that I created a new project and also worked (with two identical datasets).

    The thing that I wanted to test basically was the possibility to have a dataset with the actuals and another from a second data source with the budget information and be able to show on the same chart a line for the budget and another for the actuals matching the same registers. This for many applications has a lot of potencial.
    Thanks

  12. Hi Stacia,
    I have created a lookup as you explained, and it works perfect when I test it. It builds successfully. However, when I try to publish my report to our webserver, it fails because it does not recognise the field from the second dataset?

    Thank You

  13. Hi.

    This is my statement
    =Lookup(Fields!SectionName.Value,Fields!SectionName.Value,Fields!MineOverseerGuid.Value,”dsMinHalfLevelMng”)

    I have a hyperlink on my report. The user clicks on the SectionName that he wants to view, and the Lookup must return the guid for the section so that it can be passed to a drill-down report.

    I am trying to retrive the Guid where the 2 sections match. The Guid comes from the second dataset – dsMinHalfLevelMng

    This works when you test it, but when you try to publish to the webserver, this is the error that pops up:

    “The Value expression for the text box ‘SectionName’ refers to the field ‘MineOverseerGuid’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.”

    Please see if you can help, I am out of options.

    Thanks

  14. Hi Stacia,

    I tried to use the Lookup-Function in the same way.

    My requirement is, I have to show the fields on the report from different databases (Oracle,SQL2005 and DB2).
    I created one dataset for each data source. All the sources has a link field to join. When I try to use the Lookup function in the Expression editor I’m getting “[BC30451] Name Lookup is not declared” error.

    Here are the Datasets and Fields what I have used in my report

    Dataset1(Oracle) Dataset2(db2)
    subproject_id Subproject_id Proj_hours Issue_count

    =Lookup(Fields!subproject_id.value,Feilds!subproject_id.value,Fields!Issue_count.value,”Dataset2(db2)”)

    I’m using SQLSERVER2008R2 SP1.

    Any Suggestions?

  15. Thank you for the article. Very clear and well written. It helped with a concern I had on using dimension tables to derive group labels.

  16. Eduan,

    It’s hard to say exactly why it works in BIDS but not on the server. Possibilities – the server can’t execute both queries successfully or for some reason the RDL on the server doesn’t match the RDL that you’re using in BIDS or the server isn’t updated to SQL Server 2008 R2 or the project properties in BIDS have the target version set to SQL Server 2008. Check the project properties first to make sure the target is SQL Server 2008 R2. If that’s correct, try deleting the RDL from the server and redeploying.

  17. Hi Eduan,

    Thanks for more details. I didn’t see this comment when I responded to your first comment. It sounds like the field is not visible to the server for some reason. I would try deleting it from the server and redeploying it.

  18. It’s really important that you use the same case and the same spelling for each field reference. I notice some typos in your lookup expression, and also notice that you have subproject_ID in one case and Subproject_id in another case, but your lookup expression uses lowercase for the first and second arguments. that would be the first thing to test.

  19. Hi Stacia,

    Very good example. Simple and easy to understand.

    Have you tried this with Data Source View/Report Model?

    Thank you

  20. Hi Shalinder,

    No I haven’t used this technique with report models, but I happen to be working on one at the moment. I’ll add it as a topic to this series on filters.

    Stacia

  21. Its very Good…. Its works…. I liked it…
    Thanks a lot

  22. That is great. Good explanation. I want to do a similar thing but I want to use a Report Parameter for the left side (first function parameter) like this:

    =Lookup(Parameters!ProjectID.Value,Fields!ProjectID.Value,Fields!ProjectName,”ProjectList”)

    It doesn’t seem to work. Does anyone have a solution for getting the field I want out of this reused data set in my Report?

    Thanks,

    Brian

  23. Thanks for the information. Are these functions accessible from Custom Code? I want to write a function in the Report custom code section which manipulates the input values before calling Lookup to find value in my dataSet. When I run the function, I got an error that says Lookup is not declared. I probably need to prefix Lookup with a Namespace. But what is the namespace for the SSRS’s Lookup function?

  24. Thanks Stacia, Your article was very helpful.

  25. It helped me a lot.I was using sharepoint with SSRS where i am not having any list joining facility.

  26. Excellent – thanks for explaining this so succinctly! Big big love.

  27. [...] 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 [...]

  28. I’m trying to use the lookup function in a graph but I’m only getting the first row of the table to show up in the graph. I’ve tried to use lookupset and multilookup, but I’m not sure what syntax to use since I’m not going to concatignate these rows. Do you have any clues if this works in graphs or onnly in tables?

  29. The lookup function is just an expression that returns a value, so whether you use it in a graph (chart) or a table or a matrix should not matter. Furthermore, the lookup would not affect how many rows get turned for use in the data regions. Instead, it sounds like you’re not using an aggregate expression for the chart values. Another thing to try would be to set up a matrix that contains the same elements as your chart so that you have greater visibility into what’s happening in the results and work with the expressions there until you see what you want, then recreate the expressions in the chart.

  30. Hi Stacia !!

    Thank you for the information, please could you help me with finding a function like “lookup” for Reporting Services 2005.

    Or, Could you tell me how can I combine data from two datasets in a single data region but in Reporting Services 2005 ?

    I know that “lookup” does not exist in 2005 version, but I think that there is a way to do this without this function…

  31. Depending on your report requirements, you might be able to do something with a sub report that you place inside a table or matrix. I wrote about this in my SSRS 2005 Step by Step book. Basically you pass in a parameter to the sub report with the value that you are using to do the lookup. Performance could be a problem with this approach though. The only other way would be to develop your own function, either as a custom assembly or as embedded code in the report (http://support.microsoft.com/kb/920769). I suspect performance could be an issue with this approach as well.

  32. Stacia,

    Now, the next step for me is to decide which is the best way to do my reports.

    Thanks a lot for your Replay, this information was very helpful…

  33. Stacia, Great information. However, I’m having the same problem as Marcus from above. I’m not sure how to add a calculated field to a dataset and call the same lookup function.

  34. I’m able to bring across an image using =Lookup(Fields!ReportImageID.Value, Fields!ReportImageID.Value, System.Convert.ToBase64String(Fields!ReportImage.Value),”ReportImage”). I also bring back a description field which identifies the logo being pulled in. I have a textbox next to the image in the report header using ReportItems. The Description changes with the group, but the image doesn’t change.

    Any ideas?

    Thanks!

  35. Sorry – no ideas at the moment. I’ll play around with this when I get a chance, but it’s likely to be a few more days before I have a chance due to my current schedule

  36. Hi Stacia,
    I am trying to create a report from two tables of the same database. Below is the expression I have used..

    =Lookup(Fields!AuditEventKey.Value,Fields!AuditEventKey.Value,Fields!Operation.Value,”DataSet2″)

    But I am getting ‘Unknown Collection Member’ error…
    Please help asap..

    Thanks in advance

  37. That message means that one of the arguments is invalid. Either AuditEventKey is not a valid field in your first or second dataset or Operation is invalid or your dataset name “DataSet2″ doesn’t match the actual dataset name correctly.

  38. I am using the lookup function and it is succesfull in pulling the first result it finds in the dataset most of the time. However, some of the rows are blank, and when I run the query for the dataset, the item is in the query results. Why won’t the report populate the lookup?

  39. [...] (une jointure interne, en somme). Pour les anglicistes, Stacia Misner a fait un très bon papier ici que je ne m’amuserai pas à [...]

  40. NOTE!!!

    I kept getting a “#ERROR” message in my tablix header, the trick for me was:

    FIRST(Lookup(…)).

    And yes, the third parameter seems wrong (it’s in the other data-set, but don’t worry, that’s how it works).

  41. Hi Peter, Yes tablix headers expect an aggregate function, so that makes sense.

  42. Hi Jan, It’s very difficult to provide an explanation without more information about your scenario.

Leave a Reply