View RSS Feed

Development Team Blog

Interactive Report in Windows with Visual Report Writer

Rating: 60 votes, 4.98 average.
Now we are on the edge of delivering the final Visual Report Writer 3.0 I want to learn you how you can make use of the new interactive previewer that is available in the product. In this blog we create a report that shows all orders that used the current selected INVT item. Clicking on an order should bring up the order view with the clicked order found and displayed. Using the INVT table means that you can use the order entry example workspace.

The Report
The report needs to be created with Visual Report Writer 3.0. The Order Entry workspace uses the DataFlex embedded database which means that "DataFlex" should be selected as data-source in the new report wizard. In Windows Explorer individual tables can be selected but selection of filelist or the workspace file is a much better choice as all tables are within the same workspace and none of the tables is twice or more available (the example workspace is not multi-tenancy). After selecting the workspace file the contents of the filelist is shown. From there the tables INVT, ORDERDTL, ORDERHEA and CUSTOMER should be selected. Because the filelist is selected Visual Report Writer can automatically connect the 4 tables together where the ORDERDTL table is set as the childmost/leftmost table. The correct childmost/leftmost table selection is important for filters. Only when a filter is set on the leftmost/childmost table jump-into and out-of index operations can be performed. In the case of this report where a filter should be created for the current INVT row it does not matter if ORDERDTL or INVT is the childmost/leftmost table as both tables have the unique ITEM_ID in each row. From the list of fields the columns ORDER_NUMBER, ORDER_DATE, QTY_ORDERED, CUSTOMER_NUMBER, NAME, CITY, STATE and ZIP should be selected. Later the inventory information will be added in the page header. If you like it you can group on customer but in the report used in this blog it is skipped. While a filter is really done via integration it makes sense to select one of the items to see how the report would look like during design time. Selecting a filter will also speed up the preview and gives a better idea of the performance of the report once it is integrated. In the options page select the right paper format and orientation.
Once back in the designer formatting should be applied like changing the column headers to bold, inserting a page title, information about the inventory item etc. The report could look like:
If you run the report from the designer you might not directly notice that the report operates slower than it should. To find out this you have to open the performance information from the report pull-down. The time needed to read the records is - on my machine - around half a second (430-550 ms). Not to bad? Well, there are not that many rows in this example. If there is much more data time to read the rows increases to a "unacceptable" value. The reason for this is simple; the report selects on a column that is not part of an index which makes jump-into and out-of an index impossible. If you add an index to the ORDERDTL table when the ITEM_ID value is the first segment and re-run the report (perform a check database first) you will see the time needed drops from around 500ms to around 40ms, TEN times faster.

The report can integrated in the application via the integration wizard. To be able to start the report integration wizard the Visual Report Writer library needs to be attached to the workspace. This can be done via the Tools pull-down, option Manage Libraries. Once the Visual Report Writer library is connected you can start the integration wizard. In the wizard choose the report created.
In the next wizard page accept or change the names of the component to be created. While the name needs to be unique it does not matter for the results of this blog as we will copy code from the component into the inventory view and destroy the newly created component.
Skip the selection fields wizard page as the selection will be done based on the selected inventory data. In the report filters page select 'remove above filters' as a double item id filter makes no sense.
The sort fields page can be left as is as well. Note the page shows the sort order read from the report. The next screenshot shows that the record sort order is set to order number from the ORDERDTL table. The sort order is left as is because it is right for this report and there is no need to change it or let the end-user change it.
In the options page the preview report is the only choice for the output destination as the goal is to display the report and make it clickable. The preview style is set to embedded as the output should be shown in the same view as the data entry.
Images like bitmaps and icons used in tool-bars can be embedded in the application executable file. Visual Report Writer images available in the library should be included in the current application else they will not be available in a deploy situation (unless they are distributed by the developer which is likely to be forgotten.
In the next wizard page it is possible to set a language and a collating sequence file. The language is used for the "Page N of M" special field in the report and should be added if the application with report integration is distributed in an environment where the language is different than the language during development of the report. The collating sequence can be changed/specified but most likely it does not need to be set.
Then the wizard can be finished which means the newly created component is loaded in the Studio and connected to the application. For the purpose of this blog remove the component via the workspace explorer but keep it open to copy information to the inventory view. Enlarge the inventory view making space for a 2nd container object below the already existing container. Set the anchors of this container to resize in all directions (Set peAnchors to anAll) if the view is resized. I also suggest you remove the piMaxSize property setting in this view to make it easier to see the report contents.
Now switch to the source code mode of the report view component and select both the commandbar and cVRWReport object from the Results tab-page. Copy this information to the inventory view in the new (2nd) container and resize the cVRWReport object to fit in the container space. The view could look like;
The next step is setting up a filter (replacing the filter) based on the current inventory item ID. For this locate the SetFilters method in the cVRWReport object and add the following two lines of code.
Get Field_Current_Value of Invt_DD Field Invt.Item_ID to sItemId
Send AddFilter sReportId "{Orderdtl.Item_Id}" C_vrwEqual sItemId
Refresh the Report
The report needs to be refreshed (re-run) each time the end-user finds a different row in the inventory table. For this I suggest you make use of the OnPostFind event of the datadictionary (INVT_DD for this view). The code to be inserted in the object should be:
Procedure OnPostFind Integer eMessage Boolean bFound
    Send RunReport of oReport
The goal of this blog is to make it posible to respond on a click in the report. For this you need to add the OnReportPreviewClick event to the cVRWReport object and write code to respond on the click. The event passes 4 values. The iSectionID is interesting if there are two or more objects in the report with the same name and in a different section. The name of the object (if an object was clicked) is passed as sObject. By default all section IDs are zero but in the report design you can - via the section expert - assign a section ID. The section ID can be a static number but can also be assigned from a function. Assuming the section ID of the details section is set to 2 the following code could be used in the report object.
Procedure OnReportPreviewClick C_vrwHitTests iPos Integer iSectionId String sObject String sValue
    Handle hoDD
    If (iSectionId = 2) Begin // Details section
        If (lowercase (sObject) = "orderhea.customer_number") Begin
            Send Activate_oCustomerView
            Get Main_DD of oCustomerView to hoDD
            Send Clear of hoDD
            Move sValue to Customer.Customer_Number
            Send Find of hoDD EQ 1
If the end-user clicks on the customer number in the preview the program will open the customer view and find the customer based on the sValue. Similar code could be written for other parts of the report.

I hope this blog inspires you to make use of Visual Report Writer and program great solutions for your customers.
Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Inventory Ordered Report.png 
Views:	7229 
Size:	43.2 KB 
ID:	7061   Click image for larger version. 

Name:	select report.png 
Views:	6907 
Size:	15.6 KB 
ID:	7062   Click image for larger version. 

Name:	object names.png 
Views:	6780 
Size:	13.3 KB 
ID:	7063   Click image for larger version. 

Name:	remove filters.png 
Views:	7016 
Size:	9.8 KB 
ID:	7064   Click image for larger version. 

Name:	sort fields.png 
Views:	6724 
Size:	15.3 KB 
ID:	7065   Click image for larger version. 

Name:	options page.png 
Views:	6715 
Size:	13.5 KB 
ID:	7066   Click image for larger version. 

Name:	add image resources.png 
Views:	7072 
Size:	9.3 KB 
ID:	7067   Click image for larger version. 

Name:	options page 2.png 
Views:	7099 
Size:	9.6 KB 
ID:	7068   Click image for larger version. 

Name:	enlarged inventory view.png 
Views:	6990 
Size:	7.7 KB 
ID:	7069  

Click image for larger version. 

Name:	invt view with report.png 
Views:	6919 
Size:	20.4 KB 
ID:	7070  


  1. Michael Mullan's Avatar
    Excellent work.

    But of course, like Oliver! "Please Sir, May I have some more?"

    How would you use this to create a Drill Down report? Customers--> Invoices--> items?