Visual Report Writer and The Web (VIII)¶

In this eighth blog about Visual Report Writer and the Web, I want to take you to the next report available on the Live Demo website (European Server, USA server) named Sick Leave. If this is the first blog you are reading, I encourage you to read the previous seven blogs (1: The Solution, 2: Invoices Report, 3: The Cleanup, 4: The CustomerList, 5: The OrderList, 6: The Credit and Balances Overview, and 7: Inventory Stock Levels). Between the fourth and fifth blogs, we released the Alpha II version of Visual Report Writer 3.0 and the 2.1+ Library only setup. The latter is necessary for easy web reporting using the 17.1 DataFlex Web Framework, as demonstrated on the demo website.

The Report¶

The report utilizes the Microsoft Adventure Works 2000 database. You can download this database if you want to experiment with SQL without converting one of the DataFlex Embedded Databases. Note that there are multiple versions of the Adventure Works database, and we chose this (somewhat old) version to ensure compatibility with older versions of Microsoft SQL Server. We also selected this database for the reports because we wanted to create reports with images from the database, but the pictures available are too poor to display. For a picture report, we created reports using the Wines Example database, which I will discuss in a later blog. Additionally, we used a script available online to increase all dates in the database, making it appear as though the orders were placed more recently. This is important for this blog's report, as not all employees of the company are over 65 years old!

While the previous two blogs used the same report for two web views, this Sick Leave reporting defines two distinct reports about the company's employees.

The Sick Leave per Age report has its page header section duplicated - once with a column header box and once without. The sections are suppressed by a parameter named SuppressDetails. When suppress details are enabled, the report results are condensed into one page containing summary information; for each employee age range (in 10-year increments), the number of sick days is displayed. Another parameter can be set to indicate whether uneven row coloring for the detail section should be used.

The Sick Leave Overview report displays all employees along with their number of sick days. Depending on a parameter (there are five parameters that can be set), different page header and detail sections are printed. For example, if you select by department, the report will not print the department name in the details section. Other parameters that can be set include hiding/showing the Male/Female column, hiding/showing the Age column, and whether to use an alert color for employees whose birthdays are within a specified number of days before or after the current date.

Both reports use an image in the page header section, and the path to the image can be set via a parameter. In the upcoming (currently available as an Alpha version) of Visual Report Writer, this type of static image can be replaced with an embedded image. The live demo website operates on version 2.1, which requires a path setting for images or deployment of the report in the same folder as at design time (which is not advisable).

Integration¶

This blog discusses two report views: one for the Sick Leave Per Age report and another for the Sick Leave Overview report. Both views utilize different methods to set the selection criteria and parameters.

Sick Leave Per Age¶

In the report view, you can browse for a department record. By selecting a department, the data is filtered for that department, meaning the AddFilter message will be used. In the OnInitializeReport, this is accomplished as follows:

WebGet psValue of oDepartmentIDForm to iDepartmentID
If (iDepartmentID <> 0) Begin
    Send AddFilter C_USEMAINVRWREPORTID '{Employee.DepartmentID}' C_VRWEqual iDepartmentID
End

Additionally, a filter can be set for employee titles, which can be added or used separately. A dropdown (cWebCombo) allows you to select the title. The dropdown values are retrieved from the database using an embedded SQL statement that performs a distinct select to obtain that data. The code for this is:

Object oEmployeeTitle is a cWebCombo
    Set piColumnSpan to 7
    Set psLabel to "Title:"
    Set peLabelAlign to alignRight
    Procedure OnFill
        Handle hoSQL hoConnection hoStatement
        Integer iFetchResult
        String sTitle
        // Make it possible to NOT select a title
        Send AddComboItem '' ''
        Get Create (RefClass (cSQLHandleManager)) to hoSQL
        Get SQLFileConnect of hoSQL SQLEmployee.File_Number to hoConnection
        Get SQLOpen of hoConnection to hoStatement
        Send SQLExecDirect of hoStatement "Select Distinct [Employee].[Title] From [Employee]"
        Repeat
            Get SQLFetch of hoStatement to iFetchResult
            If (iFetchResult <> 0) Begin
                Get SQLColumnValue of hoStatement 1 to sTitle
                Send AddComboItem sTitle sTitle
            End
        Until (iFetchResult = 0)
        // Clean up
        Send SQLClose to hoStatement
        Send SQLDisconnect to hoConnection
        Send Destroy of hoSQL
    End_Procedure
End_Object

The filter for this column is conditionally added like the department ID.

WebGet psValue of oEmployeeTitle to sEmployeeTitle
If (sEmployeeTitle <> '') Begin
    Send AddFilter C_USEMAINVRWREPORTID '{Employee.Title}' C_VRWEqual sEmployeeTitle
End

Two checkboxes are used to set the SuppressDetails and UseUnevenRowColoring parameters.

Get GetChecked of oSuppressDetails to bShowDetails
Get ParameterIdByName C_USEMAINVRWREPORTID 'SuppressDetails' to iParameter
Set psParameterValue C_USEMAINVRWREPORTID iParameter to bShowDetails
Get GetChecked of oUseUnevenRowColoring to bUseUnEvenRowColoring
Get ParameterIdByName C_USEMAINVRWREPORTID 'UseUnevenRowColoring' to iParameter
Set psParameterValue C_USEMAINVRWREPORTID iParameter to bUseUnEvenRowColoring

The last option for a user in this report view is to select the report ordering. The user can choose between the ordering already defined in the report (the design-time ordering), sorting by employee last name, or sorting by Sick Leave Hours. Both can be combined with a choice for descending. The combo form is statically filled:

Object oSortOnCombo is a cWebCombo
    Set psLabel to "Sort on:"
    Set peLabelAlign to alignRight
    Set piColumnSpan to 3
    Set pbServerOnChange to True

    Procedure OnChange
        Integer iChoice

        WebGet psValue to iChoice
        WebSet pbEnabled of oSortDescending to (iChoice <> 0)
    End_Procedure

    Procedure OnFill
        Send AddComboItem '0' 'Report sorting'
        Send AddComboItem '1' 'Sort on LastName'
        Send AddComboItem '2' 'Sort on Sick Leave Hours'
    End_Procedure
End_Object

Object oSortDescending is a cWebCheckbox
    Set psCaption to 'Descending'
    Set piColumnIndex to 3
    Set pbEnabled to False
End_Object

The user's choice is read inside the OnInitializeReport method, and if it is not option 0 (sorting as defined at design time), the current report sort order will be removed and newly constructed with the following code:

WebGet psValue of oSortOnCombo to sSortOnValue
Case Begin
    Case (sSortOnValue = '1')
        Send RemoveAllRecordSortFields C_USEMAINVRWREPORTID
        Get GetChecked of oSortDescending to bSortDescending
        If (bSortDescending) Begin
            Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.Lastname}' C_VRWDescending
        End
        Else Begin
            Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.Lastname}' C_VRWAscending
        End
        Case Break
    Case (sSortOnValue = '2')
        Send RemoveAllRecordSortFields C_USEMAINVRWREPORTID
        Get GetChecked of oSortDescending to bSortDescending
        If (bSortDescending) Begin
            Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.SickLeaveHours}' C_VRWDescending
        End
        Else Begin
            Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.SickLeaveHours}' C_VRWAscending
        End
        Case Break
Case End

The output of this report is not sent to a cWebIFrame control as before, but to a specialized control for Visual Report Writer. This control - available from version 3.0 - can display the report results generated as HTML or as images. The class is called cWebVrwReportViewer, and most of the time, you only need to tell the object where to find the cVRWReport object.

Object oWebViewerPanel is a cWebPanel
    Set pbFillHeight to True
    Object oViewer is a cWebVrwReportViewer
        Set phoReport to oReport
    End_Object
End_Object

To start the report output generation, you send ShowReport to the oViewer report. If the cWebVrwReportViewer object is used to display HTML output, it sends a request to the cVRWReport object to construct the HTML string and return it. This request is named GenerateReportHTML and needs to return HTML in chunks. The chunks can be of any size as long as they are smaller than the Visual DataFlex argument size (see Set/Get_Argument_Size command in the Visual DataFlex help). Knowing this, the GenerateReportHTML is coded as follows:

Function GenerateReportHTML Returns String[]
    String sReportId
    String[] sData
    Integer iArgSize
    Get OpenReport to sReportId
    If (sReportId <> "") Begin
        Get_Argument_Size to iArgSize
        Get ComReportHTMLPreview sReportId (iArgSize - 10) to sData
        Send CloseReport sReportId
    End
    Function_Return sData
End_Function

Sick Leave Overview¶

The second report view in the live demo application allows you to select values via a ModalDialog. This approach makes the user interface smoother by hiding the controls for selections and parameters until the user wants to change or set them. However, it requires more work from the application developer to create. First, a modal dialog needs to be constructed, and more importantly, the selection criteria need to be made as synchronizable web properties.

The selections dialog is invoked from a cWebMenuItem, and each of the settings in the dialog is created as a property inside the cWebMenuItem object.

Object oSelectionsButton is a cWebMenuItem
    Set psCaption to "Selections"
    Set psCSSClass to "VRWSelectionsButton"
    { WebProperty = True DesignTime = False }
    Property Integer piSelectOn
    { WebProperty = True DesignTime = False }
    Property String psSelectionValueForDepartment
    { WebProperty = True DesignTime = False }
    Property String psSelectionValueForTitle
    { WebProperty = True DesignTime = False }
    Property String psMaritalStatus
    { WebProperty = True DesignTime = False }
    Property Integer piBirthdayRange
    { WebProperty = True DesignTime = False }
    Property String psHideAge
    { WebProperty = True DesignTime = False }
    Property String psHideMF
End_Object

The psCSSClass property allows specifying a different icon for the button based on the selected theme. Note that the live demo website does not allow theme changes, but the Visual Report Writer CSS settings do.

In the OnClick event of the oSelectionsButton object, the values from the above web properties are sent to the modal dialog and displayed. The code for that is:

Procedure OnClick
    tSickLeaveSelections SelectionValues
    WebGet piSelectOn to SelectionValues.iSelectOn
    WebGet psSelectionValueForDepartment to SelectionValues.sDeparmentSelectionValue
    WebGet psSelectionValueForTitle to SelectionValues.sTitleSelectionValue
    WebGet psMaritalStatus to SelectionValues.sMaritalStatus
    WebGet piBirthdayRange to SelectionValues.iBirthDayRange
    WebGet psHideAge to SelectionValues.sHideAge
    WebGet psHideMF to SelectionValues.sHideMF
    Send OpenSelectionsDialog of oSickLeaveOverviewSelectionsDialog Self SelectionValues
End_Procedure

The Modal Dialog (cWebModalDialog) takes the values of the passed struct and sets the values of the controls.

Procedure OpenSelectionsDialog Handle hoReturn tSickLeaveSelections SelectionValues
    WebSet psValue of oSelectOnCombo to SelectionValues.iSelectOn
    WebSet psValue of oSelectionValueForDepartment to SelectionValues.sDeparmentSelectionValue
    WebSet psValue of oSelectionValueForTitle to SelectionValues.sTitleSelectionValue
    WebSet psValue of oMaritalStatus to SelectionValues.sMaritalStatus
    WebSet piSliderValue of oEmployeeBirthDayRange to SelectionValues.iBirthDayRange
    WebSet psValue of oHideAge to SelectionValues.sHideAge
    WebSet psValue of oHideMF to SelectionValues.sHideMF

    Send OnChange of oSelectOnCombo SelectionValues.iSelectOn ''

    Send Popup hoReturn
End_Procedure

If you experiment with the selections dialog (I recommend doing this if you haven't), you will notice that the dialog contents change based on the chosen value in the first combo control. Depending on the current choice, a control for department or title selection is rendered.

Object oSelectOnCombo is a cWebCombo
    Set psLabel to "Select on:"
    Set peLabelAlign to alignRight
    Set piColumnSpan to 6
    Set pbServerOnChange to True

    Procedure OnFill
        Send AddComboItem '0' 'None'
        Send AddComboItem '1' 'Department'
        Send AddComboItem '2' 'Title'
    End_Procedure

    Procedure OnChange String sNewValue String sOldValue
        Case Begin
            Case (sNewValue = '0')
                WebSet pbRender of oSelectionValueForDepartment to False
                WebSet pbRender of oSelectionValueForTitle to False
                Case Break
            Case (sNewValue = '1')
                WebSet pbRender of oSelectionValueForDepartment to True
                WebSet pbRender of oSelectionValueForTitle to False
                Case Break
            Case (sNewValue = '2')
                WebSet pbRender of oSelectionValueForDepartment to False
                WebSet pbRender of oSelectionValueForTitle to True
                Case Break
        Case End
    End_Procedure
End_Object

The oSelectionValueForTitle cWebCombo is coded identically to the one used for the Sick Leave Per Age report integration. The values are retrieved via an embedded SQL statement.

In contrast, the department selection value in the Sick Leave Per Age report integration is managed via a standard cWebForm object, while we use a cWebCombo in this modal dialog. This approach avoids calling a modal dialog from another modal dialog and demonstrates the effective use of embedded SQL in a web application.

Object oSelectionValueForDepartment is a cWebCombo
    Set psLabel to "Department:"
    Set peLabelAlign to alignRight
    Set piColumnSpan to 10

    Procedure OnFill
        Handle hoSQL hoConnection hoStatement
        Integer iFetchResult
        String sId sName

        Get Create (RefClass (cSQLHandleManager)) to hoSQL
        Get SQLFileConnect of hoSQL SQLDepartment.File_Number to hoConnection
        Get SQLOpen of hoConnection to hoStatement
        Send SQLExecDirect of hoStatement "Select [Department].[DepartmentId], [Department].[Name] From [Department]"
        Repeat
            Get SQLFetch of hoStatement to iFetchResult
            If (iFetchResult <> 0) Begin
                Get SQLColumnValue of hoStatement 1 to sId
                Get SQLColumnValue of hoStatement 2 to sName
                Send AddComboItem sId sName
            End
        Until (iFetchResult = 0)
        // Clean up
        Send SQLClose to hoStatement
        Send SQLDisconnect to hoConnection
        Send Destroy of hoSQL
    End_Procedure
End_Object

The above code is similar to that used to fill the cWebCombo for title selection, except that there is no DISTINCT clause in use, and the code and display values differ.

For the oMaritalStatus control, we again use a cWebCombo object that fills itself via an embedded SQL statement using a DISTINCT operation.

Send SQLExecDirect of hoStatement "Select Distinct [Employee].[MaritalStatus] From [Employee]"

While it could have been hard-coded, this approach allows for flexibility should the values change (which they likely won't), eliminating the need for code changes.

The two checkboxes for hiding Age and M/F data are simple cWebCheckbox objects with hard-coded settings for true and false.

Object oHideAge is a cWebCheckbox
    Set psLabel to "Hide Age:"
    Set peLabelAlign to alignRight
    Set psChecked to '1'
    Set psUnchecked to '0'
    Set piColumnSpan to 2
    Set pbShowLabel to True
End_Object

Object oHideMF is a cWebCheckbox
    Set psLabel to "Hide M/F:"
    Set peLabelAlign to alignRight
    Set psChecked to '1'
    Set psUnchecked to '0'
    Set piColumnSpan to 2
    Set pbShowLabel to True
End_Object

The last control in the modal dialog is a cWebSlider object, allowing the user to select a value between 0 and 31 days for an upcoming birthday alert.

When the user clicks one of the two buttons in the modal dialog, the control is passed back to the cWebMenuItem, sending an OnCloseModalDialog event. In this event, the currently selected values are retrieved and stored back in the synchronizable web properties defined above. It felt appropriate to directly generate the report after the modal dialog is closed by clicking the OK button.

Procedure OnCloseModalDialog Handle hoModalDialog
    tSickLeaveSelections SelectionValues
    Boolean bCancel

    Get SickLeaveSelectedValues of hoModalDialog (&SelectionValues) to bCancel
    If (not (bCancel)) Begin
        WebSet piSelectOn to SelectionValues.iSelectOn
        WebSet psSelectionValueForDepartment to SelectionValues.sDeparmentSelectionValue
        WebSet psSelectionValueForTitle to SelectionValues.sTitleSelectionValue
        WebSet psMaritalStatus to SelectionValues.sMaritalStatus
        WebSet piBirthdayRange to SelectionValues.iBirthDayRange
        WebSet psHideAge to SelectionValues.sHideAge
        WebSet psHideMF to SelectionValues.sHideMF
        Send ShowReport of oViewer
    End
End_Procedure

The selected values are retrieved by the cWebReport object in the OnInitializeReport event.

Procedure OnInitializeReport
    String sReportLocation sSelectValue
    Integer iParameter iSelectOn

    Get psReportLocation to sReportLocation

    Get ParameterIdByName C_USEMAINVRWREPORTID 'ImagePath' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to sReportLocation

    Send RemoveAllFilters C_USEMAINVRWREPORTID

    WebGet piSelectOn of oSelectionsButton to iSelectOn
    Get ParameterIdByName C_USEMAINVRWREPORTID 'SelectOn' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to iSelectOn
    Case Begin
        Case (iSelectOn = 0)
            Case Break
        Case (iSelectOn = 1)
            WebGet psSelectionValueForDepartment of oSelectionsButton to sSelectValue
            Send AddFilter C_USEMAINVRWREPORTID '{Employee.DepartmentID}' C_VRWEqual sSelectValue
            Case Break
        Case (iSelectOn = 2)
            WebGet psSelectionValueForTitle of oSelectionsButton to sSelectValue
            Send AddFilter C_USEMAINVRWREPORTID '{Employee.Title}' C_VRWEqual sSelectValue
            Case Break
    Case End

    WebGet psMaritalStatus of oSelectionsButton to sSelectValue
    If (sSelectValue <> '') Begin
        Send AddFilter C_USEMAINVRWREPORTID '{Employee.MaritalStatus}' C_VRWEqual sSelectValue
    End

    WebGet piBirthdayRange of oSelectionsButton to sSelectValue
    Get ParameterIdByName C_USEMAINVRWREPORTID 'BirthdayRange' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to sSelectValue

    WebGet psHideAge of oSelectionsButton to sSelectValue
    Get ParameterIdByName C_USEMAINVRWREPORTID 'HideAge' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to sSelectValue

    WebGet psHideMF of oSelectionsButton to sSelectValue
    Get ParameterIdByName C_USEMAINVRWREPORTID 'HideMF' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to sSelectValue
End_Procedure

The viewer object has one more feature available for us: the ability to respond to a hyperlink click. This event is called OnClickActionLink, which retrieves the value from the HTML anchor element. With this value, we can open a dialog to show (or even modify) the data of the selected row, making Visual Report Writer truly interactive.

Object oViewer is a cWebVrwReportViewer
    Set phoReport to oReport
    Set pbServerOnClickActionLink to True

    Procedure OnClickActionLink String sText
        Send ShowEmployee of oSQLEmployeeModalDialog Self sText
    End_Procedure
End_Object

This feature will not be available until the release of version 3.0 enterprise. I will discuss the coding in a later blog.

This concludes blog number eight about Visual Report Writer web integration. I hope you enjoyed reading this, feel inspired to get started yourself, and look forward to future blogs that will follow soon.