Archive for year: 2009

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]

It turned out that for time being the CTP2 of SQL Server 2008 R2 which I announced yesterday is available for TAP participants only. It will be publicly available on Wednesday. Meanwhile, Robert Bruckner listed the new Reporting Services features which got implemented in CTP2. The biggy is the new map region that lets you visualize geospatial and geometry data.

In my first book I had a sample that demonstrated how to export a report as a data feed. Well, in R2 this will be natively supported. Reports as data feeds, anyone? Visual Studio 2010 Beta 2 will include an AJAX Report Viewer. I would hope for a Silverlight version but one step at a time.

Again, post your KJ-related question to the Reporting Services forum.