SSRS 2008 R2 includes some new expressions which allow us to bring into one table values from another dataset based on the table’s current dataset fields. In other words, Lookup <fieldname> from <dataset2> where <fieldname><dataset1> = <fieldname><dataset2>.
The LookUp function is based on a 1 to 1 relationship, bringing in one value based on the match.
The LookUpSet function is based on a 1 to many relationship, pulling in multiple values based on the match.
Here are some examples:
We have a dataset (AccountInfo) retrieving Account information.
We have a second dataset (AccountTotalSales) using a SUM aggregate grouped on AccountID pulling in Total Sales for each Account.
In our Table, we can now use the LookUp function in a Textbox Expression.
=LookUp(Fields!accountid.Value,Fields!accountid.Value,Fields!TotalSales.Value, "AccountTotalSales ")
The first field (Fields!accountid.Value ) in the expression is from the current dataset used by the table (Source Data).
The second field (Fields!accountid.Value ) is from the second dataset (Destination Data). The first field is looking for a match to this field.
The third field (Fields!TotalSales.Value ) is the value we are trying to return from the second dataset and place in the current table.
The last value in the expression is the Scope -- the name of the second Dataset ("AccountTotalSales ") we are trying to retrieve data from.
But wait, we are now getting an Error. Since LookUpSet brings back a collection, we cannot simply display it in a textbox. We need to concatenate the results. This can be accomplished using the Join function. =Join(LookUpSet(Fields!accountid.Value,Fields!accountid.Value,Fields!name.Value, "AccountOpportunities "),"," + vbcrlf)
We have returned all Opportunities associated to the Account, concatenating them with a comma and line break when multiple opportunity names are returned.
Microsoft Dynamics CRM 2011 Implementation and Integration Solutions
Microsoft Dynamics CRM for Wholesale Distribution
Microsoft Dynamics CRM for Prophet 21® ERP
Microsoft Dynamics CRM 2011 Product Information Page