[PlanetJ Corporation

[support@planetJavaInc.com]

[www.PlanetJavaInc.com]


WEB OBJECT WIZARD:  WOW REPORTS - 7.0

 

WOW reports is an add-on feature of WOW that allows report development with Microsoft WORD and PDF programs. Users can easily create forms using these programs and WOW will merge and populate these documents at runtime. Some examples include: an invoice form, statements, bill of materials, and bill of lading.

 

There are 4 main approaches to using WOW Reports.

 

1. FDF – PDF generation

 

2. MS WORD XML reports

 

3. MS WORD HTML reports

 

4. MS WORD MERGE using template files (.DOT)

-

WEB OBJECT WIZARD:  WOW REPORTS - 7.0

Installation Instructions

WOW Reports Samples

Using FDF - PDF

Using WordXML or WordML

Using Word HTML

Using Word Merges

Parameters With Predefined Row Number

Parameters in XML Comment Blocks

Generating Reports for Multiple Rows

Images in WOW Reports

Pivot Reports

Header/Detail Reports

Installation Instructions

 

Download the WOWReports .zip file provided by PlanetJ and follow the steps below. Installation should only take a few minutes if WOW is already installed on the machine. It is highly recommended that you are using WOW 6.5.02 or higher.

 

Steps:

 

1. Unzip the WOWReports .zip folder and put it in a location that is accessible from your current WOW server or local machine that is running WOW.

 

2. Copy the wow-reports-X.X.XX.jar onto Tomcat in your current WOW context. (Ex. C:/Program Files/Apache Software Foundation/Tomcat 5.5/wow65/WEB-INF/lib folder)

NOTE: The path may be different depending on where Apache Tomcat is installed on the machine and what version of WOW you are running.

 

3. Restart Tomcat and run WOW. The following sections will describe how to use WOWReports.

WOW Reports Samples

 

Please click here for a WOW Reports sample.

 

Below is an example of a Word to HTML document conversion. It is a document template made in Word (or an existing template) saved as an HTML file. Note how the fields are labeled so that WOW can populate these fields with data from the database. Directions on how to set this up is explained later in this document.

On the left column of the table shown below, there is a small Microsoft Word icon.

Click on this icon, and an HTML page appears like the one below it:

NOTE: In order for your dynamic variables to work correctly and be noticed by WOW, you must either shut off automatic checking of spelling or grammar, or remove the red/green lines that Word inserts on words/phrases it thinks are incorrect. These red/green lines append special tags to the words/phrases and as such, WOW cannot understand the variables.

Using FDF - PDF

Use this option when a PDF report is desired. Currently this only supports a single data row (not header detail type scenarios).

 

1. Create a PDF with variable “form fields” which can be replaced by a FDF at runtime. Where variable data is desired, enter in the text “#variables.field_name#” (w/o the quotes) and where field_name represents the name of the field where the data will come from the database. Use the same name as the field name.

2. Place the PDF in your WOW server, by creating a “wowpdf” folder inside your wow64x\wow\ folder. An example would be something like this:.c:\Program Files\Apache Software Foundation\Tomcat5.5\webapps\wow64x\wow\wowpdf

 

3. In the SQL operation you created for this feature, navigate to the “Advanced” settings section, and enter “planetj.output.PDFRow” in the Row Class entry field (w/o the quotes).

 

4. You must also tell the operation which PDF to merge data with. This can be done in the following ways:

a. Property Group: Add the following to the Operation Properties section: PDFRow{ file:myForm.pdf; }

 

b. In the SQL code, include a field named “PDF_FILE” which names the PDF to use. This option allows for situations where different rows may have different PDFs to print; for example savings versus checking account records. An exampe of an SQL statement would look like this: SELECT user, lastname, firstname, “myForm.pdf” as PDF_FILE FROM dill_sample.employee

5 . Run the operation and click on the PDF icon on the left side of a row to invoke the data merge to the PDF.

 

NOTE:  Certain browsers such as Microsoft Internet Explorer may incorrectly open an “extra” window when a PDF is downloaded. At the time of this writing, this is an issue with browsers and outside the scope and control of WOW.

Using WordXML or WordML

 

Use this option when a report is wanted in XML format. A benefit is that a WORDML document can be directly opened and used inside Microsoft Word. For this feature to work correctly, it is strongly recommended that you are using Microsoft Word 2003 or later.

1.Using MS WORD, create or use an existing template document and create the form as desired. Users can benefit from the large inventory of available free templates especially at Microsoft’s Office Online website.

 

2. Where variable data is desired, place ??FIELD_NAME and WOW will replace it with database data. For more complex reports, associated data can be retrieved using WOW scripting. The field name must match the fields that are being selected in your SQL statement, or be one of the fields being selected if using SELECT *.

 

3. Nomenclature for more advanced field names inside your WORD XML document (when using associated data) is supported in two ways:

a.Using parameters like ??WORKDEPT!LASTNAME!1, the report can be displayed with only fields from a predefined row number. For more details, see the parameters with predefined row number section.

 

b. Using html/xml comments block, information from all associated rows can be displayed, not dependent on their count. For more details see parameters in XML comment blocks section.

4. Save the Word document as an “XML” file.

 

5. Place the XML file in your WOW server, by creating/using a “wowword” folder inside your wow6x\ folder (with the x being the current version you are using). An example would be something like this: c:\Program Files\Apache Software Foundation\Tomcat5.5\webapps\wow64x\wowword

 

6. In the SQL operation you created for this feature, navigate to the “Advanced” settings section, and enter “planetj.wow.reports.MSWordRow” in the Row Class entry field (w/o the quotes).

7. You must also tell the operation which XML to merge data with. This can be done in the following ways:

 

a. Property Group: Add the following to the Operation Properties: WORD{ file:invoice.xml; }

 

b. In the SQL code, include a field named “WORD_DOT” in the select statement that names the XML file to use. This option allows for situations where different rows may have different XML to print; for example savings versus checking account records. An example of an SQL statement is as follows: SELECT user, lastname, firstname, “invoice.xml” as WORD_DOT FROM dill_sample.employee

8. Run the operation and click on the MS WORD icon on the left side of a row to invoke the XML document data merge.

Using Word HTML

 

Use this option when a report is wanted in an HTML format. One huge benefit is that HTML will be the most compatible with browsers.

 

1. Using MS WORD (or any HTML/WSYISYG editor), create or use an existing template document and create the form as desired. Users can benefit from the large inventory of available free templates especially at Microsoft’s Office Online website.

 

2. Where variable data is desired, place ??FIELD_NAME and WOW will replace with database data. For more complex reports, associated data can be retrieved using WOW scripting. The field name must match the fields that are being selected in your SQL statement, or be one of the fields being selected if using SELECT *.

 

3. Nomenclature for more advanced field names inside your WORD XML document (when using associated data) is supported in two ways:

a. Using parameters like ??WORKDEPT!LASTNAME!1, the report can be displayed with only fields from predefined row number. For more details see parameters with predefined row number section.

 

b. Using HTML/XML comments block, information from all associated rows can be displayed, not dependent on their count. For more details see parameters in XML comment blocks section.

4. If you used MS WORD or another text-editing application, save your file as an HTML document.

 

5. Place the HTML file in your WOW server, by creating/using a “wowword” folder inside your wow6x\ folder (with the x being your current version of WOW). An example would be something like this:c:\Program Files\Apache Software Foundation\Tomcat5.5\webapps\wow64x\wowword

 

6. In the SQL operation you created for this feature, navigate to the “Advanced” settings section, and enter “planetj.wow.reports.MSWordRow” in the Row Class entry field (w/o the quotes).

7. You must also tell the operation which HTML to merge data with. This can be done in the following ways:

a. Property Group: Add the following to the operation “Properties” section: WORD{ file:invoice.htm; }

 

b. In the SQL code, include a field named “WORD_DOT” in the select statement that names the HTML file to use. This option allows for situations where different rows may have different HTML to print; for example savings versus checking account records. An example of the SQL statement is as follows: SELECT user, lastname, firstname, “invoice.htm” as WORD_DOT FROM dill_sample.employee

Using Word Merges

 

Use this option when a report is wanted using a new or an existing MS WORD document (.dot) template. By using “bookmarks” to denote where data is to be placed on the document, WOW dynamically populates these bookmarks with data from your database. Then, an actual document is sent to the browser in (.doc) format.

 

1.Using MS WORD, create or use an existing template document and create the form as desired. Users can benefit from the large inventory of available free templates especially at Microsoft’s Office Online website. Where database text is desired, add a bookmark.

 

2. Where variable data is desired, you will need to create a bookmark in your WORD document. Place the cursor onto the field where you want to place data from the database and click on it. Then:

a.Inside MS WORD, select Insert then select Bookmark.

 

b. Name the bookmark the same name of the field name in the database for the data you want to be placed where your cursor is located. (Do not use ?? like in previous examples, use only text.) Note: when using fields that are in an association, you also use just that field’s name. There is no need to add the name of the associated field like in the other examples.

 

c. Click on “Add.” When your operation is run with this feature set, the bookmarks will be filled in with the corresponding data from the database.

3. Save the Word document as template (.dot) file

 

4. Place the template in your WOW server, by creating/using a “wowword” folder inside your wow6x\ folder (where the x is the current version of WOW you are running). An example would be something like this: c:\Program Files\Apache Software Foundation\Tomcat5.5\webapps\wow64x\wowword

 

5. In the SQL operation you created for this feature, navigate to the “Advanced” settings section, and enter “planetj.wow.reports.MSWordRow” in the Row Class entry field (w/o the quotes).

6. You must also tell the operation which template file to merge data with. This can be done in the following ways:

a. Property Group: Add the following to the operation “Properties” section: WORD{ file:invoice.dot; }

 

b. In the SQL code, include a field named “WORD_DOT” in the select statement that names the .dot template file to use. This option allows for situations where different rows may have different .dot to print; for example savings versus checking account records. An example of the SQL statement is as follows: SELECT user, lastname, firstname, “invoice.dot” as WORD_DOT FROM dill_sample.employee

Parameters With Predefined Row Number

 

In WordXML (WORDML) and Word HTML, WOW can generate reports using any field value from a predefined row. This can be displayed using following nomenclature:

 

??WORKDEPT!LASTNAME!1

 

This script basically tells WOW to: “Find the field ‘WORKDEPT’ and then within its associated data, get the associated field ‘LASTNAME’ from row #1 (if using a 1-many association).” No number is needed if using a 1-1 association. The association must be set on the ‘WORKDEPT’ field.

 

Thus, “??PRODUCTID!PRODUCTNAME!4” will get the associated field ‘PRODUCTNAME’ field from row #4 on a 1-many association. This association must be set on a field called ‘PRODUCTID’

 

This means that reports will display only numbers that are hardcoded in XML/HTML template not depending on associated row count. To display any field form associated rows go to next section.

 

Here is a sample image of a template with parameters filled in:

Parameters in XML Comment Blocks

 

This allows you to display any data from associated rows (not depending on count) in Word XML (WORDML) and Word HTML reports. To implement, open your XML/HTML template in any text editor (for instance notepad, EditPad or Notepad++). In this example, you will not see the formatted template, just text with markup tags.

 

Use the following comment block to display associated data:

In HTML, the field name should be replaced with the associated field name to display the data. Inside of the HTML, put metadata tags repeating for each of the associated rows; example: parameters like??FieldName to retrieve data from desired field.

WOW, in generating the reports, will repeat this block for each row from the associated field and replace parameters in each. This will repeat with data from the appropriate associated row field.

 

For instance, the template can look like this:

In markup tags generated report will look like this:

Generating Reports for Multiple Rows

 

Word XML (WORDML) and Word HTML reports support report generation for more than one row in the same result document. To enable this and default reports configuration do the following:

 

1. In the SQL operation you created for this feature, navigate to the “Advanced” settings section, and enter “planetj.wow.reports.MSWordRowCollection” in the Row Coll. Class entry field (w/o the quotes).

 

 2. Go to “Display” setting section, update the Properties and set TableDisplay{ selectionType:;} value to multiple.

 

3. Now your operation should look like this:

 

Selecting one or more rows and clicking on the “Report” button will open a report for all of the selected rows.

Images in WOW Reports

 

WORD HTML Reports

 

Word HTML reports: These are a simple file with markup tags. It can not contain image content but it can contain references on external images somewhere on web, like your instance of WOW. To insert a reference, follow these steps:

 

1. Copy the image you want to be displayed on your report into your wow web root folder or some subfolder. For example: c:\Program Files\Apache Software Foundation\Tomcat5.5\webapps\wow64x\images

 

2. Open your template file with some text editor to see markup tags. Insert an img tag to reference your image in the place you want it displayed (as src can use relevant or full image path). This tag could look like:

a. <img border="0" title="WORD" alt="WORD" src="dataengine/images/some-image.gif"/>

 OR

b. <img border="0" title="WORD" alt="WORD" src="http://www.planetjavainc.com/wowReports/dataengine/images/some-image.gif"/>

3. Your image will be displayed in the report.

 

WORD XML Reports

 

Word XML (WORDML) reports: This is an XML file and can contain image content. To insert a reference image in XML, follow these steps:

 

1. Follow same steps as above to insert a reference to an external image. However, in this case:

a. Use the imagedata tag rather than the img tag

 AND

b. The image path can ONLY be external

2. Insert the image within the word template file by opening the template file with a text editor and finding the location you want to put it, then be sure to use the imagedata tag rather than the img tag and insert the image.

 

3. The image will be displayed then next time you run the report.

Pivot Reports

Pivot reports allow traditional data to be viewed in alternate formats for enhanced viewing and analysis.

To set up a pivot reports, you must first create an SQL operation with desired SQL code.  This operation must have it’s Row Collection Class set to: planetj.wow.reports.pivot.PivotRowCollection

We then need to set up our Property Group for the Pivot report.  This property group is called PivotTable{ }.  

The PivotTable{ } Property Group contains 3 different properties associated with it:

An example of a PivotTable{ } property group would be as follows:

PivotTable{KeyPivotColumn:REGION; PivotColumn:Type; PivotColumnValue:SALES;}

If set up properly the results, which started out looking like this:

Would end up looking like this:

Notice how the table has “pivoted” and used Region as the Key Pivot Field, with Type as the Columns and Sales as the Values.

Header/Detail Reports

Using the ‘Header/Detail Reports’ JSP File setting (Figure 10-7), a report with a title, header, body, and footer can be generated very easily. In the example below, we create a Department report that has a

        1.  Title graphic with dynamic title

        2.  Header section containing the department’s details (name, location, etc.)

        3.  Body section listing each employee within the department and their details

        4.  Footer graphic.

  1. Above is a screenshot of Header/Detail Reports in details view.

Here is the SQL Operation used to create this sample report:

SELECT  

'<img src="user/report/header.gif"> <h2> Department: ' ||  deptname   as Title, DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION,MAXSALARY,

'EMPLIST' as EMPLIST,

'<img src="user/report/footer.gif">' as Footer  

FROM pjdata.department

**Note: If your SQL Operation returns more than one record (like in the example above), then a header/detail report will be generated for each record, one right after another.  

On the surface, this SQL Operation may not seem any different than normal. Yet, obviously, the resulting output is significantly different than the standard result set. This is due to several key features in the SQL statement and the operation settings that allow this report to format correctly:

1. The ‘Header/Detail Reports’ setting was selected from the ‘JSP File’ field.

2. In the SQL statement, the fields must be listed in the appropriate order (Title, Header, Body, Footer)

3. All title content must be contained in a field named ‘Title’. In the example above, this is done with a derived field (as Title).

4. All footer content must be contained in a field named ‘Footer’. In the example above, this is done with a derived field (as Footer). Note: the footer is optional.

5. The Header and Body sections are simply all fields between the Title and Footer (i.e. they are not specifically designated within the SQL like Title and Footer). It is usually most convenient to group the Header fields together using the FD’s Field Set property (as seen in Figure 10-8) and likewise for the Body fields.

  1. Above is a screenshot of the JSP File and Details JSP ( /dataengine/jsp/default_row_details.jsp ) operation settings necessary for a Header/Details Report in details view. For the list view, the Details JSP field would be left blank.

Other than these five characteristics, this SQL statement is no different than any other. Note, however, that the ‘EMPLIST’ field’s FD is set to a 1-MANY association with the following SQL Operation that lists all employees within the relevant department:

SELECT empno,firstnme,lastname,sex,phoneno,workdept,hiredate

FROM pjdata.employee

WHERE workdept = ??deptno

  1. Above is a screenshot of the FD properties of the derived field ‘EMPLIST’. Note the Display Component setting: *Associated Operation. This causes the Associated Operation to execute within the ‘EMPLIST’ field.  

This SQL Operation is also designated as a 'Header/Detail Reports' in its settings to achieve the details format. Alternately, the JSP File could just be set to ‘None’ rather than ‘Header/Detail Reports’. This would result in the association displaying in list format (see Figure 10-9 below). Also, the associated operation’s Display Component is set to '*Associated Operation' so that the associated field is not just a hyperlink to the operation but actually runs the operation within that field (Figure 10-8).  

  1. Above is a screenshot of Header/Detail Reports in list view.

Footer (optional)

Title (optional)

Body

Header

Title

Header

Body

Footer