Development Team Blog

As follow up story on Visual Report Writer and the web this time the first report available on the demo website for Visual Report Writer. You might think "only one?" but read on and discover that there is enough to tell about this report and the integration.

Invoices Report
The first report that can be started on the website is called Invoice. The report uses the Microsoft Adventure Works 2000 database as its data-source. The report uses 14 tables to build the results. Very special in the model is that customer information needs to be collected from two tables. Customers can be shops or individuals. To collect the information the report makes uses of an left-outer-join between the table customer and the table shop at the same time a left-outer-join between customer and individual, both links from the same source column, which is impossible from a DataFlex table relationship as that would link to either table A or table B. The table relationship model is as shown in the next picture:

Integration
The web report view let you as visitor browse through the customers. Because a normal relationship is not possible (the customer ID needs to relate to shop or to individual) the relate_main_file event in the customer datadictionary object is augmented to find either the store or the individuals record when finding a particular customer. This can be done because the customer record contains a column that contains the customer type (store or individual). The Relate_Main_File event is as follows:

Code:

Procedure Relate_Main_File    
     Forward Send Relate_Main_File

    If (SQLCustomer.CustomerType = 'I') Begin
        Clear SQLIndividual
        Move SQLCustomer.CustomerID to SQLIndividual.CustomerID
        Find Eq SQLIndividual by 3
    End
    Else Begin
        If (SQLCustomer.CustomerType = 'S') Begin
            Clear SQLStore
            Move SQLCustomer.CustomerID to SQLStore.CustomerID
            Find Eq SQLStore by 3
        End
    End
End_Procedure

To display either the name of the store or the name of the individual the cWebForm control for the customer name is not directly connected to a table.column reference but makes use of the OnSetCalculatedValue event.

Code:

Object oCustomerNameForm is a cWebForm
    Set piColumnSpan to 10
    Set piColumnIndex to 3
    Set pbShowLabel to False
    Set pbEnabled to False

    Procedure OnSetCalculatedValue String ByRef sValue
        If (SQLCustomer.CustomerType = 'I') Begin
            Move SQLIndividual.LastName to sValue
        End
        Else Begin
            If (SQLCustomer.CustomerType = 'S') Begin
                Move SQLStore.Name to sValue
            End
            Else Begin
                Move '' to sValue
            End
        End
    End_Procedure
End_Object

Finally to also get the prompt object working correctly the peUpdateMode property of the prompt object is set to umPromptCustom and the psPromptUpdateCallBack is set to a custom method named ShowAndFindCustomer. The code for this method is as follows:

Code:

Object oCustomerIDForm is a cWebForm
    Entry_Item SQLCustomer.CustomerID
    Set psLabel to "Customer:"
    Set piColumnSpan to 3
    Set peLabelAlign to alignRight

    WebPublishProcedure ShowAndFindCustomer

    Procedure ShowAndFindCustomer Handle hoPrompt
        Handle hoServer
        Integer iMainFile

        Get Server of hoPrompt to hoServer
        Get Main_File of hoServer to iMainFile
        If (iMainFile = SQLIndividual.File_Number) Begin
            Send Clear of oSQLCustomer_DD
            Get Field_Current_Value of hoServer Field SQLIndividual.CustomerID to SQLCustomer.CustomerID
            Send Find of oSQLCustomer_DD EQ 1
        End
        Else Begin
            Send Clear of oSQLCustomer_DD
            Get Field_Current_Value of hoServer Field SQLStore.CustomerID to SQLCustomer.CustomerID
            Send Find of oSQLCustomer_DD EQ 1
        End
    End_Procedure
End_Object

If you take a close look at the selectionlist you will see that the dialog contains two tab-pages both containing a cWebList object. The first tab-page shows the store names and the second tab-page shows the names of the individuals. I experimented with combining both sets of data in one list using an ESQL statement but considered - due to the amount of data and the wish to be able to sort names - too slow. While not used we could have created a view in the database that combines both tables and change the table link to use the view.

The code for the double selectionlist is as follows:

Code:

Use cWebModalDialog
Use cWebPanel.pkg
Use cWebButton.pkg
Use cWebPromptList.pkg
Use cWebColumn.pkg
Use cWebTabContainer.pkg
Use cWebTabPage.pkg

Use cSQLIndividualDataDictionary.dd
Use cSQLStoreDataDictionary.dd

Object oSQLCustomerWebLookup is a cWebModalDialog
    Set piColumnCount to 8
    Set psCaption to "Select Customer"
    Set piWidth to 700
    Set piHeight to 400

    Object oSQLIndividual_DD is a cSQLIndividualDataDictionary
    End_Object
    
    Object oSQLStore_DD is a cSQLStoreDataDictionary
    End_Object
    
    Object oWebTabContainer is a cWebTabContainer
        Set pbFillHeight to True

        Object oStoresTabPage is a cWebTabPage
            Set psCaption to "Stores"
            Set piColumnCount to 8
            
            Object oStoresPromptList is a cWebPromptList
                Set pbFillHeight to True
                Set piColumnSpan to 8
                Set piOrdering to 4
                Set Server to oSQLStore_DD
                Set peUpdateMode to umPromptCustom
                Set psPromptUpdateCallback to "ShowAndFindCustomer"
                Set piUpdateColumn to 0
    
                Object oSQLCustomerIDForm is a cWebColumn
                    Entry_Item SQLStore.CustomerID
                    Set psCaption to "Nr"
                    Set piWidth to 30
                End_Object
                
                Object oSQLCustomerNameForm is a cWebColumn
                    Set psCaption to "Name"
                    Set piWidth to 150
                    Entry_Item SQLStore.Name
                End_Object
            End_Object
            
            Object oOkButton is a cWebButton
                Set psCaption to "OK"
                Set piColumnSpan to 1
                Set piColumnIndex to 5
        
                Procedure OnClick
                    Send Ok of oStoresPromptList
                End_Procedure
            End_Object 
        
            Object oCancelButton is a cWebButton
                Set psCaption to "Cancel"
                Set piColumnSpan to 1
                Set piColumnIndex to 6
                
                Procedure OnClick
                    Send Cancel of oStoresPromptList
                End_Procedure
            End_Object 
        
            Object oSearchButton is a cWebButton
                Set psCaption to "Search..."
                Set piColumnSpan to 1
                Set piColumnIndex to 7
        
                Procedure OnClick
                    Send Search of oStoresPromptList
                End_Procedure
            End_Object        
            
            Procedure SelectAndClose
                Send Ok of oStoresPromptList
            End_Procedure
        End_Object

        Object oIndividualsTabPage is a cWebTabPage
            Set psCaption to "Individuals"
            Set piColumnCount to 8
            
            Object oIndividualsPromptList is a cWebPromptList
                Set pbFillHeight to True
                Set piColumnSpan to 8
                Set piOrdering to 4
                Set Server to oSQLIndividual_DD
                Set peUpdateMode to umPromptCustom
                Set psPromptUpdateCallback to "ShowAndFindCustomer"
                Set piUpdateColumn to 0
    
                Object oSQLCustomerIDForm is a cWebColumn
                    Entry_Item SQLIndividual.CustomerID
                    Set psCaption to "Nr"
                    Set piWidth to 30
                End_Object
                
                Object oSQLCustomerNameForm is a cWebColumn
                    Set psCaption to "Name"
                    Set piWidth to 150
                    Entry_Item SQLIndividual.LastName
                End_Object
            End_Object 
            
            Object oOkButton is a cWebButton
                Set psCaption to "OK"
                Set piColumnSpan to 1
                Set piColumnIndex to 5
        
                Procedure OnClick
                    Send Ok of oIndividualsPromptList
                End_Procedure
            End_Object 
        
            Object oCancelButton is a cWebButton
                Set psCaption to "Cancel"
                Set piColumnSpan to 1
                Set piColumnIndex to 6
                
                Procedure OnClick
                    Send Cancel of oIndividualsPromptList
                End_Procedure
            End_Object 
        
            Object oSearchButton is a cWebButton
                Set psCaption to "Search..."
                Set piColumnSpan to 1
                Set piColumnIndex to 7
        
                Procedure OnClick
                    Send Search of oIndividualsPromptList
                End_Procedure
            End_Object        
            
            Procedure SelectAndClose
                Send Ok of oIndividualsPromptList
            End_Procedure
        End_Object
    End_Object
    
    Set pbServerOnSubmit to True
    
    Procedure OnSubmit 
        Handle hoCurrentCard
        
        Get CurrentCard of oWebTabContainer to hoCurrentCard
        Send SelectAndClose of hoCurrentCard
    End_Procedure 

    Set pbServerOnShow to True
    
    Procedure OnShow
        Send InitializePromptList of oStoresPromptList
        Send InitializePromptList of oIndividualsPromptList
    End_Procedure
End_Object

Special in above code are the OnShow and OnSubmit routines. The OnShow is normally kept hidden for the developer. In this case it is coded to address both selectionlists and instructs them to load data. The OnSubmit is special because it tells what selectionlist should return its information.

The cWebView also contains two cWebDateForm controls to select the date range and as date suggestion the minimum and maximum date values from the table are displayed in the controls. The dates are retrieved by excecution of an ESQL statement during the OnLoad event of the control.

Code:

Object oOrderFromDate is a cWebDateForm
    Set psLabel to "Order Date From:"
    Set piColumnSpan to 4
    Set peLabelAlign to alignRight

    Procedure OnLoad
        Handle hoSQL hoConnection hoStatement
        Integer iFetchResult
        String sDate
        Date dDate

        Forward Send OnLoad

        Get Create (RefClass (cSQLHandleManager)) to hoSQL
        Get SQLFileConnect of hoSQL SQLCustomer.File_Number to hoConnection
        Get SQLOpen of hoConnection to hoStatement
        Send SQLExecDirect of hoStatement "select MIN ([salesorderheader].[orderdate]) from [salesorderheader]"
        Repeat
            Get SQLFetch of hoStatement to iFetchResult
            If (iFetchResult <> 0) Begin
                Get SQLColumnValue of hoStatement 1 to sDate
                Get SQLDateToDFDate of hoStatement sDate to dDate
                Set psValue to dDate
            End
        Until (iFetchResult = 0)

        //  Clean up stuff
        Send SQLClose to hoStatement
        Send SQLDisconnect to hoConnection
        Send Destroy of hoSQL
    End_Procedure
End_Object

If you would create a report without a customer selected and thus on "just" the date range you would create a report with more than 14,000 pages of information. While Visual Report Writer can do this it is the web that does not like to wait that long and thus it is wise to limit the result set. When you click the button the print the invoices a check for customer ID and a date range check is executed.

Code:

Object oRunReportMenuItem is a cWebMenuItem
    Set psCaption to "Print Invoices"
    Set psTooltip to "Print the invoices using the selections"
    Set psCSSClass to "VRWPrintReportButton"

    Procedure OnLoad
        String[] aParams

        Forward Send OnLoad

        Move "ConfirmResponse" to aParams[0]
        Move 2 to aParams[1]

        Send ClientAction "setActionMode" aParams
    End_Procedure

    Procedure ConfirmResponse Integer eConfirmMode
        If (eConfirmMode = cmYes) Begin
            Send GenerateReport of oReport
        End
    End_Procedure

    WebPublishProcedure ConfirmResponse

    Procedure OnClick
        DateTime dtFrom dtTo
        Integer iCustomerNumber

        WebGet psValue of oCustomerIDForm to iCustomerNumber
        If (iCustomerNumber = 0) Begin
            Send ShowInfoBox "A Customer Selection is required"
            Procedure_Return
        End

        WebGet psValue of oOrderFromDate to dtFrom
        WebGet psValue of oOrderToDate to dtTo
        If (SpanTotalDays (dtTo - dtFrom) > 100) Begin
            Send ShowYesNo of oWebApp (Self) (RefProc (ConfirmResponse)) "The date range exceeds 100 days. Are you sure you want to generate a report with this range? Reporting may take some time to generate." "Date Range Large!"
        End
        Else Begin
            Send GenerateReport of oReport
        End
    End_Procedure
End_Object

The use of ConfirmResponse would be enough to get the report going but the OnLoad event finish it by telling the framework to display an hour-glass indication during the report generation. If the report was started from a button it would be normally turned on automatically but the use of the menu item control does not do that. With the code in OnLoad the waiting circle appears.

In the code above you can find that the report is generated by sending a message named GenerateReport. This is not a method defined in the cVRWReport class but coded in the object to generate this specific report. In the method the report needs to be located and opened and needs to be instructed to output the results to a file as this report makes use of displaying the results via a PDF file. For those of you that are familiar with Visual Report integration it should be clear that OpenReport and ExportReport are the key messages to be send.

During OpenReport the event OnInitializeReport is triggered. In the following code you can see that this event is used to set the value of a parameter named ImagePath. The report uses an image and needs to be told where to locate the file with the image. We need to do this as the location will be different on the webserver than on my development machine. The image is stored in the same folder as the report and thus psReportLocation can be used as the path.

Code:

Procedure OnInitializeReport
    String sReportLocation
    Integer iParameter
    
    Get psReportLocation to sReportLocation
    Get ParameterIdByName C_USEMAINVRWREPORTID 'ImagePath' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to sReportLocation

    Send SetFilters
End_Procedure

To keep the code readable a routine to set the filter information is created named SetFilters. in this routine the values of the customer ID, from and end date are retrieved. Notice that the values need to be retrieved via WebGet and not via a normal Get. Also notice that the dates are converted to a string value needed by SQL server for the selection via the function DateTimeToString, a function of cVRWReport.

Code:

Procedure SetFilters
    Integer iCustomerId
    Date dFrom dTo
    String sFrom sTo

    WebGet psValue of oCustomerIDForm to iCustomerId
    WebGet psValue of oOrderFromDate to dFrom
    WebGet psValue of oOrderToDate to dTo

    Send RemoveAllFilters C_USEMAINVRWREPORTID
    If (iCustomerId <> 0) Begin
        Send AddFilter C_USEMAINVRWREPORTID "{Customer.CustomerId}" C_VRWEqual iCustomerId
    End

    If (not (IsNullDateTime (dFrom))) Begin
        Get DateTimeToString dFrom to sFrom
        Send AddFilter C_USEMAINVRWREPORTID "{SalesOrderHeader.OrderDate}" C_VRWGreaterThanOrEqual sFrom
    End

    If (not (IsNullDateTime (dTo))) Begin
        Get DateTimeToString dTo to sTo
        Send AddFilter C_USEMAINVRWREPORTID "{SalesOrderHeader.OrderDate}" C_VRWLessThanOrEqual sTo
    End
End_Procedure

Finally we come to the point of report output and display. As written the output needs to be spooled to a PDF file. To avoid two users/sessions to get the same or eachothers results the code will create a unique filename. It does this by calling the new ReportCacheFileName function in the VRW library. The generated results are stored in a folder that is not directly accessible over the web. A special function named DownloadURL (defined in the resource manager) converts the absolute path into a unique download URL that is only available for a number of hours and only for the current session id.

Code:

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
                    WebSet psUrl of oViewer to sUrl
                End
            End
        End

        Send CloseReport sReportId
    End
End_Procedure

The URL generated by DownloadURL is given to the oViewer object which is a instantiation of the cWebIFrame class.

I hope this blog gets you on the path of using Visual Report Writer together with the DataFlex Web Application Framework.