Data Access Worldwide Forums - Blogs - Development Team Blog by Development Team

Visual Report Writer and The Web (VII)

Development Team — Sun, 12 May 2013 06:00:00 GMT

In this seventh blog about Visual Report Writer and the Web we look at the next report that is available on the Live Demo website (European Server, USA server) named Inventory Stock Levels. If this is the first blog you read I encourage you to read the six other blogs (1: The Solution, 2: Invoices Report, 3: The Cleanup, 4: The CustomerList, 5: The Orderlist and 6: The Credit and Balances Overview). Between the fourth and the fifth blog we have been able to release the Alpha I 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 uses DataFlex embedded database, the same database that is used for the WebOrder example that comes with Visual DataFlex 17.1. The tables invt and vendor are used for the report. The report groups the inventory data per vendor, sorted by name. The specialties in this report are a calculated column that contains the result of the inventory unit price times the number on hand. The value is not shown in the report, it is suppressed printed in the details section. The field's content is used for a sum operation in the group footer and the report footer. The report footer can be suppressed via a parameter in the report which can be set from integration. Another speciality is the use of a barcode font to convert the item_id into a barcode. The font is used is named "C39HrP24DlTt". If you install this font on your web-server you need to reboot the server in order to make it active.

The Integration
For the integration we create two cWebView components. One view creates a PDF when you browse through the vendors and allows you to suppress the report footer while the second component directly shows the report without filtering on vendor or allowing report footer suppress.

The OverView
First take a look at the overview implementation. This is the cWebView that immediately show the full report. While Visual Report Writer is fast (also depends in the number of simultaneously accesses and the speed of hardware and internet connections it should not be used when the amount of data is so big that a huge amount of pages is generated or when filters should be really applied. Web server access should be short and not time consuming. Users don't want to wait a long time. In this case it is fine, the report is only 3 pages long.

Note the difference between the report display in Chrome and Firefox. In Chrome your barcode is shown, in FireFox (version 20) it does not show. FireFox reads the PDF and converts it to HTML and while the barcode font is stored in the PDF it does not make use of it. In FireFox the report display is slower too.

The report start is triggered by the OnShow event of the cWebView component. So each time you open the view the report is build and shown in an iFrame control.
Code:
    Procedure OnShow
        Forward Send OnShow
        
        Send GenerateReport of oReport
    End_Procedure
End_Object
The report generating code is as in the other reports that create a PDF file.
Code:
Object oWebMainPanel is a cWebPanel
    Object oReport is a cVRWReport
        Set psReportName to "320 Inventory Stock levels per Vendor.vrw"

        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
    End_Object

    Object oViewer is a cWebIFrame
       Set pbFillHeight to True
       Set pbShowBorder to True
    End_Object
End_Object
Per Vendor
In the second cWebView component that uses the same report the generation of the report results is trigged from an OnPostFind event in the vendor data dictionary object, like it is done in Credit And Balances report view.
Code:
Object oVendor_DD is a cVendorDataDictionary
    Procedure OnPostFind Integer eMessage Boolean bFound
        Boolean bSyncing

        Forward Send OnPostFind eMessage bFound

        Get AppSynching of ghoWebApp to bSyncing
        If (not (bSyncing)) Begin
            Send GenerateReport of oReport
        End
    End_Procedure
End_Object
The special code in this view is the use of a parameter in the report. The view has a checkbox (cWebCheckbox) that let the user indicate if the report footer should be suppressed or not. The status of this checkbox is read in the OnInitializeReport event of the cVRWReport object.
Code:
Procedure OnInitializeReport
    Boolean bSuppress
    Integer iParameter
    
    Get GetChecked of oSuppressFooterSection to bSuppress
    Get ParameterIdByName C_USEMAINVRWREPORTID 'SuppressFooter' to iParameter
    Set psParameterValue C_USEMAINVRWREPORTID iParameter to bSuppress

    Send SetFilters
End_Procedure
The filtering is done by taking the value of the vendor ID cWebForm object and add it as a filter to the report using the AddFilter message.
Code:
Procedure SetFilters
    Integer iVendorId

    WebGet psValue of oVendorID to iVendorId

    Send RemoveAllFilters C_USEMAINVRWREPORTID
    If (iVendorId <> 0) Begin
        Send AddFilter C_USEMAINVRWREPORTID "{Vendor.Id}" C_VRWEqual iVendorId
    End
End_Procedure
This blog was again written to show you what power there is behind Visual Report Writer and the Web and how easy it is to implement, integrate the reports. Make sure your customers get to know the product and deliver nice looking reports to you for integration.

Visual Report Writer and The Web (VI)

Development Team — Thu, 09 May 2013 05:42:28 GMT

Blog number six in this category about Visual Report Writer and the web integration. I will talk about the next report that you can find on the Live Demo website (European Server, USA server) named Credit and Balances Overview. If this is the first blog you read I encourage you to read the five other blogs (1: The Solution, 2: Invoices Report, 3: The Cleanup, 4: The CustomerList and 5: The OrderList). Between the fourth and the fifth blog we have been able to release the Alpha I 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 uses the DataFlex embedded database, the same database as is used for the WebOrder workspace of version 17.1. The tables Orderhea and Customer are in use and a normal relationship between orders and customers is in force. The special features in this report are three calculated columns (functions) that concatenate customer details, calculate the difference between credit_limit and balance and display the number of years that is between the current date and the order date. Further the report creates a new page for each customer.

The Integration
At integration level there are two cWebView components using the report. One creates a PDF when browsing through the customers and one makes it possible to export the report to a mult-page TIFF file and download this to your computer.

The PDF report
In the cWebView component that creates a PDF we need to focus on how this is triggered as the PDF creation and displaying it in an iFrame is already covered in other blogs in the serie. As you can see the report executes automatically when browsing through the customers in the database. It also shows you how fast Visual Report Writer can do this (and I promise 3.0 will be faster!). And keep in mind that the report is exported to file at the web server and downloaded to the browser. BTW, you will see Google Chrome display the report faster than Firefox as the build in PDF viewer of Chrome is faster and does not convert the PDF to HTML which is done by FireFox.

To trigger the report creation we made use of the OnPostFind event of the datadictionary class. Here is the code:
Code:
Object oCustomer_DD is a cCustomerDataDictionary
    Procedure OnPostFind Integer eMessage Boolean bFound
        Boolean bSyncing bHasRecord

        Forward Send OnPostFind eMessage bFound

        Get AppSynching of ghoWebApp to bSyncing
        If (not (bSyncing)) Begin
            Get HasRecord to bHasRecord
            If (bHasRecord) Begin
                Send GenerateReport of oReport
            End
            Else Begin
                Set psUrl of oViewer to "about:blank"
            End
        End
    End_Procedure
End_Object
The only tricky difference between above code and a Windows project that can use the same event (version 17.0 and higher) is the AppSynching check. The report should not be generated when the application is synchronizing.

The report selection is done in the OnInitializeReport event of the cVRWReport object. It takes the current displayed customer number (WebGet) and adds a filter to the report based on this information.
Code:
Procedure OnInitializeReport
    Integer iCustomerId

    WebGet psValue of oCustomerCustomer_Number to iCustomerId

    Send RemoveAllFilters C_USEMAINVRWREPORTID
    If (iCustomerId <> 0) Begin
        Send AddFilter C_USEMAINVRWREPORTID "{Customer.Customer_Number}" C_VRWEqual iCustomerId
    End
End_Procedure
The TIFF report
In order to show you that reports are not limited to PDF generation, display and handling and that Visual Report Writer can export to other file formats this cWebView was added. A TIFF file is a image file format that allows multiple pages in one file. Make sure you have an image viewer that support this if you want to see this. Where the PDF report view automatically generates the report this view let you decide. You first browse to a customer, lookup its information and then press the "TiFF download" button. When you press this only difference sits in the GenerateReport method.
Code:
Procedure GenerateReport
    String sReportId sFile sUrl
    VRWImageExportOptions ImageExportOptions
    Boolean bCanceled

    Get OpenReport to sReportId
    If (sReportId <> "") Begin
        Get DefaultImageExportOptions to ImageExportOptions
        Set pImageExportOptions to ImageExportOptions
        Get ReportCacheFileName ".tiff" to sFile
        If (sFile <> "") Begin
            Send ExportReport C_VRWImage sFile    
            Get pbCanceled to bCanceled
            If (not (bCanceled)) Begin
                Get DownloadURL of ghoWebResourceManager sFile to sUrl
                If (sUrl <> "") Begin
                    Send NavigateToPage of oWebApp sUrl btCurrentWindow
                End
            End
        End

        Send CloseReport sReportId
    End
End_Procedure
The differences are:
.tiff export filename (instead of .pdf so-far)

VRWImageExportOptions structure in use (instead of VRWPDFExportOptions)

DefaultImageExportOptions instead of DefaultPDFExportOptions

C_VRWImage parameter for ExportReport instead of C_vrwPDF

NavigateToPage instead of setting the psURL

The browser will in most cases download the tiff file to your computer as TIFF file display in a browser requires a special plug in (if available).

I hope that this sixth blog sets you on the path of using Visual Report Writer in the web report world. More blogs will follow.

Visual Report Writer and The Web (V)

Development Team — Mon, 06 May 2013 17:03:53 GMT

In this fifth blog about Visual Report and The Web I will talk about the next report that you can find on the Live Demo website (European Server, USA server) named OrderList. If this is the first blog you read I encourage you to read the four other blogs (1: The Solution, 2: Invoices Report, 3: The Cleanup and 4: The CustomerList). Between the fourth blog and this one we have been able to release the Alpha I 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 used in this example webview is delivered with the 17.1 WebOrder example workspace. The report shows how you can make use of colors (page header and group header have a different color), how you can show the selection criteria (assuming the report "only" filters on order total) can be shown and how you can concatenate table columns with a function into a new calculated column. The report also uses two summaries placed in the group footer.

The Integration
At integration level the speciality contains two features not seen in the other blogs yet; the user can select the report order in a multi-segment way and the result (PDF) is shown in a modal dialog.

The Ordering Selection
This integration feature from a Visual Report Writer point of view is not very complex as you can set the record ordering by either expanding or replacing the current ordering. Replacing means you need to remove the current ordering which is done via:
Code:
Send RemoveAllRecordSortFields sReportId
The sReportId is a GUID returned by an OpenReport instruction. The report id (required) may also be passed to the function as an empty string (or C_USEMAINVRWREPORTID) in which case the integration library uses the psReportId property of the cVRWReport class.

Adding record filter instructions can be done by sending the AddRecordSortField message. The user selected sort fields in this example view can be retrieved by calling a function named SortColumnSet from the grid. The function returns two-dimensional string array with the sort field names and their sort direction (ascending/descending). The returned information can be enumerated and each row in the array can be used to set the record sort order. The code in this example is:
Code:
Procedure SetSortFields
    String[][2] sSortColumnData
    Integer iElements iElement

    Send RemoveAllRecordSortFields C_USEMAINVRWREPORTID
    Get SortColumnSet of oSortColumnsGrid to sSortColumnData
    Move (SizeOfArray (sSortColumnData)) to iElements
    Decrement iElements
    For iElement from 0 to iElements
        Send AddRecordSortField C_USEMAINVRWREPORTID sSortColumnData[iElement][0] sSortColumnData[iElement][1]
    Loop
End_Procedure
Let us now look at the control because that let the user select the columns for the sort ordering. First; the end-user cannot select which columns to sort on, only set the sort order amongst the columns. Which columns are available is chosen by the report integrator (you). The grid has 2 columns; one for the name of the sort column and one for selecting ascending/descending.
Code:
Object oSortColumnsGrid is a cVRWWebSortColumnsGrid
    Object oSortOnWebColumn is a cWebColumn
        Set psCaption to "Sort On"
        Set pbSortable to False
        Set piWidth to 90
        Set pbEnabled to False
    End_Object

    Object oSortDirectionWebColumn is a cVRWWebSortDirectionColumn
        Set psCaption to "Direction"
        Set pbServerOnChange to True

        Procedure OnChange String sNewValue String sOldValue
            Forward Send OnChange sNewValue sOldValue

            Send ProcessDataSet of oSortColumnsGrid 3
        End_Procedure
    End_Object

    Procedure OnSetSortColumns tWebRow[] ByRef WebSortColumnRows
        Move "{Orderdtl.Detail_Number}" to WebSortColumnRows[0].aValues[0]
        Move "Detail Nr" to WebSortColumnRows[0].aValues[1]
        Move C_VRWAscending to WebSortColumnRows[0].aValues[2]

        Move "{Orderdtl.Item_Id}" to WebSortColumnRows[1].aValues[0]
        Move "Item ID" to WebSortColumnRows[1].aValues[1]
        Move C_VRWAscending to WebSortColumnRows[1].aValues[2]

        Move "{Orderdtl.Qty_Ordered}" to WebSortColumnRows[2].aValues[0]
        Move "Qty Ordered" to WebSortColumnRows[2].aValues[1]
        Move C_VRWAscending to WebSortColumnRows[2].aValues[2]
    End_Procedure
End_Object
The grid object is instantiated from the cVRWWebSortColumnsGrid class which is a sub-class of cWebGrid. The grid sub-class is not readonly and not a cWebList because the user must be able to select the sort direction of each of the columns. New grid rows cannot be added by the end-user by setting the pbAllowAppendRow, pbAllowInsertRow to false. Users cannot remove columns by setting the pbAllowDeleteRow to false. These properties are set in the construct_object event.

The sort columns are loaded in an event named OnSetSortColumns. This event is fired from OnManualLoadData (documented event of the cWebGrid class). The OnManualLoadData is marked as private for the cVRWWebSortColumnsGrid class and should not be used by you. The grid data is stored in both the grid as well as a web synchronized property that contains a comma separated value list of the grid data. This comma separated list is maintained to reduce the complexiity when getting the users' choice for the sort order. The SortColumnSet function (used from SetSortFields) converts this comma separated list into a two dimensional array of sort columns.

As you can see in the code above the grid seems to have three columns as for every grid row 3 values are put in the tWebRow[] argument. This is not true, the first value (the [0] value) is a unique row identifier. In a data-aware grid (such as an order detail grid) it contains the serialized RowId of the database table row. In this non data-aware grid (pbDataAware is false and peDbGridType is gtManual) the unique value is the table.column reference as Visual Report Writer likes to get it from you. This makes the first real grid column just a display value column and you could anything you like in there, of course the end-user must be able to make the decision based on this value.

If you like to translate the words "Ascending" and "Descending" you can set the properties psAscendingText and psDescendingText of the sort direction column (class is cVRWWebSortDirectionColumn). BTW, I noticed Google translate the values last week and misteriously the control kept working.

Changing the sort order can be done by clicking one of the buttons on the side. These controls are cWebButtons but don't look like buttons (thanks to CSS). If the end-user clicks one of the buttons a ProcessDataSet message is send to the grid. The method ProcessDataSet is augmented in the class to correct the piCurrentRowIndex if needed and then send to the client. The client on its turn sends a OnProcessDataSet to the server. Based on whether the user clicked the up or down button or changed the sort direction drop-down value the OnProcessDataSet is changing the contents of the grid's data (passed as a tWebRow[] argument). After really changing the data a grid refresh instruction is send to the client. Data is only changed if a sort column could be moved up or down in the order or the user changed the sort direction.

The good news of this rather complex grid control is that you never have to code this yourself if you are using the integration wizard. During the wizard you can select that the user should be able to select the sort order and that a multi value sort order is desired and the whole grid (plus the buttons) are generated. Important complexity if this grid is that the messages are - due to process pooling - most likely not handled by the same server process. It is all covered for you but be aware if you like to make changes.

The Modal Display
The results of the report are written to a PDF file on the server as with the customer report and a couple of other reports on this demo website. The PDF is not displayed in a IFrame or on a different window/browser nor on a second tab-page but are shown in a modal dialog. The integration library contains a web object component that can be included in your web application and used for displaying these files. The component file name is PDFModalDialog.wo. The component can be included in the report component or inside webapp.src where all other web components are included. In either case the dialog becomes a sibling of the cWebView as it should. And the really good news - I hope - is that the integration wizard can do this for you!
Code:
Use PDFModalDialog.wo
The component defines an object of the cWebModalDialog class with two panel children. One to display the results and one to organize the location of the button to close the dialog.

You use the dialog by sending the message ShowPDFModal to the object passing the object to return back to when the dialog closes, the object handle of the cVRWReport object and a caption text to be displayed in the caption bar of the modal dialog.
Code:
Object oRunReportMenuItem is a cWebMenuItem
    Set psCaption to "Run Report"
    Set psTooltip to "Run the report with selections"
    Set psCSSClass to "VRWRunReportButton"

    Procedure OnClick
        Send ShowPDFModal of oPDFModalDialog Self oReport "OrderList (PDF)"
    End_Procedure
End_Object
As you can deduct from above code you are not passing the name of the PDF file but sending the instruction to create the report PDF and display it. The ShowPDFModal method sends a message RunReport which in its turn send a GenerateReport message to the cVRWReport object. So the cVRWReport object (inside the cWebView component) has the logic to collect the selection criteria, sort orders etc and generate the PDF file and return the URL to the PDF file to the caller (the dialog).

The GenerateReport inside the cVRWReport object is the same as shown before but to make it easy for my readers, here is the code:
Code:
Function GenerateReport Returns String
    String sReportId sFile
    vrwPDFExportOptions PDFReportOptions
    Boolean bCanceled

    Get OpenReport to sReportId
    If (sReportId <> "") Begin
        Get DefaultPDFExportOptions to PDFReportOptions
        Set pPDFExportOptions to PDFReportOptions
        Get ReportCacheFileName ".pdf" to sFile
        If (sFile <> "") Begin
            Send ExportReport C_vrwPDF sFile
        End

        // see if report ran ok, if not return empty path
        Get pbCanceled to bCanceled
        If bCanceled Begin
            Move "" to sFile
        End

        Send CloseReport of oReport sReportId
    End

    Function_Return sFile
End_Function
This finishes this fifth blog about Visual Report Writer and the web. Keep tuned as more blogs will follow in the coming time.

Visual Report Writer and The Web (IV)

Development Team — Sun, 28 Apr 2013 15:39:12 GMT

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 here discussed report 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 return the text "Page:" combined with the special field {&Page Number}. Besides the fact that you can easily combine a 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:
Code:
return CodeDescription({?CodeMastPath},"STATES",{Customer.State})
The first parameter of this function call is a path to the codemast table. The codemast table maybe 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 integration level in a similar way as pbAutoLocateDFFiles operates.

Last but not least; a report should look nice, should be attractive. In this report we added a blue bar behind the column captions and the bar has a 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 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 eachother 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 shows the customers of one state this is blanked out. You can still - and it is the purpose of having the cWebCombo - a state to limit the data found and presented. In the OnInitializeReport event of the cVRWReport object this is covered as:
Code:
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:
Code:
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 jump-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' choice to the report selection criteria.
Code:
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.
Code:
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 a 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 code is behind the slider controls? First the object itself:
Code:
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 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:
Code:
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 mimimum 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.
Code:
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)
Code:
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 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.
Code:
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 web site 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 commandbar used in the cWebView.
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
                    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 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 succesful business level web application using the 17.1 Web Framework and the Data Access Report Writer.

Visual Report Writer and The Web (III)

Development Team — Tue, 23 Apr 2013 05:58:56 GMT

This is the third blog about Visual Report Writer and the Web. Did you already digest the other two blogs (Solution Page and Invoices Report)? If not, I suggest you read them first. This blog is about cleanup at the server. It is not the last one in the serie.

Cache files
From the blog about Invoices Report you should have learned that the integration library instructs Visual Report Writer to create a PDF file with the output of the report. By default this output file is only for the current session and can be made only available for a certain amount of time. The output is stored in a cache folder. The standard output folder is named Cache and resides inside the Reports folder. The cache folder does not have to be a sub-folder of the reports folder but it is by default and it is advisable as This folder is not web shared and files are not public accessible. The function ReportCacheFileName (member of the cVRWReport class) creates a unique file name in the cache folder. Certainly if a lot of users/sessions create reports the cache folder will soon be big and a lot of files are no longer accessible. How long should the files in there be kept? Who should be responsible for cleanup?

The files should be available as long as the URL that is generated can be used. The URL generated by the cWebResourceManager can be bound to the sessionkey and can be made to expire by setting a timeout specified in hours. By default the timeout is set to 0 which means infinitive. If you want to have the URL not available for that time span you have to set the piDownloadTimeout property of the ghoWebResourceManager object. You can do this globally or per report but keep in mind that the value is always global and if not set again will be applied for the next URL. While it is possible to not bind the URL to the current sessionkey it is not advised to do so else anyone can copy the URL and pass it to someone else and they can read the file contents.

Cleanup
i see three ways to cleanup the cache folder. They are:
Program that performs the cleanup started by the Windows scheduler. This may even be a batch file.

Part of the webserver backup plan. Of course you have your schedule to backup the data on the server and the cleanup could be done as part of that activity.

Have a routine inside your web application that periodically cleans the cache folder. This option is discussed here in this blog as it is in use for the Visual Report Writer Live Demo website.

Cleanup routine inside your application
While it is important to cleanup the process should not make your web application slower. To avoid this happens I made the cleanup in the live demo website to operate once in each 50 times that a request is received by one of the web applications in the pool. There is not such a thing as timer at the webserver itself and this counter based activity sounds Ok to me. The only downside is that it will be accessed a number of times in a row as each of the applications in the pool uses its own counter. I could have created a shared counter but that requires a bit of I/O that I did not want to use.

I wrote the cleanup routine in a self created object named oVRWReportsManager and since this is not really a web object it is an instance of the cObject class, the most empty class in the product. The cleanup routine is invoked from OnAttachProcess in the cWebApp object.
Code:
    Procedure OnAttachProcess
        Forward Send OnAttachProcess
        Send Cleanup of oVRWReportsManager
    End_Procedure
End_Object

Send StartWebApp of oWebApp
The above code snippet is the bottom part of the cWebApp object in WebApp.src. The routine does not need to be at the bottom but it just is.

In the cVRWReportsManager object I defined a regular DataFlex property named piAccessed and it is incremented by the message Cleanup. As soon as it becomes 50 or higher the cleanup starts and the property is set back to zero. If 50 would be too often it could be easily changed.

When the counter hits 50 the current date and time is retrieved via a CurrentDateTime() function and the cache folder is enumerated with Windows API functions to see if there are files that are older than 180 minutes. If they are the files are deleted. The following code does do this.
Code:
Procedure Cleanup
    Integer iAccessed iRetval iMinutes
    String sCachePath sFileName
    Handle hFindFile
    tWin32_Find_Data FileData
    DateTime dtNow dtCreated
    TimeSpan tsDiff
    
    Get piAccessed to iAccessed
    Increment iAccessed
    
    If (iAccessed > 50) Begin
        Move (CurrentDateTime ()) to dtNow
        Get ReportsCacheFolder of oVRWReport to sCachePath
        Move (winFindFirstFile (sCachePath - '*.*', AddressOf (FileData))) to hFindFile
        If (hFindFile <> INVALID_HANDLE_VALUE) Begin
            Repeat
                If (not (IsFlagIn (FILE_ATTRIBUTE_DIRECTORY, FileData.dwFileAttributes))) Begin
                    Get ConvertDateTimeToSystemDateTime FileData.ftCreationLowDateTime FileData.ftCreationHighDateTime to dtCreated
                    Move (dtNow - dtCreated) to tsDiff
                    Move (SpanTotalMinutes (tsDiff)) to iMinutes
                    If (iMinutes > 180) Begin
                        Move (AddressOf (FileData.cFileName)) to sFileName
                        Move (sCachePath - sFileName) to sFileName
                        Move (DeleteFile (sFileName)) to iRetval
                    End
                End
                Move (winFindNextFile (hFindFile, AddressOf (FileData))) to iRetval            
            Until (iRetval = 0)
            Move (winFindClose (hFindFile)) to iRetval
        End
        Move 0 to iAccessed
    End
    
    Set piAccessed to iAccessed
End_Procedure
The ConvertDateTimeToSystemDateTime is not a built in routine but a function I wrote a long time ago (1999/2000) and published via the Data Access knowledge base. The winFindFile, winFindNextFile function are defined in the cWorkspace.pkg file.

I hope this blog helps you further on your path to use the DataFlex Web Framework in combination with Visual Report Writer.

Visual Report Writer and The Web (II)

Development Team — Sun, 21 Apr 2013 09:25:13 GMT

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.

Visual Report Writer and The Web (I)

Development Team — Mon, 15 Apr 2013 11:44:31 GMT

At the just ended Synergy 2013 conference held in Nashville, Tennessee we have shown how easy it will be to make Web Based Applications using the DataFlex Web Application Framework. One of the parts of a Web Application will be reporting. Data Access advises two reporting solutions; Dynamic AI and Visual Report Writer. During the Synergy sessions (symposium and trainings) demonstrations were given how relative easy it is to integrate the two reporting solutions in a Web Application made with the DataFlex Web Application Framework. To complete this we have created two demonstration websites where the power of Dynamic AI and Visual Report Writer as reporting solution are shown in real time. You can feel the speed, the output solutions and in this blog and the next blogs I will show you how what is behind the website, how it is made.

Before continue reading this blog I would advise you to visit the demonstration websites. You can visit the Dynamic AI demo website to feel the power of Dynamic AI. You can visit the Visual Report Writer websites at either a USA based server or a European based server.

The Start Page
The links above that go to the demo websites brings open a start page named "The Solution". Opening this page is controlled by setting the phoDefaultView property to this cWebView object. This page is a cWebView classed based component containing two panel (cWebPanel) objects. The panel on the left hand side is set to a width and can be resized by the visitor grabbing the splitter between the two panels. The splitter is turned on by setting the pbResizable property of the left panel to true. The panel on the right hand side does not have a width and the size is determined by the size of your browser. The panel on the left contains a treeview control (cWebTreeView) for navigation purposes. The contents are read from a DataFlex embedded database table. This allows us to change the contents without recompilation of the application. The first level of the tree is automatically expanded. Clicking on a node executes a find request at the server and results in showing the documentation text on the right panel which contains a cWebHTMLBox control. The text is stored as HTML in a TEXT based column in the documentation table. The text contains special instructions that allows the server to open an integration web view when the user clicks on a link.
While the Dynamic AI and Visual Report Writer demo websites are similar they will not be identical, they are two different report solutions. The next part of this blog and the next blogs are written from what you see on the Visual Report Writer live demo website.

Ok, enough said, let's look at some code that was used to fill the page.

In the OnLoad event of the cWebTreeView control the data is loaded. This is automatic and the events sends a message OnLoadChildNodes to get its data. In the treeview coded here the data is read from a table named Documentation. The code for this is:
Code:
Function OnLoadChildNodes String sId String sValue Integer iLevel Returns tWebTreeItem[]
    tWebTreeItem[] TreeItems
    
    Get CollectTreeData sId to TreeItems
    
    Function_Return TreeItems
End_Function

Function AppendTreeData tWebTreeItem[] ByRef DestinationTreeItems tWebTreeItem[] ByRef SourceTreeItems Returns Integer
    Integer iElements iElement iDestinationElement
    
    Move (SizeOfArray (DestinationTreeItems)) to iDestinationElement
    Move (SizeOfArray (SourceTreeItems)) to iElements
    For iElement from 0 to (iElements - 1)
        Move SourceTreeItems[iElement] to DestinationTreeItems[iDestinationElement]
        Increment iDestinationElement
    Loop
    
    Function_Return iElements
End_Function

Function CollectTreeData String sId Returns tWebTreeItem[]
    tWebTreeItem[] TreeItems ChildTreeItems
    Integer iElement iAddedItems iSet
    RowID riDocumentation

    Move (sId + 1) to iSet
    Constraint_Set iSet Clear
    Constrain Documentation.ParentID eq sId
    Constrained_Find First Documentation by 1
    While (Found)
        Move Documentation.ID to TreeItems[iElement].sId
        Move Documentation.ParentID to TreeItems[iElement].sParentId
        Move (Trim (Documentation.Name)) to TreeItems[iElement].sName
        Move Documentation.LoadChildren to TreeItems[iElement].bLoadChildren
        Move Documentation.Folder to TreeItems[iElement].bFolder
        Move Documentation.Expanded to TreeItems[iElement].bExpanded
        Move Documentation.Icon to TreeItems[iElement].sIcon
        Move (Trim (Documentation.CSSClass)) to TreeItems[iElement].sCSSClass
        Move (Trim (Documentation.AltText)) to TreeItems[iElement].sAltText
        Increment iElement
        If (Documentation.Expanded) Begin
            Move (GetRowID (Documentation.File_Number)) to riDocumentation
            Get CollectTreeData Documentation.ID to ChildTreeItems
            Get AppendTreeData (&TreeItems) (&ChildTreeItems) to iAddedItems
            Move (iElement + iAddedItems) to iElement
            Move (FindByRowID (Documentation.File_Number, riDocumentation)) to Found
        End
        Constraint_Set iSet
        Constrained_Find Next
    Loop
    
    Constraint_Set iSet Delete
    
    Function_Return TreeItems
End_Function
So the OnLoadChildNodes event sends a message to read the data from the table. If none of the rows does have the Expanded column set to true the routine would only load the data at the first level and the user needs to expand each tree item.
To ensure the first item is selected the OnLoad event is augmented to Select the item that corresponds with the first row in the table.
Code:
Procedure OnLoad
    Forward Send OnLoad
    
    Clear Documentation
    Find Gt Documentation by 1

    Send Select Documentation.ID
End_Procedure
The table layout is simple but complex as it uses a relationship to itself by using an ALIAS table and allowing NULL parent support. The table structure is:
Code:
NUM  FIELD NAME       TYPE SIZE  OFFST IX
---  ---------------  ---- ----- ----- --
  1  ID               NUM    4.0     1  1
  2  ParentID         NUM    4.0     3   
  3  Name             ASC    255     5   
  4  LoadChildren     NUM    2.0   260   
  5  Folder           NUM    2.0   261   
  6  Icon             ASC    255   262   
  7  CSSClass         ASC    100   517   
  8  AltText          ASC    150   617   
  9  Value            TEX  14752   767   
 10  Expanded         NUM    2.0 15519   
 11  ViewName         ASC    250 15520  2
The column ParentID links to the ALIAS table for Documentation from within the DataDictionary object that is used when we enter or alter the documentation. As you can see above the DataDictionary object is not used for reading the data into the treeview. While it could have been used there is no need to do so. The DataDictionary class that is used for DDOs when entering documentation is as follows:
Code:
Use DataDict.pkg

Open Documentation
Open DocumentationParent

Register_Object oDocumentationWebLookup

Class cDocumentationDataDictionary is a DataDictionary
    
    Procedure Construct_Object
        Forward Send Construct_Object
        Set Main_File to Documentation.File_Number

        Set Add_Server_File to DocumentationParent.File_Number

        Set ParentNullAllowed DocumentationParent.File_Number to True

        Set Foreign_Field_Option DD_KEYFIELD DD_NOPUT to True
        Set Foreign_Field_Option DD_KEYFIELD DD_FINDREQ to True
        Set Foreign_Field_Option DD_INDEXFIELD DD_NOPUT to True
        Set Foreign_Field_Option DD_DEFAULT DD_DISPLAYONLY to True

        Set Field_WebPrompt_Object Field Documentation.ID to oDocumentationWebLookup
        Set Field_Option Field Documentation.ID DD_AUTOFIND to True

        Set Field_Class_Name Field Documentation.LoadChildren to "Checkbox"

        Set Field_Class_Name Field Documentation.Folder to "Checkbox"

        Set pbUseDDRelates to True
        Set Field_Related_FileField Field Documentation.ParentID to File_Field DocumentationParent.ID
    End_Procedure
End_Class

#IFDEF Is$WebApp
Use Lookups\DocumentationWebLookup.wo
#ELSE
#ENDIF
Clicking a node in the treeview sends the OnSelect event to the server. In the OnSelect the row that is linked to the tree item is found and its documentation data is send to the cWebHtmlBox control via an UpdateHtml message.
Code:
Procedure OnSelect String sId String sValue Integer iLevel
    Forward Send OnSelect sId sValue iLevel
    
    Clear Documentation
    Move sId to Documentation.ID
    Find Eq Documentation.ID
    
    Move (Trim (Documentation.Value)) to sValue
    
    If (sValue = "") Begin
        Move " " to sValue
    End
    
    Send UpdateHtml of oWebDocumentationBox sValue
End_Procedure
The panel on the right contains a cWebHTMLBox object that is used to show the contents.
Code:
Object oExplanationPanel is a cWebPanel
        Object oWebDocumentationBox is a cWebHtmlBox
            Set psHtmlId to "SolutionText"
            Set pbServerOnClick to True
            Set pbFillHeight to True
            
            Procedure OnClick String sId String sParam
                Handle hoView
                
                Case Begin
                    Case (sId = "openview")
                        Get WebObjectByName sParam to hoView
                        Send Show of hoView
                        Case Break
                    Case (sId = "navigate")
                        Send NavigateToPage of oWebApp sParam btNewTab
                        Case Break
                Case End
            End_Procedure
        End_Object
    End_Object
End_Object
Special in the oWebDocumentationBox object is the psHtmlId setting and the OnClick event implementation. The psHtmlId is set to make it possible for the web designers to style the content of the HTML text stored in the Documentation table. The OnClick event is fired if the user clicks a anchor element in the HTML text that contains a data-serveronclick attribute. Check out the documentation of this setting within the DataFlex Web Application Framework documentation.
Finally this solution page contains two buttons below the treeview that would bring the user to the websites for Visual Report Writer and Visual DataFlex.

Report Data Sources

Development Team — Sun, 24 Feb 2013 16:47:04 GMT

This blog contains information on choosing and changing the data source in the Data Access report writer.

Data Source?
During the creation of a new report a data source needs to be selected. A data source makes it possible for the report writer to access the rows of data in tables. Currently 3 data source categories are available; DataFlex, ODBC and RDS.

DataFlex
For the DataFlex database the data source is either the list of tables (usually named filelist.cfg) or individual tables.

Opening a workspace (.ws / .sws) is the best way to select tables from the data source. Selecting a workspace automatically loads the paths to the data (DataPaths) as well as the reference to the list of tables (usually filelist.cfg) as this is defined in the .ws file. If the workspace' data paths contain multiple folders or if the filelist is not in the same folder as the data files the environment is setup correctly automatically. Of course assuming the information in the .ws file is correct.

If there is no workspace available the best and most used option is selecting a list of tables (filelist). The data source is then set to the list of tables (usually filelist.cfg) and opening tables relies on the folder the list of tables file is in. If the files are in multiple folders or the filelist is in a separate folder this option results in having difficulties to add tables to the report. That's why a workspace is a much better option.

Selecting individual tables is really meant for those situations where the tables are not listed in the list of tables or where tables need to come from multiple folders and these folders are not all listed in the workspace file. Or where the same table appears multiple times in different folders. The first selected table will be set as the data source. The biggest downside of this method is that more work needs to be done if the paths are changing as each table needs to be re-connected.

Besides the previous 3 options it is possible to select an INT file. INT file are used to connect a DataFlex application to an foreign database such as Microsoft SQL, DB2, Pervasive.SQL etc. The use of INT files for the report writer offers a flexibility of having to define one report and work with different databases for each of the deployed situations. However, the biggest downside of this method is SPEED! Using an INT file is much slower and I wouldn't want that. With an INT it is impossible for the report writer to use SQL optimalizations. With the ability to change a data source on at application integration level it is not an option you need.

ODBC
An ODBC data source is located via a DSN (Data Source Name) reference (User, System or File DSN) or via DSN less connection as the data source. User, System or File DSNs are maintained via the Microsoft ODBC Manager. Before you can use one of these for a new report the DSN needs to be created. After selecting the DSN its name will be stored in the connection string. In a DSN less connection the connection string contains all the necessary information (such as driver, user and password information, database etc) to access the database and its tables, views or stored procedures. Each ODBC driver has its own keywords and specifications.

RDS
The third category is RDS. This abbreviation stands for Runtime Data Source and all table descriptions are made during the report definition and stored in the report. No external references are present. The use of RDS only make sense if the report is used via integration and therefore it is not an option available in the standard edition.

Execution
A report can be ran from the designer (developer or standard edition) or as part of an application. This is called report integration. The developer edition contains the classes to make integration possible and allows the owner of the license to distribute the reports with a fat client application. The next paragraph deals with this. If the report is distributed and the end-user has a developer or standard edition of the report writer they can (often need) to change the data source in the reports and re-save the reports. After a change of the data source it is always wise to check the database to correct mismatches between the database information stored in the report and the real information. If the report uses an ODBC database and a DSN name is stored it is not needed to change the data sources of the reports, just make sure the DSN contents is correct and the table layout matches the stored information.

Integration
If the report is used as part of an application it is most likely that the application does not use the same data source information as what is stored in the report when this was created or updated. A report stores the data source name (location) to know how to get access to the data and to be enable the option to check the structure of the tables against what was know when the report was created. The data may even be retrieved from a location independent of the application.

ODBC, RDS or DataFlex?
Does it matter what data source is used in the report? Yes it does matter as you need to do more or less (or nothing) to change.

DataFlex
Lets first focus on the usuage of the DataFlex database. If the data files are located in the workspace of the application and there is just one set of tables with the same name the report class does all the data source replaces based on a property named pbAutoLocateDFFiles.

If the data source is a list of tables file (this is a file whose name is seen as the filelist (see function IsFileList)) the filelist name of the workspace is taken and replaced at execution time. If the workspace does not have a filelist (DF_FILELIST_NAME attribute is blank) an error is reported but as far as I know each DataFlex application MUST have a list of tables file.

If the data source is a table name (so individual tables were selected for the report) the name of each table is retrieved, the path is stripped off and an attempt is made to find a file with that name in the workspace paths via an internal function named FindTablePath. This function enumerates all paths in the workspace' datapath (psDataPath property). If any of the tables cannot be found an error is returned.

This automatic path correction does work for the main report and all sub-reports and nested sub-reports. As might not be known, the DataFlex report writer can have an "unlimited" number of sub-report levels.

If the workspace contains multiple tables with the same name (different folders) the automatic path correction might not work (depends on the workspace settings). In that case code needs to be written by the developer that does a similar activitiy as the auto locate of DataFlex files but corrected to the workspace. Study the private AutoLocateDFFiles method for the code that needs to be written.

ODBC
If the datasource is an ODBC datasource the change of data sources at integration level might not need to be done at all. It depends on what technique is used. In a DSN less connection it is probably needed to change the connection string contents while a user, system or file DSN only require the presence of the DSN and the correct configuration of the DSN. If the report integration code is generated by the integration wizard code can be generated (ChangeODBCDataSource) in which the data source can be modified. Modification means the change of psDataConnection and psDatabaseName.

In the following code snippet the connection string of the report and each of its possible sub-reports is read and changed. The report in this case used an DSN less connection string. The server name is replaced with information from an open table in the system. If the login information requires a change the code can be extended.
Code:
Procedure ChangeDatabaseConnection Handle hoReport    
    String sServerFromTable sConnectionString sServerFromReportDatabaseConnection sSubReportId
    Integer iSubReports iSubReport

    Get TableConnectionString Customer.File_Number to sConnectionString
    Get ServerFromConnectionString sConnectionString to sServerFromTable
    If (sServerFromTable <> "") Begin
        Get psDatabaseConnection of hoReport '' to sConnectionString
        Get ServerFromConnectionString sConnectionString to sServerFromReportDatabaseConnection
        Move (Replace (sServerFromReportDatabaseConnection, sConnectionString, sServerFromTable)) to sConnectionString
        Set psDatabaseConnection of hoReport '' to sConnectionString
        Get SubReportCount of hoReport '' to iSubReports
        Decrement iSubReports
        For iSubReport from 0 to iSubReports
            Get SubReportId of hoReport '' iSubReport to sSubReportId
            Get psDatabaseConnection of hoReport sSubReportId to sConnectionString
            Get ServerFromConnectionString sConnectionString to sServerFromReportDatabaseConnection
            Move (Replace (sServerFromReportDatabaseConnection, sConnectionString, sServerFromTable)) to sConnectionString
            Set psDatabaseConnection of hoReport sSubReportId to sConnectionString
        Loop
    End
End_Procedure

Function DriverIndex String sDriverName Returns Integer
    String sCurrentDriverName
    Integer iDriver iDrivers

    Get_Attribute DF_NUMBER_DRIVERS to iDrivers
    For iDriver from 1 to iDrivers
        Get_Attribute DF_DRIVER_NAME of iDriver to sCurrentDriverName
        If (Uppercase (sDriverName) = Uppercase (sCurrentDriverName)) Begin
            Function_Return iDriver
        End
    Loop

    Function_Return 0
End_Function

Function TableConnectionString Handle hTable Returns String
    Integer iDriver iConnections iConnection
    String sConnectionString sConnectionID
    
    Get_Attribute DF_FILE_LOGIN of StopPln.File_Number to sConnectionString
    If (Uppercase (Left (sConnectionString, 8)) = "DFCONNID") Begin
        Get DriverIndex "MSSQLDRV" to iDriver
        Get_Attribute DF_DRIVER_NUMBER_CONNECTION_IDS of iDriver to iConnections
        Decrement iConnections
        For iConnection from 0 to iConnections
            Get_Attribute DF_DRIVER_CONNECTION_ID of iDriver iConnection to sConnectionID
            If (sConnectionString contains sConnectionID) Begin
                Get_Attribute DF_DRIVER_CONNECTION_ID_STRING of iDriver iConnection to sConnectionString
            End
        Loop                       
    End
    
    Function_Return sConnectionString
End_Function

Function ServerFromConnectionString String sConnectionString Returns String
    String sServerPart
    Integer iServerPos iSemiColonPos

    Move (Pos ('server=', Lowercase (sConnectionString))) to iServerPos
    If (iServerPos > 0) Begin
        Move (Pos (';', sConnectionString, iServerPos)) to iSemiColonPos
        If (iSemiColonPos = 0) Begin
            Move (Length (sConnectionString)) to iSemiColonPos
        End
        Move (Mid (sConnectionString, iSemiColonPos - iServerPos, iServerPos)) to sServerPart
    End

    Function_Return sServerPart
End_Function
RDS
With an RDS data source there is nothing that can and should or needs to be done in changing. The table description is stored in the report and no external references are present that require actions to be coded.

Summary
The correct selection of a data source is important and it is not difficult to change a data source at integration level, only a bit of time to study how the system is build is needed. We are open for suggestions to improve the system.

How to Create a Checkbox in Your Report?

Development Team — Fri, 01 Feb 2013 16:20:15 GMT

Often a picture says more than a 1000 words... Well, a checkbox is not a picture but if well used it can draw the same kind of attention to the reader of the report results. Assume you want a checkbox on results of your Visual Report Writer report, how could you do this?

First look at the following screenshot that shows what I mean with using a checkbox in your report output.

If the phone number is a free number, that is when it starts with (800), the report shows a checkbox. The report is based on the vendor table in the standard order entry example that comes with Visual DataFlex.

To create this I made two functions in Visual Report Writer. One function that results in printing the X and one function that manages the line style of the border. The line style of the border? Yes, to create a checkbox I decided to place a border on all four sides of the object if the 'X' is printed.

In the report create a function named CheckboxBorderStyle and place the following code in the function body:
Code:
if (left ({Vendor.Phone_Number}, 5) = '(800)') then
    return vrwBorderSingle
else
    return vrwBorderNone
end
It says to return the constant for a single border if the '800' value is in the first 5 characters of the phone number. The advantage of creating one function for returning the border style is that we can easily replace it in one location.

The second function to create is called FreePhoneNumberCheckbox and contains the following code as function body:
Code:
if (left ({Vendor.Phone_Number}, 5) = '(800)') then
    return 'X'
else
    return ''
end
This function is really put in the report on the body line. If you would print the report now it would print an 'X' or nothing. So a little formatting is required to make it show as a checkbox. For that open the field properties of the object added the report and change the following things;
1. Make the text centered (horizontal alignment)
2. Make the text font bold (font tab-page)
3. Use the CheckboxBorderStyle for the four border style functions on the border tab-page.

Each border style function is the same. The contents should be:
Code:
return {@CheckboxBorderStyle}
and the field properties dialog looks like:

Finally, I will enclose the report to this blog. Note that if you want to run the report you need to change the path to the data-source. If you don't do that you get a file not open error.

Attached Files

A Line Around Your Report

Development Team — Sat, 18 Aug 2012 08:04:31 GMT

In this short blog I want to show you how you can make a line around the output of your Visual Report Writer report. Visual Report Writer does not have a box - or a line - object that can go over multiple sections. The development team has granted to need for such a feature and will implement this via a page layer. In general: objects should not go over sections.

A Fake Box
What is a box? A box is a rectangle consisting of two horizontal and two vertical lines. This means it is possible to start the top of the box in the page header with two vertical lines parts and the top horizontal line part. In the page footer the bottom horizontal line and two bottom vertical lines can be created. In each of the sections between page header and footer you can create the vertical lines (left and right).

If you place vertical lines in sections that can extend in height because of the content (text object with variable height or sub-report) you can - since version 1.5 - select the variabele height checkbox for these lines as well. The amount of height extension for the variable height objects will then also be used to extend the lines.

If the body section and - if you use groups - does not reach the page footer you can create a filler section. Draw the vertical line in there and your report extends. The "Filler Sections" report in the enclosed zip file shows how to make use of filler sections and line elements.

We recognize this is a lot of work to create a box around your page and the page layer that is in the planning will be a much easier solution.

Unsupported Trick...
It is possible to make a box in a section - for example the page header section - and via field properties enter a height that covers the whole print area. The box will be shown at design time covering the whole report design area. Create this box at the end of your report design as the Visual Report Writer designer will want to extend your section (e.g. page header section) in height to accomodate te space for the box. Creating a box this way is not the correct solution and while it work up to version 2.1 it might not work in the future and page layers is a much better solution. The report "CustomerList Boxed" shows how I used this trick.

Attached Files

Saving Error Messages in Text Form

Development Team — Thu, 19 Jul 2012 18:24:19 GMT

Did you know that in Windows most error messages can be converted to text using the Copy (Ctrl+C) function? This is a tremendous, yet somewhat hidden and thus underutilized, feature of Windows.

Most importantly, it works with standard Visual DataFlex errors and all messages based on the standard Windows MessageBox interface (see Message_Box, Info_Box, Stop_Box, etc. in the Visual DataFlex help index).

So, for example, if you run the Order sample workspace and try to delete a Vendor, a message box pops up asking you whether you are sure that you want to delete the record. If you click Yes, you will get an error message (provided the vendor being deleted has inventory items related to it, which all or most of the sample vendors should):

If you now press Ctrl+C on your keyboard, the whole message box is copied to the clipboard as text. If you paste it, the result looks like this:

---------------------------
Error
---------------------------
Cannot delete - related records exist

File: 21 - Vendor
---------------------------
OK
---------------------------

The main benefits of this error reporting method are:
It is quick and easy to do

It is smaller (in bytes) and simpler to transmit than a screenshot/image

The information is complete and human error (typo) free

The information is ready for copying and pasting, again without human errors, into search engines, forum searches, etc.

The above was an example of a user (handled) Visual DataFlex error message. Just as important to you as a developer are unhandled errors (see the ErrorSystem class help in the Visual DataFlex help), whether they happen to your end-users or to you during development.

For example:

If I add a button to a view that sends Request_Assign (a method of the DD class) to a view object (yes, this is a contrived sample, but it serves my purpose) and click on that button, I will get the following unhandled error message:

If I now click on the provided Copy button, the whole message box is copied to the clipboard as text. If I paste it, the result looks like this:

C:\Visual DataFlex 17.0 Examples\Contact Management\Programs\Contacts.exe
Invalid message. MSG_REQUEST_ASSIGN

Error: 98

MSG_ONCLICK (4600) - OBUTTON1 (438) - at address 73367
MSG_COMMAND (648) - OBUTTON1 (438) - in native code
[start] - at address 77792

Once again, the full error message, this time including the full call stack leading up to the error, since it is an unhandled error, is available in simple text form, for emailing to support, copying into any search I want, etc.

Recursive Use of Structs and Arrays

Development Team — Sun, 08 Jul 2012 15:15:10 GMT

In a recent post at the forums the question was given how to make use of a recursive struct. Years ago I wrote a training manual about Structs and Arrays and in one of the exercises the trainees had to write a routine that could read the files in a folder from a disk. A folder can have 0-N files and 0-N sub-folders. In this blog I will explain you how you can build the code to read the information using arrays and structs.

Nested Structs
Because a folder can have 0-N files and sub-folders we need a struct where a member refers to itself as a folder can have sub-folders. We need a struct where one of the members is an array of the same struct:
Code:
Struct tFolder
    String sFolderName
    String[] sFiles
    tFolder[] Folders
End_Struct
Without the array marker on the third member the code would create a infinitive recursion which is of course not allowed. A struct is a data definition as integer, real, number, decimal, pointer etc. so to use the struct you need to create a variable.
Code:
Procedure Test
    tFolder MyFolder
End_Procedure
The variable is not an array itself as we can start with a single folder (e.g. c:\program files). Accessing a nested member can be done in static mode or by reference. If you would need to write in a static mode the code would be:
Code:
Move 'Visual DataFlex' to MyFolder.Folders[0].Folders[0].sFolderName
While it works it is quite clumsy and if the depth is dynamic writing code this way cannot be done.

The Solution
The solution lies in making use of recursion and referencing members by reference. Recursion in writing a procedure or function that calls itself and by reference in passing a member of the struct variable via its by reference marker to the recursive routine.

If you call a procedure or a function is a recursive way you need to have a way to terminate the recursion else you will run out of stack space and get a very nice GPF (General Protection Fault). For the exercise to read files and folders from a disk the operating system determines the number of recursions as at a certain moment you won't have sub-folders anymore. Hopefully you reach that point before you run out of stack space or - in general - run out of memory. Memory as you should know is not unlimited, not in real, not on a 32 bit application.

Read Disk Information
Lets take a look at how we can read disk information into the variable. We can make use of the build in sequential I/O statements of Visual DataFlex (Direct_Input, ReadLn, SeqEof etc). This means that the following code can be used to read the files of a folder:
Code:
Procedure ReadDirectory String sDir
    String sDirEntry 
    Integer iChannel iFolderItem
    tFolder MyFolder
    
    Get Seq_New_Channel to iChannel
    If (iChannel <> DF_SEQ_CHANNEL_NOT_AVAILABLE) Begin
        // Open an input channel with the DIR: device plus the directory path
        Direct_Input channel iChannel ("DIR:" + sDir + "*.*")
        While (not (SeqEof))
             // Read an entry from the directory
             Readln channel iChannel sDirEntry
             // Only continue if you have not found end of the directory
             If (not (SeqEof)) Begin
                 // If the entry is not a folder
                 If (Left (sDirEntry, 1) <> '[') Begin
                     // Store the name of file
                     Move sDirEntry to MyFolder.sFiles[SizeOfArray(MyFolder.sFiles)]
                 End
                 Else Begin
                     // Only store folders that are not [.] and [..]
                     If (Left (sDirEntry, 2) <> '[.') Begin
                         // Retrieve the current number of folders
                         Move (SizeOfArray (MyFolder.Folders)) to iFolderItem
                         // Add the directory (plus full path) to the array of folders. Without the square brackets!
                         Move (sDir + Mid (sDirEntry, Length (sDirEntry) - 2, 2)) to MyFolder.Folders[iFolderItem].sFolderName
                     End
                 End
             End
        Loop
        // Close the I/O channel
        Close_Input channel iChannel
        // Release the channel
        Send Seq_Release_Channel iChannel
    End
End_Procedure

Send ReadDirectory "c:\program files\"
The above code reads the files in a folder in the string array member names sFiles and the names of the sub-folders in an array member named Folders. Now how to continue reading? How to get the contents of the nested folders? Add the following code between the last end statement and the End_Procedure
Code:
// Determine the number of foldersMove (SizeOfArray (MyFolder.Folders)) to iFolderItems
Decrement iFolderItems
// Loop through the folders and enumerate each of them recursively
For iFolderItem from 0 to iFolderItems
    Send ReadDirectory (MyFolder.Folders[iFolderItem].sFolderName + '\')
Loop
The above code will loop through each of the sub-folders and call itself for this sub-folder. In the iteration it might find sub-folders again so the ReadDirectory can be easily called often. As a tip, don't start "c:\" or even "c:\program files\" but something more simple such as "c:\Visual DataFlex Examples\" or "c:\tmp\" (f you have a temp folder on the root of your disk).

Now, how do you get everything in one array with information as above code will build up arrays but as they are not kept somewhere you will loose the information when the recursion ends. The "trick" as mentioned before is by passing a variable via by reference. For this change two things. The first change is the line where the ReadDirectory is declared. Change it into:
Code:
Procedure ReadDirectory String sDir tFolder BYREF MyFolder
The second change is where the ReadDirectory is invoked. Change that/those lines into:
Code:
Send ReadDirectory (MyFolder.Folders[iFolderItem].sFolderName + '\') (&MyFolder.Folders[iFolderItem])
This means that the initial call needs to use a variable. Change:
Code:
Send ReadDirectory "c:\program files\"
into
Code:
tFolder MyFolder
Send ReadDirectory "c:\program files\" (&MyFolder)
Each time the procedure calls itself a reference to a deeper member is passed. So the first time MyFolder is passed, the second time MyFolder.Folders[N], the third time MyFolder.Folders[N].Folders[N]. On return of the original instruction the array of tFolder contains all files and sub-folders of the passed root folder.

Count Files and Folders
To count the total number of files and folders in the MyFolder variable you can make use of a procedure that takes three arguments by reference. The first is the MyFolder array, the second a reference to an integer for counting files and the third a reference to an integer to count the folders. The method and the call can be written as follow:
Code:
// This routine enumerates the recursive struct to count the files and folders
Procedure CountFilesAndFolders tFolder ByRef MyFolders Integer ByRef iFiles Integer ByRef iFolders
    Integer iFolderItems iFolder

    // We don't need to count the items; there is a nice build in function for this
    Move (iFiles + SizeOfArray (MyFolders.Files)) to iFiles
    // Get the number of folders first and store this in a variable because we need to enumerate from there
    Move (SizeOfArray (MyFolders.Folders)) to iFolderItems
    // Add the folders (not decremented) to the number of folders
    Move (iFolders + iFolderItems) to iFolders
    // Decrement the count because we loop from 0 to N
    Decrement iFolderItems
    // Loop the contents of the array recursively.
    For iFolder from 0 to iFolderItems
        Send CountFilesAndFolders (&MyFolders.Folders[iFolder]) (&iFiles) (&iFolders)
    Loop
End_Procedure
As you can see this method calls itself recursively as well. Because the array is passed by reference it does not take up more memory.

Conclusion
Once you understand that you can use recursion and by reference it is quite easy to make complex routines like this. The code won't execute as fast if you would write it in a 3rd generation language as 'C' but it is much easier as you don't have to do all the memory operations and let the DataFlex 4GL take care of that.

Happy coding, hope this will contribute to keep you busy with Visual DataFlex!

Fonts in Visual DataFlex

Development Team — Mon, 28 May 2012 14:31:19 GMT

There are many aspects to the discussions about how the new font system in Visual DataFlex works when compared to the old. This is actually a complex topic with a lot of different perspectives � so this blog might be on the long side. I promise to do my best not to ramble (too much).

I�ll start by stating that the main goal in wirting this is to get everyone to a common understanding of the way fonts work: how the old system functioned, what we changed in moving to the new system and why those changes produce the current behaviors. We can then see what the options are for the product and developers; now and in the future.

Fonts are the driving factor in how things should appear (and scale) on Windows. Well, at least they are supposed to be. Rather than go through all the details on fonts in this document, it would help if you read the new section in the 17.0 help under �Windows Application Development | Using Fonts�. Go ahead; I�ll wait until you�re done�

Back already? You did read it, didn�t you? OK, onward. Let�s remember how the old font system works. All that good stuff you just read about everything adjusting and scaling based on font changes was not really functioning. Visual DataFlex was essentially a single font (MS San Serif) single size (8) system for many years (at least as far back as Visual DataFlex 7 and probably earlier). The default sizes for everything and the way we all (or at least most of us) designed our applications were almost solely based on the singular way our fonts worked best. Even for those of you that adopted other methods of dealing with fonts the underlying screen designs and default sizes play a large role in your applications today. For instance, have any of you adopted a form height ofother than 13 (our default since DataFlex for Windows)?

The existing font system had behaviors that have always been there, but the change to a significantly different (and larger) system font in Windows Vista was when those behaviors started to get more noticeable. We actually looked into changing the font system during the 12.x and 14.x projects (more on that later). The emergence of high-DPI displays on laptop computers was the real tipping point � we felt something had to be done to enable developers to create DPI-aware applications. The questions was; what and how?

The existing Visual DataFlex font system did not use the Windows system font. It had its own behaviors that weren�t based on Windows standards, was hard to manipulate and was not DPI-aware (so it would not scale). To complicate matters, COM controls (including, but not limited to, the CodeJock controls we use for menus and grids) do use the Windows system font and have behaviors that are based on Windows standards and are (or at least can be) DPI aware (and thus,scale). In addition to COM controls, if your application also uses Windows controls at a more basic level (popping up Windows Explorer dialogs, message boxes, etc.) all those will have their font and sizing based upon the host system font as well.

Adopting the Windows standards (using the system font and adopting DPI awareness) was the only logical choice.

The way the font system is supposed to work in Windows is that you specify a typeface (eg. Segoe UI) and a font point height (eg. 9) and, essentially, Windows does the rest based on dialog units. Windows then becomes the ultimate judge on how everything sizes. There were really two components to the change we needed to make; Visual DataFlex must use a font correctly (meaning that everything would scale properly off the combination of typeface and font point height) and Visual DataFlex must use the system font of the host system. This would mean that Visual DataFlex applications that used the new system would change from applications designed adn run mainly around MS Sans Serif 8 to applications that would run on Tahoma 8 or Segoe UI 9 (the two predominant system fonts). But it�s important to understand that using the new system would allow the application to adapt to any typeface and point height used by the system automatically.

I�ll use the venerable Order Entry main screen to illustrate the journey. We�ll start with XP (at both 100% and 125%) and then look at Vista / Windows 7 later.

The Old Font System on Windows XP

The image above shows that Visual DataFlex 16.1 on XP at 100% uses a combination of MS Sans Serif (VDF) & Tahoma (CJ) fonts because the Codejock grid is an example of a COM control that is (properly) using the system font. This illustrates one underlying issue; that the native VDF controls were using a different font fromCOM controls. Many developers lived with that difference because the two fonts were similar and the same size.

When we run the same application with Visual DataFlex 16.1 on XP at 125% (above) we see that the MS Sans Serif and Tahoma fonts are still used, but only the Codejock grid (Tahoma) is DPI-aware and scaled properly. Before high-DPI displays became commonplace, developers would just try to make sure their customers didn�t run with anything other than standard sizes. Those days are over�

The Start of the Change

We then made changes necessary to start using the Windows system font in all Visual DataFlex controls so that they would exhibit the same behaviors as the CJ COM controls. This is where we brought in the new Font Point Height support (necessary to make it all work) and designed the new system in such a way that the old font system behaviors could be preserved for compatibility.

After the changes were made (sounds like waving a magic wand, doesn�t it?) the first thing we verified was that we got the same results between 16.1 and 17.0 when the new font system was turned off. We did, so I�m not going reproduce any screen shots with the 17.0 / old font system combination.

Trust me :rolleyes:

The New Font System (Take 1) on Windows XP

OK, so we turned on the new system (and really appreciated the design of setting a single property in the application object) and continued�

The image above shows Visual DataFlex 17.0 on XP at 100% using the new font system. The fonts are now all correctly using Tahoma 8, but you can see that there are issues with the horizontal sizing and spacing. All you have to do is look at how much the overall horizontal size of the view was reduced (while the vertical remained the same) to know that something has changed significantly.

This kind of sizing problem is the similar to issues we had in the past when we tried to change the font system. While even a view as simple as Order shows the horizontal sizing problem, the impact is much more profound as the layout is more complex and tightly-packed. Putting the horizontal sizing issue aside for a moment, we wanted to make sure that the changes made not only allowed the application to use the system font consistently but then would also scale correctly on high-DPI settings.

The image above shows Visual DataFlex 17.0 on XP at 125% using the new font system. Again we see that we are picking up the system font (Tahoma) everywhere and it is indeed scaling properly to 125%, but there is still an issue with the horizontal sizing and spacing.

Rather than show you how these behaviors manifested on Vista / Windows 7, I�ll stick with XP for now and explain what we did and what we found. We�ll then look at Windows 7 from top to bottom.

As mentioned earlier, the combination of typeface (now Tahoma) and font point height is, when working correctly, supposed to control everything at this point. Using the new system, based on Font Point Height, that was now true vertically, but not horizontally � so where was the problem? To understand what we found, we have to dive one layer deeper into how fonts really work.

While it�s true that everything is positioned using those �universal� dialog units, you eventually need to translate those dialog units to actual pixels to paint the control on the screen. This is actually the point at which the �magic� that makes things look good with different fonts and scaling properly happens � you do calculations on how the dialog units (the universal measurement) map to actual pixels (based on the specific settings of the system you are running on) and off you go.

So, we looked into how that calculation was being done. That code hadn�t changed from the way it worked in the old font system and had been in place for many, many years. In fact, it still worked great when the font was �our� system font (MS Sans Serif) and we couldn�t see anything obviously wrong with it. So, we went looking for how Microsoft does the conversion from dialog units to pixels and the formula was different from the one we use. Rather than try to adjust our formula we just decided to use the Microsoft calculation! So, we put the Microsoft recommended calculation in place (it sounds so easy when you say it that way) and ran the tests again�

The New Font System (Take 2) on Windows XP

Now, the image above shows Visual DataFlex 17.0 on XP at 100% using the new font system AND the Microsoft approved calculation for converting dialog units to pixels. We can see that the system font is properly used throughout and that both the vertical and horizontal scaling and positioning are �correct�. I quote the correct reference because even though the layout is properly resizing in both the vertical and horizontal plains we need to understand that the calculations that anyone (ourselves or Microsoft) uses are based on choices. What is the tallest character in the font? Should the horizontal plain use an �average� character width and how should that average be calculated? Should we �leave a little extra room� and, if so, how much? There is no such thing as a layout that will perfectly scale to all possible fonts and DPI settings � you design your application layout for the best look most of the time.

So, how will the Microsoft calculation hold up at 125%?

Much better! The image above shows Visual DataFlex 17.0 on XP at 125% using the new font system and the Microsoft approved calculation for converting dialog units to pixels. Even at 125% most everything is scaled properly and positioned well. It isn�t perfect, but what is?

So, it looks like we found that the calculation we had been using for many years didn�t do well when the fonts were changed (even �just� from MS Sans Serif 8 to Tahoma 8) and so the horizontal didn�t scale well at all. We dug deeper into the two calculations and determined that the difference was due to truncation (our old calculation) vs. rounding (the Microsoft calculation). It just so happened that for MS Sans Serif 8, there was no real difference between the calculations, regardless of which method they use. It actually depends on all the aspects of the font to determine if the difference between a truncate or round will tend to make things bigger or smaller than the original layout.

Now, how does all this work in Windows 7, where the system font is even less like our old standard font? Remember, instead of going from MS Sans Serif 8 to Tahoma 8 we�re going to Segoe UI 9. Let�s start with how Visual DataFlex 16.1 looked�

The Old Font System on Windows 7

The image above shows Visual DataFlex 16.1 on Windows 7 at 100% and uses a combination of MS Sans Serif 8 (VDF) and Segoe UI 9 (CJ). As noted earlier, because Vista and Windows 7 not only changed the typeface, but moved to a larger font point height, the differences between the VDF controls and COM controls are more noticeable. The font difference is even more profound at higher DPI settings�

The image above shows that Visual DataFlex 16.1 on Windows 7 at 125% uses the same combination of MS Sans Serif and Segoe UI, but just as with XP, the high-DPI scaling is only happening correctly with the COM controls. In addition, since the system font was already larger in the grid, the difference when it scales up to 125% (and the VDF controls don�t) is stunning (but not in a good way).

The New Font System on Windows 7

Using the same 17.0 change (before updating to the Microsoft conversion calculation) we saw the following behaviors on Windows 7:

Visual DataFlex 17.0 on Windows 7 at 100% using the new font system (with our legacy calculation) looks fairly reasonable, but you can still notice that the dialog didn�t properly scale horizontally. The design of this particular view is actually reasonably benign so its impact is misleading. This does illustrate one of the complicating factors of fonts and scaling: What looks acceptable in one instance can be just horrifying in others.

You might look at this Order view and say �Hey, with Segoe UI 9 the old calculation is fine (perhaps even better) than the Microsoft calculation, so why didn�t you stick with it?�

In response I give you Exhibit A�

The image above is running on Visual DataFlex 16.1 on Vista at 100%. This is a pretty simple test view that uses what most would consider pretty basic control positioning found in a more complex �main� screen. Available real estate for the layout is tight, but works. Here is the same image running with the new font system but our legacy calculation�

The image above is running on Visual DataFlex 17.0 on Windows 7 at 100% with our legacy calculation from dialog units to pixels. It�s pretty amazing how quickly something that is less than optimal (too much additional white space) can turn into something that is completely unacceptable. I�ve lost the overall aspect ratio of my meticulously designed view, Customer Number is completely cut off, I now have to scroll my tab pages where they used to fit with plenty of room and the labels for my important data are at best cramped and at worst cut off.

Imagine the volume and tone of the discussion we'd be having if this (or worse) happened to your applications?

So, how does this view look with the change to the Microsoft calculation?

The image above shows the test view running on Visual DataFlex 17.0 on Windows 7 at 100% with the Microsoft calculation.

Everything is as it should be � no clipping of labels, no scrolling of tab pages and my overall aspect ratio is essentially as I designed it.

I could go on and on with all kinds of layouts shown in all kinds of host environments but I think we�ve provided enough information and illustrations so that we�ve accomplished what we set out to: that common understanding of what was, what changed (and why) and that the new system, while certainly not perfect, is just as certainly not �broken� (just different). It is doing exactly what is it designed to do and doing so according to Microsoft�s own recommendations.

Additional Observations and Clarifications

Some of you noticed that the "extra" width is most prominent in date and numeric forms, especially those with prompt buttons. All the controls are actually "scaling up" based on the font is use the same way - there is nothing different done in the size calculations (50 dialog units wide is just that). So why is the effect more prominent with certain types of forms? First, the date is more narrow when compared to the average character width. Remember that the horizontal sizing of the controls are based on the dialog units times this "average" width. Where the data displayed is above the average (like a string of Ws) it will have to scroll, where it is below (like a string of 1s) it will not take up the full amount. Prompt buttons add to this in that they used to be just a fixed size (and fairly square) and now they are having their size properly calculated according to the same formula as everything else (which actually makes them a bit taller than wider).

Some of you have noticed that the new font system �works� on Windows Server 2008. This is because the Windows system font on that platform is 8 point, not 9 (it is also Tacoma and not Segoe UI - but the more important factor is actually the font point height). That common size between the design environment and the new environment (even though the typeface is changing) is what makes the result more pleasing. Any platform that has a system font of 8 point (Windows Server, XP, anything running Windows Classic as its theme, etc.) has a good chance of giving you a pleasing result, even though it is going through the same conversions and calculations; everything is indexed off the font point height. If you switch to any font that is the same basic size as your design environment the impact of control resizing is reduced. Of course, it is possible that a font could be so much wider than MS Sans Serif that even at 8 point it would have the same kind of sizing impact as Segoe UI 9 does.

All kinds of numbers are being thrown around about the percent difference - ranging from as small as 12% to as high as 20%. I'm certain we can all find examples to prove any particular point we wish (43.62 percent of all statistics are manufactured, you know) - but if we go back to my test view and use the overall size of the view (remember that everything scales up or down based on font size) as a reasonable example we can see that using the new formula we gained 14% vertically and 17% horizontally. In a perfect world we would have gained 14% in both, but the change in formula accounted for that extra gain in width.

Please keep in mind that my goal of trying to reach a common understanding isn�t meant to diminish any developer�s position or concern for the look and feel their application; it just gives us a better platform from which to have a discussion based on all the information.

But Why Didn't You... ?

Of course, understanding the behvaiors of both the old and new calculations leads us to the inevitable �Ok, so on platform A do this (instead of doing that) and then tweak it this way (but only when this happens), etc.� and on and on.

We�re certainly willing to entertain suggestions and continue to refine the new font system into the future (just like we do with all refinements) � but there is no silver bullet, no �one size fits all� or �correct� fix.

Take the two calculations we have at hand (our legacy calculation and the Microsoft recommendation) as an example. Our old calculation in the best of circumstances looks cramped but more often than not is simply unusable. Microsoft�s calculation is only sometimes a bit cramped (but never actually crosses into text clipping) and is often a bit too generous with the horizontal white space.

If you must pick between "more often than not simply unusable" and "often a bit too generous with horizontal white space", which would you choose?

The Microsoft calculation also is what Microsoft uses at the operating system level (but not always or consistently at the application level) so as Microsoft changes in the future using their calculations should put us (and you) in the best position to adapt.

What About Text Positioning?

If we put the horizontal scaling aside for a moment, there is another behavior that developers have commented on: how the text is positioned inside forms. As I mentioned back near the start of this discussion; we adopted a default height for the various forms of 13 dialog units. The decision was made so long ago it�s not clear if this was the Microsoft recommendation at the time (or even still is) but we have all (or most of us) designed our applications around that particular metric for as long as any of us remember. Developers have commented that, when using the new font system, they don�t like the increase in white space between the bottom of the characters and the bottom of the form. This behavior (a bit more space below than above) has always been there � it�s just that the new font size of 9 makes it more pronounced then is it at 8. This isn't even a byproduct of the change in calculation - even with our old formula the vertical white space is just as imbalanced.

There are really only two possible ways to address this concern; reduce the vertical size of the forms (probably from 13 to 12) or to try to center the text vertically in the control (so that the white space is balanced). We�ll look into both of these options for the future and seek your feedback on them.

Are You REALLY Sure About All This? Microsoft Applications Look Fine!

Lastly, some of you have commented that Microsoft applications don�t exhibit the same overall size and spacing behaviors and that those differences would indicate that �the problems� lay with Visual DataFlex. Again, this isn�t as cut and dried as it may seem. First, many (most) Microsoft applications don�t actually use the Windows system font. Even those that do often manipulate the size back down to 8 on Vista and Windows 7.

Why does Microsoft make these choices and implement some (many, most...) of their applications differently? We could speculate about that all day long but the reality is I simply can�t tell you what their motivations are.

What I can tell you is that if you want to see if the overall sizing is really based on the Windows system font it can be done by examing the size of text and buttons, etc. When we fisrt saw the sizes of the controls on Windows 7 (because of that font point height difference) we wanted to make sure Visual DataFlex was really �correct� based on the host system. We compared them to things like Windows message boxes. If you do so, you�ll see that we are indeed using the correct metrics.

What Now?

Some may think that using the sizing and spacing determined by the Windows system font is not always desirable but we believe it is the best option available. But developers do have choices:
Continue to use the legacy font system where it provides you and your customers with the best path (for as long as that remains true).

Adopt the new font system without going through a lot of redesign of your layout � this will work for some of you more often than others. Yes, the horizontal sizing may be different than it was when you designed it, but remember what it is doing (automatically) and why.

Adopt the new font system and make adjustments to the layout of parts of your application. This will be a bigger job for some thant others and you may want to limit this investment by only making it to the main application screens (seeing "the customary" sizing layout in less-used parts of the application is not as pressing a need). Keep in mind that when designing applications to be DPI-aware there is no perfect layout, only one that you determine better presents your application in those environments that you think are important.

Take more control over the use of fonts to determine your own standard (ala some Microsoft applications). You know that the new font system will actually allow you to do that morea easily and consistenly than the old. But you will have the ultimate responsibility to control not only the Visual DataFlex controls, but any COM controls you use (regardless of who provided them).

Thanks for taking the time to read through all this (there are so many elements at play) � I hope I didn�t meander too much�

An Error Has Occurred On The Server...

Development Team — Wed, 02 May 2012 07:26:55 GMT

This blog was created to tell you what to do when you get this error while starting a Visual DataFlex web application in particular Electos Studio.

The error
An error occurred on the server when processing the URL. Please contact the system administrator.
If you are the system administrator please click here to find out more about this error.An error occurred on the server when processing the URL. Please contact the system administrator.
If you are the system administrator please click here to find out more about this error.

What to do?
If you click any of the "here" links (they don't work in above text) you sometimes get useful information but not always. The shown error is the non-technical error message thrown by IIS to browser. You can configure IIS to throw the non-technical error or a more detailed error via IIS Manager. You can see this as a debugging option. The default from IIS is to show a non-technical error. To configure, open IIS Manager and navigate to the site or application that throw-ed the error. Double-click the ASP feature. In the list of ASP features open the debugging properties and set the setting "Send errors to browser" to True.

For Electos?
The specific error was shown when a user executed the "http://localhost/MyAppStudio" URL. After turning on the "Send errors to browser" feature the non-technical error was "The virtual directory for the Electos Studio had his 'Parent path enabled' setting turned off. This setting must be turned on for this component to work. Electos has changed the setting for you so you can use the Electos Studio.". After that the Electos Studio functioned correctly.

Exposure
Any web application - so not Electos only - might generate errors and it is up to you if you want to show the errors to the invoker or not. In general I prefer to see detailed errors and not hide information but turning on the IIS option might expose information that you better do not show to (malicious) visitors. It is up to you to decide. You can also turn it on to find the problem and turn it off again when the problem is fixed, so treat the option as a debug option.

Use of Runtime Data Source with Visual Report Writer

Development Team — Sun, 29 Apr 2012 16:13:04 GMT

One of the new features of Visual Report Writer version 2.0 will be support of a Runtime Data Source (RDS). A runtime data source is a virtual table that is defined during report definition while the data is supplied via integration. This opens the way to report of data that does not come from a "real" database. In this blog you will read how to make a report based on the filelist.

Report Definition
Start Visual Report Writer and press Ctrl+N for a new report (or choose New Report from the file pull-down, click the New Report button). In the wizard that is now started you navigate to the page where you select the data source. Double click "Runtime datasource" or single click the tree item and click the Open button. Both of these actions opens a modal dialog in which you have to enter a table name - you can have multiple RDS tables in one report - and define the columns of the report. For each column select the desired data type and if required specify a length and precision. For the example to be made in this blog enter "Filelist" as the table name and the following column names;

Column Name Datatype Length

Number Integer

RootName String 40

DisplayName String 32

LogicalName String 31

After all columns are defined click the OK button. Click the table add button in the wizard page that you return to (the table appears on the right list). On the next page select all four columns to be printed. Skip the pages for grouping, selection. Decide on what paper format you want to print the report (for example A4 or Letter, Landscape or portrait).

On return in the designer format the report a bit so that the headers of the columns are printed bold. Click the preview button or press the key (F5) to see results. Visual Report Writer will open a dialog in which you can enter some test data. Test data is persistent to the report (it will be stored with the report). If you are happy with the results, save the report as filelist.vrw and continue to report integration via the Visual DataFlex Studio.

Integration
To integrate a Visual Report Writer report you need to attach the library that can be installed during the installation of Visual Report Writer to your workspace. After attaching the library you will see two new items in the new view/report page of the new component dialog in the Visual DataFlex Studio. From the new icons select the wizard to make a start with the integration.

In the wizard select the new report. If you wish you can preview the report to see if you have the right report. The wizard page where you can select the columns for record selection you better skip because you will provide the data yourself and in most cases - and certainly with the filelist report - it is more useful to supply not more data than is needed to be printed. You can opt in for a sort order but in most cases you will provide already sorted data. Finish the integration by selecting the desired preview style and other options on the options page.

In the source code you will find two routines that are RDS related. The first routine is LoadRDSData. This routine collects all the RDS tables of the report and sends out a message (named AddRDSData) to really load the data. The LoadRDSData does not only process the main report but also optional sub-reports. While it is not very likely that a report based on RDS uses a sub-report with RDS data it is possible and allowed BUT it is also possible that the main report is based on a "real" database such as Microsoft SQL server and a sub-report that uses RDS.

The AddRDSData routine gets the RDS table name as argument and a level. The level parameter is increased for each nested sub-report and can be used - if needed - to make a distinction between two RDS tables that carry the same name but are present in a different report. Normally the code inside AddRDSData only checks on table name and not on level. In the very rare situation that a report has two (or more) sibling sub-reports all using RDS as data-source and all using the same table names you need to make a code change yourself as it is not forseen by the wizard.

In the AddRDSData routine you find the CASE statement for your RDS tabke name. For this blog it is named 'Filelist'. For each table name the wizard writes code to help you start supplying the data. In comments you will find the name of each column, the data-type and the length. Our Filelist RDS data code needs to be:
Code:
Function AddRDSData String sTableName Integer iLevel Returns Variant[][]    
    Variant[][] vData
    Integer iRow
    Handle hTable

    Case Begin
        Case (iLevel = 0 and sTableName = "FileList")
            Get_Attribute DF_FILE_NEXT_USED of hTable to hTable
            While (hTable <> 0)
                Move hTable to vData[iRow][0] // Name: Number, Length: 10, Datatype: Integer
                Get_Attribute DF_FILE_ROOT_NAME of hTable to vData[iRow][1] // Name: RootName, Length: 40, Datatype: String
                Get_Attribute DF_FILE_DISPLAY_NAME of hTable to vData[iRow][2] // Name: DisplayName, Length: 32, Datatype: String
                Get_Attribute DF_FILE_LOGICAL_NAME of hTable to vData[iRow][3] // Name: LogicalName, Length: 31, Datatype: String
                Increment iRow

                Get_Attribute DF_FILE_NEXT_USED of hTable to hTable
            Loop
            Case Break
    Case End

    Function_Return vData
End_Function
The routine now creates a jagged array with data. The array size (the number of columns) need to match the table definition else the row will be rejected.

Now you can press F5 to compile and run the current project. The result should be your filelist in a preview window or in the desired output format.

Column Name	Datatype	Length
Number	Integer
RootName	String	40
DisplayName	String	32
LogicalName	String	31