Visual Basic in Reports
May 3, 2012 Leave a comment
I’ve been spending a great deal of time building reports in Visual Studio for our new Tracker Reports product. While SQL Server Reporting Services has a number of great features, one of my favorites is the fact that you can embed Visual Basic code in the report itself. This can be extremely helpful with complex logic. For example, the completion status of a lesson or course can be expired, critical, or alert when it has an expiration date. Here is what the function looks like:
Public Function DetermineCompletionStatus(ByVal completionStatusObj As Object, _ ByVal expirationDateObj As Object, ByVal currentDays_CriticalObj As Object, _ ByVal currentDays_AlertObj As Object, Optional ByVal isCourse as Boolean = True) As String Dim completionStatus As String Dim expirationDateId, currencyCriticalDate, currencyAlertDate As DateTime Dim currencyCriticalDays, currencyAlertDays As Integer Dim checkExpiration As Boolean = isCourse Dim defaultStatus as String = "Not Started" If isCourse = False Then defaultStatus = "not attempted" End If If IsDBNull(completionStatusObj) = True Then completionStatus = defaultStatus Else completionStatus = CStr(completionStatusObj) If completionStatus = "" completionStatus = defaultStatus End If End If Dim returnString As String = completionStatus If IsDBNull(expirationDateObj) = True OrElse expirationDateObj Is String.Empty OrElse _ IsNothing(expirationDateObj) = True OrElse expirationDateObj.ToString = "" Then expirationDateId = DateTime.MinValue Else Try expirationDateId = Convert.ToDateTime(expirationDateObj) Catch ex As Exception expirationDateId = DateTime.MinValue End Try If expirationDateId > DateTime.MinValue Then If IsDBNull(currentDays_CriticalObj) = True OrElse IsNothing(currentDays_CriticalObj) = True OrElse _ currentDays_CriticalObj.ToString = "" OrElse currentDays_CriticalObj.ToString = "-10" Then currencyCriticalDays = 0 currencyCriticalDate = expirationDateId Else currencyCriticalDays = CInt(currentDays_CriticalObj) currencyCriticalDate = expirationDateId.AddDays(-(currencyCriticalDays)) End If If IsDBNull(currentDays_AlertObj) = True OrElse IsNothing(currentDays_AlertObj) = True OrElse _ currentDays_AlertObj.ToString = "" OrElse currentDays_AlertObj.ToString = "0" OrElse _ currentDays_AlertObj.ToString = "-10" Then currencyAlertDays = 0 currencyAlertDate = expirationDateId Else currencyAlertDays = CInt(currentDays_AlertObj) currencyAlertDate = expirationDateId.AddDays(-(currencyAlertDays)) End If End If End If Select Case completionStatus.ToLower Case "completed", "passed" ' expiration Date checkExpiration = True End Select If checkExpiration = True Then If expirationDateId <> DateTime.MinValue Then If (expirationDateId <= Now) Then Dim expiredString As String = "Expired" returnString = expiredString ElseIf (currencyCriticalDate <= Now) Then returnString = "Critical" ElseIf (currencyAlertDate <= Now) Then returnString = "Alert" End If End If End If returnString = StrConv(returnString, VbStrConv.ProperCase) Return returnString End Function
Within the report itself, we can use an expression for the value for the “status” rather than linking to just a single column from our query. For example, here is an expression for the course status:
=Code.DetermineCompletionStatus(Fields!CompletionStatus.Value, Fields!ExpirationDate.Value, _ Fields!CurrencyDaysFlag_Critical.Value, Fields!CurrencyDaysFlag_Alert.Value)
Notice how we begin the expression with Code to denote that we are using our own code rather than built-in operators.