Visual Report Writer and The Web (VIII)
by
, 27-May-2013 at 11:52 PM (11694 Views)
In this eighth blog about Visual Report Writer and the Web I want to take you to the next report that can be found at the Live Demo website (European Server, USA server) named Sick Leave. If this is the first blog you read I encourage you to read the seven other blogs (1: The Solution, 2: Invoices Report, 3: The Cleanup, 4: The CustomerList, 5: The Orderlist, 6: The Credit and Balances Overview) and 7: Inventory Stock Levels). Between the fourth and the fifth blog we have been able to release the Alpha II version of Visual Report Writer 3.0 and the 2.1+ Library only setup. The latter one is needed to make the web reporting using the 17.1 DataFlex Web Framework easy and look like at the demo website.
The Report
The report makes use of the Microsoft Adventure Works 2000 database. You can download this database if you want to play with SQL and don't want to convert one of the DataFlex Embedded Databases. Note there are more versions of the Adventure Works database and we choose for this (quite old) database so that it can run on older versions of Microsoft SQL Server. We also selected this database for the reports as we wanted to make reports with images coming from the database but the pictures in there are too poor to show to you. For a picture report we made reports on the Wines Example database and I will discuss the reports using this database in a later blog. Also note that we used a script that you can download from internet to increase all dates in the database so that it looks like the orders were placed more recently and important for the report of this blog that employees of the company are not all above 65 years old!
Where the previous two blogs used the same report for two web views this Sick Leave reporting defines two different reports about employees of the company.
The Sick Leave per age report has its page header section duplicated, once with a column header box and once without and the sections are suppressed by a parameter named SuppressDetails. With suppress details turned on the report results are one page which contains summary information; per employee age range (ranges per 10 year periods) a number of sick days. Another parameter can be set to indicate that uneven row coloring for the detail section should be used.
The Sick Leave Overview report shows all employees with their number of sick days. Depending on a parameter (there are 5 parameters that can be set) different page header and details sections are printed. For example; you can select on department and if so the report will not print the department name in the details section. Other parameters that can be set are to hide/show the Male/Female column, hide/show the Age column and whether you want an alert color for employees that have their birthday in a number of days prior or past the current date.
Both reports use a image in the page header section and the path to the image can be set via a parameter. In the upcoming (in fact already available as Alpha version) of Visual Report Writer this kind of static image can be changed into an embedded image. The live demo website runs on version 2.1 which requires a path setting for images or deployment of the report in the same folder as at design time (not a good idea).
Integration
There are two report views to discuss in this blog. One report view for the Sick Leave Per Age report and one for the Sick Leave Overview report. Both views use a different way to set the selection criteria and parameters.
Sick Leave Per Age
In the report view you can browse for a department record. By selecting a department the data is filtered for this department meaning the AddFilter message will be used. In the OnInitializeReport this is done as follows:
In addition - can be added or used separately - a filter can be set to employees title. A drop down (cWebCombo) is used to let you select the title. The drop down values are read from the database using an embedded SQL statement where a distinct select is used to get that data. The code for that is:Code:WebGet psValue of oDepartmentIDForm to iDepartmentID If (iDepartmentID <> 0) Begin Send AddFilter C_USEMAINVRWREPORTID '{Employee.DepartmentID}' C_VRWEqual iDepartmentID End
The filter is for this column is like the department id conditionally added.Code:Object oEmployeeTitle is a cWebCombo Set piColumnSpan to 7 Set psLabel to "Title:" Set peLabelAlign to alignRight Procedure OnFill Handle hoSQL hoConnection hoStatement Integer iFetchResult String sTitle // Make it possible to NOT select a title Send AddComboItem '' '' Get Create (RefClass (cSQLHandleManager)) to hoSQL Get SQLFileConnect of hoSQL SQLEmployee.File_Number to hoConnection Get SQLOpen of hoConnection to hoStatement Send SQLExecDirect of hoStatement "Select Distinct [Employee].[Title] From [Employee]" Repeat Get SQLFetch of hoStatement to iFetchResult If (iFetchResult <> 0) Begin Get SQLColumnValue of hoStatement 1 to sTitle Send AddComboItem sTitle sTitle End Until (iFetchResult = 0) // Clean up stuff Send SQLClose to hoStatement Send SQLDisconnect to hoConnection Send Destroy of hoSQL End_Procedure End_Object
Two checkboxes are used to set the SuppressDetails and UseUnevenRowColoring parameters.Code:WebGet psValue of oEmployeeTitle to sEmployeeTitle If (sEmployeeTitle <> '') Begin Send AddFilter C_USEMAINVRWREPORTID '{Employee.Title}' C_VRWEqual sEmployeeTitle End
The last option for a user in this reportview is to select the report ordering. The user has the choice between the ordering already defined in the report (the design time ordering), Sorting on the employee last name or Sick Leave Hours. Both can be combined with a choice for descending. The combo form is statically filled:Code:Get GetChecked of oSuppressDetails to bShowDetails Get ParameterIdByName C_USEMAINVRWREPORTID 'SuppressDetails' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to bShowDetails Get GetChecked of oUseUnevenRowColoring to bUseUnEvenRowColoring Get ParameterIdByName C_USEMAINVRWREPORTID 'UseUnevenRowColoring' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to bUseUnEvenRowColoring
The user's choice is read inside the OnInitializeReport method and if it is not option 0 (sorting as defined at design time) the current report sort order will be removed and newly constructed with the following code:Code:Object oSortOnCombo is a cWebCombo Set psLabel to "Sort on:" Set peLabelAlign to alignRight Set piColumnSpan to 3 Set pbServerOnChange to True Procedure OnChange Integer iChoice WebGet psValue to iChoice WebSet pbEnabled of oSortDescending to (iChoice <> 0) End_Procedure Procedure OnFill Send AddComboItem '0' 'Report sorting' Send AddComboItem '1' 'Sort on LastName' Send AddComboItem '2' 'Sort on Sick Leave Hours' End_Procedure End_Object Object oSortDescending is a cWebCheckbox Set psCaption to 'Descending' Set piColumnIndex to 3 Set pbEnabled to False End_Object
The output of this report is not send to a cWebIFrame control as before but to a specialized control for Visual Report Writer. This control - that will be available from version 3.0 - can display the report results generated as HTML or as images. The class is called cWebVrwReportViewer and most of the time you only tell the object where to find the cVRWReport object.Code:WebGet psValue of oSortOnCombo to sSortOnValue Case Begin Case (sSortOnValue = '1') Send RemoveAllRecordSortFields C_USEMAINVRWREPORTID Get GetChecked of oSortDescending to bSortDescending If (bSortDescending) Begin Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.Lastname}' C_VRWDescending End Else Begin Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.Lastname}' C_VRWAscending End Case Break Case (sSortOnValue = '2') Send RemoveAllRecordSortFields C_USEMAINVRWREPORTID Get GetChecked of oSortDescending to bSortDescending If (bSortDescending) Begin Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.SickLeaveHours}' C_VRWDescending End Else Begin Send AddRecordSortField C_USEMAINVRWREPORTID '{Employee.SickLeaveHours}' C_VRWAscending End Case Break Case End
To start the report output generation you send ShowReport to the oViewer report. If the cWebVrwReportViewer object is used to display HTML output it send a request to the cVRWReport object to construct the HTML string and return it back. This request is named GenerateReportHTML and needs to return HTML in chunks. The chunks can be in any size as long as they are smaller than the Visual DataFlex argument size (See Set/Get_Argument_Size command in the Visual DataFlex help). Knowing this the GenerateReportHTML is coded as follows:Code:Object oWebViewerPanel is a cWebPanel Set pbFillHeight to True Object oViewer is a cWebVrwReportViewer Set phoReport to oReport End_Object End_Object
Sick Leave OverviewCode:Function GenerateReportHTML Returns String[] String sReportId String[] sData Integer iArgSize Get OpenReport to sReportId If (sReportId <> "") Begin Get_Argument_Size to iArgSize Get ComReportHTMLPreview sReportId (iArgSize - 10) to sData Send CloseReport sReportId End Function_Return sData End_Function
The second report view in the live demo application let you select values via ModalDialog. It makes the user interface more smooth by hiding the controls for selections and parameters until the user wants to change/set them but for the application developer it is more work to create. First a modal dialog needs to be constructed but more important the selection criteria need to made as synchronizable web properties.
The selections dialog is invoked from a cWebMenuItem and each of the settings in the dialog is created as property inside the cWebMenuItem object.
The psCSSClass property makes it possible to specify a different icon to the button based on the selected theme. Note that the live demo website does not allow you to change themes but the Visual Report Writer CSS settings make it possible.Code:Object oSelectionsButton is a cWebMenuItem Set psCaption to "Selections" Set psCSSClass to "VRWSelectionsButton" { WebProperty = True DesignTime = False } Property Integer piSelectOn { WebProperty = True DesignTime = False } Property String psSelectionValueForDepartment { WebProperty = True DesignTime = False } Property String psSelectionValueForTitle { WebProperty = True DesignTime = False } Property String psMaritalStatus { WebProperty = True DesignTime = False } Property Integer piBirthdayRange { WebProperty = True DesignTime = False } Property String psHideAge { WebProperty = True DesignTime = False } Property String psHideMF End_Object
In the OnClick event of the oSelectionsButton object the values from the above web properties are send to the modal dialog and displayed. The code for that is:
The Modal Dialog (cWebModalDialog) takes the values of the passed struct and sets the values of the controls.Code:Procedure OnClick tSickLeaveSelections SelectionValues WebGet piSelectOn to SelectionValues.iSelectOn WebGet psSelectionValueForDepartment to SelectionValues.sDeparmentSelectionValue WebGet psSelectionValueForTitle to SelectionValues.sTitleSelectionValue WebGet psMaritalStatus to SelectionValues.sMaritalStatus WebGet piBirthdayRange to SelectionValues.iBirthDayRange WebGet psHideAge to SelectionValues.sHideAge WebGet psHideMF to SelectionValues.sHideMF Send OpenSelectionsDialog of oSickLeaveOverviewSelectionsDialog Self SelectionValues End_Procedure
If you 'play' a bit with the selections dialog (I advise you to do this now if you didn't) you will see that dialog contents change based on the chosen value in the first combo control. Based on the current choice a control for department or title selection is rendered.Code:Procedure OpenSelectionsDialog Handle hoReturn tSickLeaveSelections SelectionValues WebSet psValue of oSelectOnCombo to SelectionValues.iSelectOn WebSet psValue of oSelectionValueForDepartment to SelectionValues.sDeparmentSelectionValue WebSet psValue of oSelectionValueForTitle to SelectionValues.sTitleSelectionValue WebSet psValue of oMaritalStatus to SelectionValues.sMaritalStatus WebSet piSliderValue of oEmployeeBirthDayRange to SelectionValues.iBirthDayRange WebSet psValue of oHideAge to SelectionValues.sHideAge WebSet psValue of oHideMF to SelectionValues.sHideMF Send OnChange of oSelectOnCombo SelectionValues.iSelectOn '' Send Popup hoReturn End_Procedure
The oSelectionValueForTitle cWebCombo is in code identical as the one used for the Sick Leave Per Age report integration. The values are retrieved via an embedded SQL statement.Code:Object oSelectOnCombo is a cWebCombo Set psLabel to "Select on:" Set peLabelAlign to alignRight Set piColumnSpan to 6 Set pbServerOnChange to True Procedure OnFill Send AddComboItem '0' 'None' Send AddComboItem '1' 'Department' Send AddComboItem '2' 'Title' End_Procedure Procedure OnChange String sNewValue String sOldValue Case Begin Case (sNewValue = '0') WebSet pbRender of oSelectionValueForDepartment to False WebSet pbRender of oSelectionValueForTitle to False Case Break Case (sNewValue = '1') WebSet pbRender of oSelectionValueForDepartment to True WebSet pbRender of oSelectionValueForTitle to False Case Break Case (sNewValue = '2') WebSet pbRender of oSelectionValueForDepartment to False WebSet pbRender of oSelectionValueForTitle to True Case Break Case End End_Procedure End_Object
Where the department selection value in the Sick Leave Per Age report integration is managed via a standard cWebForm object we use a cWebCombo in this modal dialog. We did this to avoid a modal dialog to be called from a modal dialog and to show how well you can use embedded SQL in a web application.
The above is similar to the code used to fill the cWebCombo for title selection with the exception there is no DISTINCT in use and the code and display values are different.Code:Object oSelectionValueForDepartment is a cWebCombo Set psLabel to "Department:" Set peLabelAlign to alignRight Set piColumnSpan to 10 Procedure OnFill Handle hoSQL hoConnection hoStatement Integer iFetchResult String sId sName Get Create (RefClass (cSQLHandleManager)) to hoSQL Get SQLFileConnect of hoSQL SQLDepartment.File_Number to hoConnection Get SQLOpen of hoConnection to hoStatement Send SQLExecDirect of hoStatement "Select [Department].[DepartmentId], [Department].[Name] From [Department]" Repeat Get SQLFetch of hoStatement to iFetchResult If (iFetchResult <> 0) Begin Get SQLColumnValue of hoStatement 1 to sId Get SQLColumnValue of hoStatement 2 to sName Send AddComboItem sId sName End Until (iFetchResult = 0) // Clean up stuff Send SQLClose to hoStatement Send SQLDisconnect to hoConnection Send Destroy of hoSQL End_Procedure End_Object
For the oMaritalStatus control we again use a cWebCombo object that fills itself via an embedded SQL statement using a DISTINCT operation
It could have been hard coded but it the values change (they probably don't) no code change is required.Code:Send SQLExecDirect of hoStatement "Select Distinct [Employee].[MaritalStatus] From [Employee]"
The two checkboxes for Hide Age and Hide M/F data are simple cWebCheckbox objects with hard coded settings for true and false
The last control in the modal dialog is a cWebSlider object allowing the user to select a value between 0 and 31 days for a upcoming birthday alert.Code:Object oHideAge is a cWebCheckbox Set psLabel to "Hide Age:" Set peLabelAlign to alignRight Set psChecked to '1' Set psUnchecked to '0' Set piColumnSpan to 2 Set pbShowLabel to True End_Object Object oHideMF is a cWebCheckbox Set psLabel to "Hide M/F:" Set peLabelAlign to alignRight Set psChecked to '1' Set psUnchecked to '0' Set piColumnSpan to 2 Set pbShowLabel to True End_Object
When the user clicks one of the two buttons in the modal dialog the control is passed back to the cWebMenuItem sending a OnCloseModalDialog event. In this event the current selected values are retrieved and stored back in the synchronizable web properties defined above. It felt right to directly generate the report after the modal dialog was closed by clicking the OK button as well.
The selected values are retrieved by the cWebReport object in the OnInitializeReport event.Code:Procedure OnCloseModalDialog Handle hoModalDialog tSickLeaveSelections SelectionValues Boolean bCancel Get SickLeaveSelectedValues of hoModalDialog (&SelectionValues) to bCancel If (not (bCancel)) Begin WebSet piSelectOn to SelectionValues.iSelectOn WebSet psSelectionValueForDepartment to SelectionValues.sDeparmentSelectionValue WebSet psSelectionValueForTitle to SelectionValues.sTitleSelectionValue WebSet psMaritalStatus to SelectionValues.sMaritalStatus WebSet piBirthdayRange to SelectionValues.iBirthDayRange WebSet psHideAge to SelectionValues.sHideAge WebSet psHideMF to SelectionValues.sHideMF Send ShowReport of oViewer End End_Procedure
The viewer object has one more unexplained feature available for us. This is the possiblity to respond on a hyperlink click. This event is called OnClickActionLink and it get the value between of HTML anchor element. With this value we can open a dialog to show (or even modify) the data of the selected row. Making Visual Report Writer really interactive.Code:Procedure OnInitializeReport String sReportLocation sSelectValue Integer iParameter iSelectOn Get psReportLocation to sReportLocation Get ParameterIdByName C_USEMAINVRWREPORTID 'ImagePath' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to sReportLocation Send RemoveAllFilters C_USEMAINVRWREPORTID WebGet piSelectOn of oSelectionsButton to iSelectOn Get ParameterIdByName C_USEMAINVRWREPORTID 'SelectOn' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to iSelectOn Case Begin Case (iSelectOn = 0) Case Break Case (iSelectOn = 1) WebGet psSelectionValueForDepartment of oSelectionsButton to sSelectValue Send AddFilter C_USEMAINVRWREPORTID '{Employee.DepartmentID}' C_VRWEqual sSelectValue Case Break Case (iSelectOn = 2) WebGet psSelectionValueForTitle of oSelectionsButton to sSelectValue Send AddFilter C_USEMAINVRWREPORTID '{Employee.Title}' C_VRWEqual sSelectValue Case Break Case End WebGet psMaritalStatus of oSelectionsButton to sSelectValue If (sSelectValue <> '') Begin Send AddFilter C_USEMAINVRWREPORTID '{Employee.MaritalStatus}' C_VRWEqual sSelectValue End WebGet piBirthdayRange of oSelectionsButton to sSelectValue Get ParameterIdByName C_USEMAINVRWREPORTID 'BirthdayRange' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to sSelectValue WebGet psHideAge of oSelectionsButton to sSelectValue Get ParameterIdByName C_USEMAINVRWREPORTID 'HideAge' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to sSelectValue WebGet psHideMF of oSelectionsButton to sSelectValue Get ParameterIdByName C_USEMAINVRWREPORTID 'HideMF' to iParameter Set psParameterValue C_USEMAINVRWREPORTID iParameter to sSelectValue End_Procedure
Above feature is not available until the release of version 3.0 enterprise. I will discuss the coding in a later blog.Code:Object oViewer is a cWebVrwReportViewer Set phoReport to oReport Set pbServerOnClickActionLink to True Procedure OnClickActionLink String sText Send ShowEmployee of oSQLEmployeeModalDialog Self sText End_Procedure End_Object
This concludes blog number eight about Visual Report Writer web integration. Hope you enjoyed reading this, are getting inspired to get started yourself and are looking forward to future blogs that will follow soon.