Visual Report Writer and The Web (II)
by
, 21-Apr-2013 at 04:25 AM (12992 Views)
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:
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: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
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 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
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.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
The code for the double selectionlist is as follows:
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.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
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.
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 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
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.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
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.
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 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
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 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
The URL generated by DownloadURL is given to the oViewer object which is a instantiation of the cWebIFrame class.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
I hope this blog gets you on the path of using Visual Report Writer together with the DataFlex Web Application Framework.