Development Team Blog

In this eighth blog about Visual Report Writer and the Web I want to take you to the next report that can be found at the Live Demo website (European Server, USA server) named Sick Leave. If this is the first blog you read I encourage you to read the seven other 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 the fifth blog we have been able to release the Alpha II version of Visual Report Writer 3.0 and the 2.1+ Library only setup. The latter one is needed to make the web reporting using the 17.1 DataFlex Web Framework easy and look like at the demo website.

The Report
The report makes use of the Microsoft Adventure Works 2000 database. You can download this database if you want to play with SQL and don't want to convert one of the DataFlex Embedded Databases. Note there are more versions of the Adventure Works database and we choose for this (quite old) database so that it can run on older versions of Microsoft SQL Server. We also selected this database for the reports as we wanted to make reports with images coming from the database but the pictures in there are too poor to show to you. For a picture report we made reports on the Wines Example database and I will discuss the reports using this database in a later blog. Also note that we used a script that you can download from internet to increase all dates in the database so that it looks like the orders were placed more recently and important for the report of this blog that employees of the company are not all above 65 years old!

Where the previous two blogs used the same report for two web views this Sick Leave reporting defines two different reports about employees of the company.

The Sick Leave per age report has its page header section duplicated, once with a column header box and once without and the sections are suppressed by a parameter named SuppressDetails. With suppress details turned on the report results are one page which contains summary information; per employee age range (ranges per 10 year periods) a number of sick days. Another parameter can be set to indicate that uneven row coloring for the detail section should be used.

The Sick Leave Overview report shows all employees with their number of sick days. Depending on a parameter (there are 5 parameters that can be set) different page header and details sections are printed. For example; you can select on department and if so the report will not print the department name in the details section. Other parameters that can be set are to hide/show the Male/Female column, hide/show the Age column and whether you want an alert color for employees that have their birthday in a number of days prior or past the current date.

Both reports use a image in the page header section and the path to the image can be set via a parameter. In the upcoming (in fact already available as Alpha version) of Visual Report Writer this kind of static image can be changed into an embedded image. The live demo website runs on version 2.1 which requires a path setting for images or deployment of the report in the same folder as at design time (not a good idea).

Integration
There are two report views to discuss in this blog. One report view for the Sick Leave Per Age report and one for the Sick Leave Overview report. Both views use a different way 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 this department meaning the AddFilter message will be used. In the OnInitializeReport this is done as follows:

Code:

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

In addition - can be added or used separately - a filter can be set to employees title. A drop down (cWebCombo) is used to let you select the title. The drop down values are read from the database using an embedded SQL statement where a distinct select is used to get that data. The code for that is:

Code:

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 stuff
        Send SQLClose to hoStatement
        Send SQLDisconnect to hoConnection
        Send Destroy of hoSQL
    End_Procedure
End_Object

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

Code:

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.

Code:

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 reportview is to select the report ordering. The user has the choice between the ordering already defined in the report (the design time ordering), Sorting on the employee last name or Sick Leave Hours. Both can be combined with a choice for descending. The combo form is statically filled:

Code:

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:

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 send to a cWebIFrame control as before but to a specialized control for Visual Report Writer. This control - that will be 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 tell the object where to find the cVRWReport object.

Code:

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 send a request to the cVRWReport object to construct the HTML string and return it back. This request is named GenerateReportHTML and needs to return HTML in chunks. The chunks can be in 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:

Code:

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 let you select values via ModalDialog. It makes the user interface more smooth by hiding the controls for selections and parameters until the user wants to change/set them but for the application developer it is more work to create. First a modal dialog needs to be constructed but more important the selection criteria need to made as synchronizable web properties.

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

Code:

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 makes it possible to specify a different icon to the button based on the selected theme. Note that the live demo website does not allow you to change themes but the Visual Report Writer CSS settings make it possible.

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

Code:

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.

Code:

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 'play' a bit with the selections dialog (I advise you to do this now if you didn't) you will see that dialog contents change based on the chosen value in the first combo control. Based on the current choice a control for department or title selection is rendered.

Code:

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 in code identical as the one used for the Sick Leave Per Age report integration. The values are retrieved via an embedded SQL statement.
Where the department selection value in the Sick Leave Per Age report integration is managed via a standard cWebForm object we use a cWebCombo in this modal dialog. We did this to avoid a modal dialog to be called from a modal dialog and to show how well you can use embedded SQL in a web application.

Code:

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 stuff
        Send SQLClose to hoStatement
        Send SQLDisconnect to hoConnection
        Send Destroy of hoSQL
    End_Procedure
End_Object

The above is similar to the code used to fill the cWebCombo for title selection with the exception there is no DISTINCT in use and the code and display values are different.
For the oMaritalStatus control we again use a cWebCombo object that fills itself via an embedded SQL statement using a DISTINCT operation

Code:

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

It could have been hard coded but it the values change (they probably don't) no code change is required.
The two checkboxes for Hide Age and Hide M/F data are simple cWebCheckbox objects with hard coded settings for true and false

Code:

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 a 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 a OnCloseModalDialog event. In this event the current selected values are retrieved and stored back in the synchronizable web properties defined above. It felt right to directly generate the report after the modal dialog was closed by clicking the OK button as well.

Code:

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.

Code:

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 unexplained feature available for us. This is the possiblity to respond on a hyperlink click. This event is called OnClickActionLink and it get the value between of 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 really interactive.

Code:

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

Above feature is not 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. Hope you enjoyed reading this, are getting inspired to get started yourself and are looking forward to future blogs that will follow soon.