Visualizing a SharePoint Survey with Power BI

In this video, we explain the advantages of using Power BI to visualize SharePoint survey data and then create a powerful visualization that shows responses in the last week, month, and year.

Subscribe to My Channel

My Book: Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More

Advertisement

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.