Skip to content

Visual Report Writer and The Web (IV)

In this fourth blog, the Customer List is the topic. Ehh, a customer list, isn't that too simple for a demo and a blog? Yes and no. A customer list running on the Web Order Customer table is, in fact, a simple report, but as you discover below, we have added a couple of features to make the report and integration more attractive. To see the report discussed here in action, you should navigate to the live demo website (European Server, USA server). You are also advised to read the three previous blogs (1: The Solution, 2: Invoices Report, 3: The Cleanup).

The Report

The report is named Simple Customer List but contains a couple of enhancements to make it more attractive. The report contains two functions. One function is named PageNumber, which simply returns the text "Page:" combined with the special field {&Page Number}. Besides the fact that you can easily combine static text and a variable, it can be seen as simple. The second function is named StateName, which shows the usage of ELFs. ELF is the abbreviation of Extended Library Function. With Visual Report Writer, you can make use of functions written in a DLL (Dynamic Link Library) to perform special operations that are not in the product itself. The functions should return a value, and in this case, it returns the name of the customer state by finding a row in the codemast table of the workspace. This ELF is named CodeDescription and is delivered with the product in a DLL named CodeMast.Dll. The function code itself is:

return CodeDescription({?CodeMastPath},"STATES",{Customer.State})

The first parameter of this function call is a path to the codemast table. The codemast table may be converted to SQL, and then the value of the report parameter named CodeMastPath should point to an INT file. In this demo website, the table is in the embedded DataFlex database format and thus points to a codemast.dat file on disk. With the usage of the parameter field, it is possible to control the location of the table at the integration level in a similar way as pbAutoLocateDFFiles operates.

Last but not least, a report should look nice and be attractive. In this report, we added a blue bar behind the column captions, and the bar has rounding in its corners. For the upcoming revision 3.0, we can add gradients to such an object to make it even more attractive.

The Integration

At the report integration level, we decided to add a couple of features available in and through the DataFlex 17.1 Web Application Framework.

First, the selection criteria and the results are separated from each other to make more space for both of them and to show that it can be done. The selection criteria can be set on the first cWebTabPage object, and the results are shown in an iFrame control on the second tab-page. The selections tab-page is divided into two panels to keep the controls reasonable in width on wider devices. The width of an input control enlarges if the width of the view enlarges and the piWidth of the cWebView object is not set to a specific width.

The first control on the tab-page is a cWebCombo control, which is data-aware and automatically pulls the states from the codemast table via the DD validation table connection. The only change made in regards to this control is the cancellation of the field_defaults setting in the customer data dictionary object. Normally, the state Florida is specified as a default state, and to avoid the report by default only showing the customers of one state, this is blanked out. You can still - and it is the purpose of having the cWebCombo - select a state to limit the data found and presented. In the OnInitializeReport event of the cVRWReport object, this is covered as:

WebGet psValue of oCustomerStateForm to sCustomerState
If (sCustomerState <> "") Begin
    Send AddFilter C_USEMAINVRWREPORTID "{Customer.State}" C_VRWEqual sCustomerState
End

The second control on the tab-page is a cWebForm control, not data-aware and "just" an input field. If the user enters a value here, the rows from the table are filtered on including this value in the name column. In coding, this is done as follows:

WebGet psValue of oNameContains to sNameContains
If (sNameContains <> "") Begin
    Get GetChecked of oContainsCaseInSensitive to bCaseInSensitive
    Move (SFormat ("return Instr({Customer.Name},'%1',1,%2)", sNameContains, bCaseInSensitive)) to sFilterFunction
    Set psFilterFunction C_USEMAINVRWREPORTID to sFilterFunction
End

Usage of functions inside a psFilterFunction adds power to the system BUT slows down the selection. Visual Report Writer currently (still on the wishlist) does not attempt to offload selection criteria if a function is used inside a psFilterFunction instruction. Offload means adding the selection criteria to the WHERE clause in an ODBC database report and jumping into index in a DataFlex embedded database data-source report. Note that selection criteria in a psFilterFunction where no functions are used can be offloaded to speed up the selection process. For an ODBC-based report, you can check if the selection criteria are offloaded by viewing the SQL statement created, available from the report designer tool as well as from integration level (ReportQuery function of cVRWReport).

The next control is again a cWebCombo control, but not data-aware this time, and its values are hard-coded in the OnFill event of the control. The control allows you to limit the number of found customers by looking at the customer.status column. The following code is used to pass the user's choice to the report selection criteria.

WebGet psValue of oCustomerStatus to sStatus
If (sStatus <> '-') Begin
    Send AddFilter C_USEMAINVRWREPORTID "{Customer.Status}" C_VRWEqual sStatus
End

The next control is a horizontal line to serve both the purpose of creating some distance between the combo and the sliders (the next big thing) and having "just" a separator line. The object is instantiated from the cWebHorizontalLine class.

Object oSpacerLine is a cWebHorizontalLineEnd_Object

The next three controls are sliders (cWebSlider). The user can set the selection range for balance, purchase, or credit limits to an upper and lower boundary value. The checkbox on the right-hand side of the slider needs to be used to make the value active. This is done because the slider's ranges are set to a smaller range than the full set, just to show it can be done.

How is the code behind the slider controls? First, the object itself:

Object oPurchasesSlider is a cWebSlider
    Set piColumnSpan to 6
    Set psLabel to "Purchases Range:"
    Set peLabelAlign to alignRight
    Set pbShowRange to True
    Set pbShowValue to True
    Set pbRanged to True
End_Object

You can create this by drag-and-drop from the class palette and setting properties such as pbShowRange, pbShowValue, and pbRanged.

The OnLoad event is used to find the current highest and lowest values for the columns balance, credit limit, and purchases. Because the credit limit and purchase columns are not part of an index, we added two indexes to the customer data file to make the process of finding the highest and lowest value easier and faster. The OnLoad event of one of the sliders is:

Procedure OnLoad
    Integer iMinValue iMaxValue iFrom iTo

    Forward Send OnLoad

    Clear Customer
    Find Gt Customer by 3
    Move Customer.Balance to iMinValue

    Clear Customer
    Find Lt Customer by 3

    Move Customer.Balance to iMaxValue
    Move (iMinValue + (iMaxValue - iMinValue / 10)) to iFrom
    Move (iMaxValue - (iMaxValue - iMinValue / 10)) to iTo

    Set piMinValue to iMinValue
    Set piFrom to iFrom
    Set piMaxValue to iMaxValue
    Set piTo to iTo
End_Procedure

The code above sets the minimum and maximum values of the sliders (piMinValue and piMaxValue) but also sets a "current" range which is 10% of the maximum minus the minimum value plus the minimum or the maximum, moving the range a little off the upper and lower values.

The slider control can show labels spread over the slider, and this is done in the event OnFillLabels.

Procedure OnFillLabels
    Integer iMinValue iMaxValue iLow iMedium iHigh

    Get piMinValue to iMinValue
    Get piMaxValue to iMaxValue
    Move (iMaxValue - iMinValue * 0.05) to iLow
    Move (iMaxValue - iMinValue * 0.50) to iMedium
    Move (iMaxValue - iMinValue * 0.95) to iHigh
    Send AddLabel iLow "Low"
    Send AddLabel iMedium "Medium"
    Send AddLabel iHigh "High"
End_Procedure

Three labels are added at 5%, 50%, and 95% of the minimum and maximum values.

The ranges are - if made active via the checkbox - added to the selection criteria via the following code (again inside OnInitializeReport):

Get GetChecked of oUseBalanceFiltering to bUseValue
If (bUseValue) Begin
    WebGet piFrom of oBalanceSlider to iFrom
    WebGet piTo of oBalanceSlider to iTo
    Send AddFilter C_USEMAINVRWREPORTID "{Customer.Balance}" C_VRWGreaterThanOrEqual iFrom
    Send AddFilter C_USEMAINVRWREPORTID "{Customer.Balance}" C_VRWLessThanOrEqual iTo
End

In the report description above, it is mentioned that the report uses codemast.dll to return the name of the state. For this, the path to the codemast table needed to be given, and it was done via a parameter named CodeMastPath. The parameter is set in a method of its own (started from OnInitializeReport). In the following code, the report is queried for the parameter ID of the named parameter CodeMastPath, which is then used to set the path.

Procedure SetParameters
    Integer iParameter
    String sCodeMastPath

    Get ParameterIdByName C_USEMAINVRWREPORTID 'CodeMastPath' to iParameter
    Get_File_Path "CodeMast.Dat" to sCodeMastPath
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to sCodeMastPath
End_Procedure

For deployment, it is important to make sure the DLL can be loaded. In the website, this means the codemast.dll has to be copied to the same folder where webapp.exe is stored.

The report generation is done in a routine named GenerateReport, which is started by the RunReport button in the command bar used in the cWebView.

Procedure GenerateReport
    String sReportId sFile sUrl
    VRWPDFExportOptions PDFExportOptions
    Boolean bCanceled

    Get OpenReport to sReportId
    If (sReportId <> "") Begin
        Get DefaultPDFExportOptions to PDFExportOptions
        Set pPDFExportOptions to PDFExportOptions
        Get ReportCacheFileName ".pdf" to sFile
        If (sFile <> "") Begin
            Send ExportReport C_vrwPDF sFile
            Get pbCanceled to bCanceled
            If (not (bCanceled)) Begin
                Get DownloadURL of ghoWebResourceManager sFile to sUrl
                If (sUrl <> "") Begin
                    Send Show of oResultsTabPage
                    WebSet psUrl of oViewer to sUrl
                End
            End
        End
        Send CloseReport sReportId
    End
End_Procedure

The main difference between this code and the GenerateReport discussed in the Invoices Report Blog is the instruction to switch to the results tab-page. The Send Show is responsible for switching tab-pages.

We hope that this blog - and the upcoming blogs - help you to create a very successful business-level web application using the 17.1 Web Framework and the Data Access Report Writer.