Visual Basic in Reports

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.

Fixing SQL Divide By Zero Error

We had an issue yesterday with reports failing in Tracker.Net yesterday. The exception was “Divide by Zero Error Encountered.” I first thought it might be an error in a report itself. But searching for the error pointed to the SQL Server query instead. This Stack Overflow article describes the problem pretty well. The problem with my reports was that I was calculating the “percent score” on a test. I was accounting for a null “maximum score” but not for a zero maximum score. I haven’t checked the SCORM specification to see if a zero value is even valid, but it doesn’t really matter as at least one customer has data like that. Here is the original section of a query:

Tracker_SessionLessonStudentLog.LessonTestScore / ISNULL(Tracker_SessionLessonStudentLog.LessonMaxScore, 100) AS SessionPercentScore

The ISNULL() function checks if LessonMaxScore is null and, if so, substitutes 100. But it doesn’t address the situation where LessonMaxScore has an actual value of 0. That’s what caused the divide by zero error. As suggested in the article referenced above, I added the NULLIF() function as shown below.

Tracker_SessionLessonStudentLog.LessonTestScore / NULLIF(ISNULL(Tracker_SessionLessonStudentLog.LessonMaxScore, 100), 0) AS SessionPercentScore

So if the inside expression is 0, the whole divisor becomes null and the SessionPercentScore becomes null.