Creating a Power BI Visualization from SQL Server Data

In this video, we create a Power BI visualization using a query from Microsoft SQL Server. Specifically, we join several tables from the Tracker.Net Learning Management System to visualize student and lesson information. We cover slicers, cards, pie charts, tables, and the Text Filter control. We also examine how to customize the drill-down interactions with the pie charts.

Subscribe to My Channel

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

Advertisement

Making a jQueryUI Button

We now use the nice jQueryUI library in Tracker.Net, Exam Engine, and Training Studio. In each case, you link in the correct JavaScript files and style sheet and then use JavaScript to create the button. The nice thing is that you can use various types of HTML objects (input, anchor, ASP.NET buttons, etc.) and they still end up looking like a button. Here is an example from Tracker.Net:

$(function () {
        var optionsBtnId = $("#optionsBtn");

        if (optionsBtnId.length) {
            optionsBtnId.button({
                icons: {
                    primary: "ui-icon-home"
                },
                text: false
            });
            optionsBtnId.click(function () {
                var returnVal = true;

                if (isOnCoursePage == true) {
                    if (isOneCourse == true) {
                        window.resizeTo(800, 600);
                    }
                    else {
                        returnVal = checkIfOkToLeave(LessonOpenMessageLeavingScreen);
                    }
                }

                return returnVal;
            });
        }
    });

The $() means that the jQuery will call the function after the page fully loads. We then get our hands on the HTML object that we want to use for our button. The #optionsBtn means that we are looking for an object with an id of “optionsBtn.” We then call the jQueryUI button method to create the button. In this case, we tell it to use the jQueryUI home icon and not to have any text. Next, we define the click function. Here we are doing some logic to determine whether to proceed with the click. If returnVal is false, the server side event will not be processed and we stay on the page.

Playing a Video in the HTML 5 Era

I was setting up a Tracker.Net demonstration today and the customer sent me a .wmv file to be used as one of the lessons. Although a video like this would normally be added to a lesson created by an authoring tool, Tracker.Net also allows any file or HTML link to be an asset, meaning that the user can launch it. It is then automatically marked as complete since there is no SCORM communication.

While it is possible to launch a .wmv file directly, I prefer not to do that since then you are relying on the user’s association with that extension. The result can be Windows Media Player or any of a myriad of players. These can take quite a while to spin up and can make your training seem unresponsive. Instead, I like to embed the video in a simple HTML page. In the past, I would have just pasted in the tags for a Windows Media Player, but with the advent of HTML 5, it is better to play the video natively in the browser. This is faster and a better cross-platform approach, particularly with mobile devices. And older browsers will default to the Windows Media Player anyway. Here’s how to do.

1. Convert the .wmv files to the other video formats: mp4 (Internet Explorer and Safari), ogg/ogv (Firefox and Opera), webm (Firefox, Chrome, and Opera). You can see a list of what browsers support what formats at http://en.wikipedia.org/wiki/HTML5_video. There are numerous programs and sites for doing the conversion. I’ve found the Freemake Video Converter and Freemake Audio Converter (to do the same thing with audio files) work well.
2. Create your HTML page with the HTML5 video tags first but with the Windows Media Player ActiveX control tags (Internet Explorer) and embed tags (Firefox and other browsers) as shown.

<!doctype html> 
     <html>  
          <head>   
               <title>Measuring Voltage</title>  
          </head>  
          <body>   
               <form>    
                    <video controls="controls" autoplay="autoplay">     
                         <source src="Measuring_Voltage.webm" type='video/webm; codecs="vp8.0, vorbis"'/>     
                         <source src="Measuring_Voltage.ogv" type='video/ogg; codecs="theora, vorbis"'/>     
                         <source src="Measuring_Voltage.mp4" type='video/mp4; codecs="avc1.4D401E, mp4a.40.2"'/>     
                         WordPress won't display the windows media tags correctly. Click here to see a graphic of the tags.
                    </video>   
               </form>  
          </body> 
</html>

Newer browsers will play the video format that they can support. Older browsers will revert to the Windows Media Player.

I hope this is helpful.

SCORM and Closing the Browser Window: Silverlight

As discussed in the previous post on ActionScript, we needed to change our logic somewhat to support showing our Exam Engine and Training Studio content in a Tracker.Net 5 (or other LMS) frame. Since Exam Engine has both a Flash/Flex implementation and a Silverlight implementation (note: version 4 is now HTML/JavaScript), I thought I would show the Silverlight implementation in this article. The logic is the same as with the previous article. We want to initiate the SCORM messages when the user clicks the Exit button and then be sure not to resend them when the window closes. In the popup window situation, however, the SCORM messages need to be sent when the user closes the browser window without clicking the Exit button.

The Exit button code is shown below.

Private Sub ExitBtn_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
    Dim examRefId As ExamEngineSettings = ExamReferenceId
    Dim exitMessage As String = examRefId.ReadExamSetting("ExitMessage", "")
    Dim okToExit As Boolean = True

    If exitMessage <> "" Then
        okToExit = Browser.HtmlPage.Window.Confirm(exitMessage)
    End If
    If okToExit = True Then
        ' Changed the following since this did not work if the content is in an iFrame.
        ' Instead, call ExitExam directly and set a variable to skip the exit on the
        ' subsequent closing of the browser window
        Dim exitSuccess As Boolean = examRefId.ExitExam()

        If exitSuccess = True Then
            examRefId.AlreadyExited = True
            Browser.HtmlPage.Window.Eval("window.close()")
        End If
    End If
End Sub

We again read our exit message and display that to the user via JavaScript. Notice how we can call the JavaScript confirm method directly from the Visual Basic code. If the user confirms exiting, we call the ExitExam method. This is where all the SCORM messages are sent. If that returns True, we set the AlreadyExited global variable to True and try to close the window.

As with the Flash/Flex implementation we registered the “onunload” browser event. In this case, we call ExitExamHandler as shown below. Notice that we check the same AlreadyExited variable before calling ExitExam. This avoids it being called twice if the user clicked the Exit button to close the window.

Private Sub Application_Startup(ByVal o As Object, ByVal e As StartupEventArgs) Handles Me.Startup
    Me.RootVisual = New Page()
    HtmlPage.Window.AttachEvent("onunload", AddressOf ExitExamHandler)
End Sub

Public Sub ExitExamHandler(ByVal sender As Object, ByVal e As EventArgs)
    Dim examRefId As ExamEngineSettings = ExamReferenceId

    If examRefId.AlreadyExited = False Then
        ExamReferenceId.ExitExam()
        examRefId.AlreadyExited = True
    End If
End Sub

SCORM and Closing the Browser Window: Flash/Flex

One of the new features of Tracker.Net 5 is the ability to show content in a frame instead of a popup window. One thing we noticed in testing our Exam Engine and Training Studio content is that we were assuming a popup window in that the Exit button closed the browser window and then that triggered the SCORM calls. With a frame, we need to send the SCORM calls first since the LMS will then close the frame. Since deployment via a popup window still requires sending SCORM messages when the user closes the browser window, we needed a global variable to distinguish the two situations.

The “Exit Button” code for Training Studio is shown below. We’ll leave the Exam Engine implementation for the next post since that has a Silverlight equivalent as well.

public function ImplementExitClick(e:MouseEvent):void {
    var exitMessage:String = ReadStructureSetting("ExitBtn_Message", "");
    var returnString:String;
    var okToExit:Boolean = true;

    if (exitMessage != "") {
        returnString = ExternalInterface.call("confirmClose", exitMessage);
        okToExit = ConvertToBoolean(returnString);
    }
    if (okToExit == true) {
        var exitSuccess:Boolean = ExitTraining();

        if (exitSuccess == true) {
            this.AlreadyExited = true;
            ExternalInterface.call("closeWindow");
        }
    }
}

We read our message (“Are you sure you want to exit?”) and display that to the user via JavaScript. If the user confirms, we call the ExitTraining method. This is where all the SCORM messages are sent. Assuming all went well, we set the AlreadyExited global variable to true and attempt to close the window (this fails silently if you are in a frame).

When we started the application, we registered the “onunload” browser event with the cleanup method as shown below. Notice that we check the same AlreadyExited variable before calling ExitTraining. This avoids it being called twice if the user clicks the Exit button to close the window.

// Added for exit handling
ExternalInterface.addCallback("exitTraining", cleanUp);

public function cleanUp():void {
    if (this.AlreadyExited == false) {
        ExitTraining();
        this.AlreadyExited = true;
    }
}

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.