Reporting Services Lookup Functions
One new SSRS SQL Server 2008 R2 feature that caught my attention is lookup functions. When reading the Reporting Services discussion list, I come frequently across questions that ask if it is possible to look up a value from a dataset other than the dataset the region is bound to. Excluding getting the first or last value (First and Last functions) or aggregate functions that work on the entire dataset, the only solution was to cache the dataset in a report code-behind code and use custom code to perform the navigation.
R2 will change this by introducing Lookup, LookupSet, and Multilookup functions. The lookup functions are best understood with an example. Take a look at this report (attached) which is based on the Sales Order Detail 2008 report that ships with the Adventure Works sample reports.
I added a couple of textboxes to the report. The order details section includes a Sales Person field that displays the sales person’s name. What’s interesting is that the sales person name is retrieved from the SalesOrder dataset that feeds the order header section. Just to address the question that I can almost hear you asking, no, R2 will not support joining datasets at a report level. Instead, I use the Lookup function which performs on-the-fly search, as follows:
=Lookup(Fields!SalesOrderID.Value, Fields!SalesOrderID.Value, Fields!SalesPerson.Value, “SalesOrder”)
I pass the field of the SalesOrderDetail dataset (Fields!SalesOrderID.Value) on which the lookup will be based. I pass the field of the SalesOrder dataset that will be matched. As a third argument, I pass the field (Fields!SalesPerson.Value) of the SalesOrder dataset that I need. Finally, I pass the name of the dataset (“SalesOrder”) that will be searched. The Lookup function is executed for each row in the source dataset.
In the order header section, I display the third item total from the SalesOrderDetails dataset by using the LookupSet function, as follows:
=Code.GetValueByRowNumber(LookupSet(Fields!SalesOrderID.Value, Fields!SalesOrderID.Value, Fields!LineTotal.Value, “SalesOrderDetail”), 2)
You call LookupSet in the same way you call Lookup. The difference is that Lookupset returns a variant array of all values that match. I decided to make the demo more interesting and let you specify the row index needed by adding a simple function to the report code.
Function GetValueByRowNumber(ByVal items As Object(), ByVal pos as Integer) As Object
If items Is Nothing Then
Return Nothing
End if
If pos > items.Length -1 Or pos<0
Return Nothing
End If
Return items(pos)
End Function
The expression passes the variant array returned by LookupSet and the row index to the GetValueByRowNumber. The GetValueByRowNumber returns the field at that row.
The Multilookup function, which I don’t demonstrate, accepts an array of source values to match and returns an array of the matched values that have 1:1 relationship to the source values, such as:
=MultiLookup(Split(Fields!CategoryList.Value,”,”) , Fields!CategoryID.Value, Fields!CategoryName.Value,”Category”))
[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/7532.Sales Order Detail 2008.rdl]
