Comparing rows from disparate tables in VB.NET and SSRS

I love using the word “disparate”, by the way.

I needed to compare two fields returned from a SQL query to determine which of the two fields occurred later. The fields were text, and were in the format “Consulting Services – 6/3/2013”. I had two different tables that this data was being returned from, Products and Services. (Yes, the names have been changed to protect the innocent.) And I needed to show this data in a SSRS report.

Here is the VB.NET function I used inside Reporting Services to compare the dates of these two fields. I have added plenty of comments, because everyone loves over-commented code:

Public Function GetLatestProductOrService(ByVal Product As String, ByVal Service As String) As String

        ‘ In SSRS, if the fields are empty, they are sent as Nothing.
If Product Is Nothing Then Product = String.Empty
If Service Is Nothing Then Service = String.Empty

        ‘ Get the space right before the date portion of the string
Dim ProductSpace As Integer = Product.LastIndexOf(” “)
Dim ServiceSpace As Integer = Service.LastIndexOf(” “)

       ‘ If neither products nor services were returned, then don’t display anything
If ProductSpace < 0 AndAlso ServiceSpace < 0 Then
Return String.Empty
End If

       ‘ If there is a value for the product, strip out the date from the string. If there was no service passed to the function,

       ‘no need to go further, so return the product
Dim ProductDate As String = String.Empty
If ProductSpace > -1 Then
ProductDate = Product.Substring(ProductSpace, Product.Length – ProductSpace)
If ServiceSpace < 0 Then
Return Product
End If
End If

       ‘ Like above, just for services
Dim ServiceDate As String = String.Empty
If ServiceSpace > -1 Then
ServiceDate = Service.Substring(ServiceSpace, Service.Length – ServiceSpace)
If ProductSpace < 0 Then
Return Service
End If
End If

Dim LastUpdateDate As String = String.Empty

       ‘ If both products and services were passed to the function, see which was processed last
If CDate(ProductDate) > CDate(ServiceDate) Then
LastUpdateDate = Product
Else
LastUpdateDate = Service
End If

Return LastUpdateDate

End Function

Calling this beautiful function through VB.NET code is simple:

Dim Result As String = GetLatestProductOrService(Nothing, “Widget – 06/17/2013”)

Calling it in Reporting Services is not much harder. I just added an expression to the TextBox where I wanted the text to appear:

=Code.GetLatestProductOrService(Fields!LastProduct.Value, Fields!LastService.Value)

 

Advertisements

,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: