Project

General

Profile

JasperReports Integration

FWD provides a handle-based resource that can be easily used for exporting data reports into Excel (XLS, XLSX), CSV and PDF formats using JasperReports Library.

Introduction

Included with FWD is a set of Java classes that make it easy to export data reports. The following features are available:

  • Export to XLS, XLSX, CSV and PDF formats.
  • Use a 4GL query or a browse as the data source.
  • Uses standard JasperReports report definition format.
  • Embed images in report using a local file or an image via URL.

The backing support for this is implemented using the JasperReports Library project. No extra installation of software is needed. The supporting libraries are automatically pulled in to the installation by the FWD build process.

4GL API Details

This is an implementation of the REPORT extension to the 4GL. The created object is a handle based resource with attributes and methods that can be used to define the report data source and template and to run report using the target output format. Output file will occur on the client side.

This is a FWD-specific set of functionality. It does not exist and will not run in the Progress 4GL (e.g. OpenEdge).

To create an REPORT resource use the following syntax:

CREATE REPORT handle [ASSIGN { ATTR = value ...}].

The following list of attributes are available:

Attribute Data Type Required Description
report-data-source character Y Handle to the data source. Can be handle to a query or a browse widget. However, for browse widget case, you shouldn't assign the data source directly, but use browse:report attribute, which returns the report bound to this browse.
report-design character Y Report design file name (may include relative path). Can be report design file (.jrxml) or compiled design file (.jasper). File location is a relative path inside a server-side jar (searched first) and the client directory (for non-web version, searched second). Note that reports from the client side are cached on server, so after changing a report design file you have to restart FWD server in order to apply the new design.

The following list of methods are available:

Method Return Type Return Meaning Description
set-report-param(character param-name, var param-value) logical TRUE on success. Set report parameter. Parameters are optional, they are defined in report design files.
export-report-pdf(character output-file-name) logical TRUE on success. Export report using PDF format.
export-report-xls(character output-file-name) logical TRUE on success. Export report using XLS format.
export-report-xlsx(character output-file-name) logical TRUE on success. Export report using XLSX format.
export-report-csv(character output-file-name) logical TRUE on success. Export report using CSV format.

Creating Report Design

There are many tools available for creating/editing Jasper report definitions (.jrxml files). Some tools are free and others are commercial offerings.

One popular tool for visual creation/editing report definitions is Jaspersoft Studio. The latest version of Jaspersoft Studio known for producing reports compatible with the version of JasperReport Library which FWD uses is "Jaspersoft Studio 6.4.3.final".

You can also create or edit .jrxml files manually using an XML editor. Once you have a working template, XML edits can be a quick way to customize or manipulate the report.

Referencing Fields

You can reference fields of the buffers iterated by the 4GL query specified as the report data source using in-report fields defined in the report design file. In order to reference a buffer's field, name of the in-report field should match 4gl-buffer-name.4gl-field-name or just 4gl-field-name. So, if you have the following definitions:
def temp-table person field person-id as integer
                      field name as char.
open query q for each person.

def var rpt as handle.
create report rpt.
rpt:report-data-source = query q:handle.

you can define the person.name field as
<field name="person.name" class="java.lang.String"/>

or
<field name="name" class="java.lang.String"/>

In-report fields can have arbitrary class as the data type, including Java wrappers for primitive types (like java.lang.Integer) and FWD data types (like com.goldencode.p2j.util.integer).
<field name="person-id" class="java.lang.Integer"/>

or
<field name="person-id" class="com.goldencode.p2j.util.integer"/>

Declared fields can be referenced further in the report:
<textFieldExpression><![CDATA[$F{person-id}]]></textFieldExpression>

The value of a field is rendered using toString(), but for 4GL data types you may want to use your own rendering format, e.g.
<textFieldExpression><![CDATA[$F{person-id}.toString(">>,>>>,>>9")]]></textFieldExpression>

Except passing fields as is without altering data type, FWD supports automatic conversion which takes place when 4GL field data type doesn't match in-report field type. The following conversion directions are supported:
  • character, longchar -> java.lang.String
  • logical -> java.lang.Boolean
  • integer, int64 -> java.lang.Integer, java.lang.Long, java.lang.Short
  • decimal -> java.lang.Double, java.lang.Float, java.lang.Integer, java.math.BigDecimal
  • date, datetime, datetimetz -> java.util.Date

If 4GL field data type doesn't match in-report field type and automatic conversion is not supported between these two data types, an error is displayed.

Referencing Parameters

Report parameters can be declared and referenced in a report in a similar way as fields: arbitrary data types are supported, the same data type conversion rules apply.

<parameter name="ReportTitle" class="java.lang.String"/>
...
<textFieldExpression><![CDATA[$P{ReportTitle}]]></textFieldExpression>

Parameters are set using set-report-param function:
set-report-param("ReportTitle", "Address Report").

Example 4GL Program to Produce a PDF Report

The following 4GL procedure generates PDF output using the provided report design:

def temp-table person  field id as integer
                       field name as char
                       field city as char
                       field street as char.

create person. assign city = "Berne"          id = 22    name = "Bill Otto"             street = "754 Brook Street".
create person. assign city = "Berne"          id = 9     name = "James Schneider"       street = "9413 Harrison St.".
create person. assign city = "Boston"         id = 32    name = "Michael Bay"           street = "491 East 1st Rd.".
create person. assign city = "Boston"         id = 23    name = "Mary Karsen"           street = "94 East Lancaster St.".
create person. assign city = "Chicago"        id = 39    name = "George Bowden"         street = "192 Wakehurst Dr.".
create person. assign city = "Chicago"        id = 35    name = "Julia White"           street = "35 Arnold Ave.".
create person. assign city = "Chicago"        id = 11    name = "Janet Fuller"          street = "29 SW. Bay Dr.".
create person. assign city = "Dallas"         id = 47    name = "Susanne Smith"         street = "461 Meadow Court".
create person. assign city = "Dallas"         id = 43    name = "Susanne Miller"        street = "37 King Rd.".
create person. assign city = "Dallas"         id = 40    name = "John Steel"            street = "827 Summer St.".
create person. assign city = "New York"       id = 71    name = "Sylvia Myers"          street = "644 Wild Rose St.".
create person. assign city = "New York"       id = 12    name = "Bill King"             street = "580 Grand Road".
create person. assign city = "New York"       id = 5     name = "Janet May"             street = "7964 Piper Dr.".
create person. assign city = "San Francisco"  id = 31    name = "James Peterson"        street = "918 South Marsh Avenue".
create person. assign city = "San Francisco"  id = 17    name = "Robert Gampthon"       street = "7237 Old St Margarets St.".

open query q for each person.

def var rpt as handle.
create report rpt.
rpt:report-data-source = query q:handle.
rpt:report-design = "Report.jrxml".
rpt:set-report-param("ReportTitle", "Address Report").
rpt:export-report-pdf("Report.pdf").

Report.jrxml:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.3.final using JasperReports Library version 6.4.3  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="DataSourceReport" pageWidth="595" pageHeight="842" columnWidth="515" leftMargin="40" rightMargin="40" topMargin="50" bottomMargin="50" uuid="030574cb-2d4b-4281-8294-0f87619f0d7f">
    <property name="net.sf.jasperreports.print.create.bookmarks" value="true"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
    <style name="Sans_Bold" fontName="DejaVu Sans" fontSize="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
    <style name="Sans_Italic" fontName="DejaVu Sans" fontSize="12" isBold="false" isItalic="true" isUnderline="false" isStrikeThrough="false"/>
    <parameter name="ReportTitle" class="java.lang.String"/>
    <field name="id" class="com.goldencode.p2j.util.integer"/>
    <field name="person.name" class="java.lang.String"/>
    <field name="street" class="java.lang.String"/>
    <field name="city" class="java.lang.String"/>
    <variable name="CityNumber" class="java.lang.Integer" incrementType="Group" incrementGroup="CityGroup" calculation="Count">
        <variableExpression><![CDATA[Boolean.TRUE]]></variableExpression>
    </variable>
    <group name="CityGroup" minHeightToStartNewPage="60">
        <groupExpression><![CDATA[$F{city}]]></groupExpression>
        <groupHeader>
            <band height="20">
                <textField evaluationTime="Group" evaluationGroup="CityGroup" bookmarkLevel="1">
                    <reportElement style="Sans_Bold" mode="Opaque" x="0" y="-20" width="515" height="15" backcolor="#C0C0C0" uuid="db27978d-5882-4541-8ec1-ccb9211edb17"/>
                    <box leftPadding="10">
                        <bottomPen lineWidth="1.0"/>
                    </box>
                    <textFieldExpression><![CDATA["  " + String.valueOf($V{CityNumber}) + ". " + String.valueOf($F{city})]]></textFieldExpression>
                    <anchorNameExpression><![CDATA[String.valueOf($F{city})]]></anchorNameExpression>
                </textField>
            </band>
        </groupHeader>
        <groupFooter>
            <band height="20">
                <staticText>
                    <reportElement style="Sans_Bold" x="400" y="-24" width="60" height="15" uuid="6e23dcca-2a97-4184-888e-4d04a09337ad"/>
                    <textElement textAlignment="Right"/>
                    <text><![CDATA[Count :]]></text>
                </staticText>
                <textField>
                    <reportElement style="Sans_Bold" x="460" y="-24" width="30" height="15" uuid="65fe9e14-560b-45cd-9c0e-e190c8464296"/>
                    <textElement textAlignment="Right"/>
                    <textFieldExpression><![CDATA[$V{CityGroup_COUNT}]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <title>
        <band height="70">
            <line>
                <reportElement x="0" y="0" width="515" height="1" uuid="4058d4f4-b73d-4f3f-80db-3b2857ffb945"/>
            </line>
            <textField isBlankWhenNull="true" bookmarkLevel="1">
                <reportElement style="Sans_Normal" x="0" y="10" width="515" height="30" uuid="fe78690f-568a-4de4-950e-2355cbc905f1"/>
                <textElement textAlignment="Center">
                    <font size="22"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{ReportTitle}]]></textFieldExpression>
                <anchorNameExpression><![CDATA["Title"]]></anchorNameExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="20">
            <staticText>
                <reportElement style="Sans_Bold" mode="Opaque" x="0" y="-20" width="50" height="15" forecolor="#FFFFFF" backcolor="#333333" uuid="d0bda39f-a3d6-445f-9253-f156c802c4c2">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textElement textAlignment="Center"/>
                <text><![CDATA[ID]]></text>
            </staticText>
            <staticText>
                <reportElement style="Sans_Bold" mode="Opaque" x="50" y="-20" width="200" height="15" forecolor="#FFFFFF" backcolor="#333333" uuid="402baff8-c1df-4c1b-bfcb-0f21529cbf04">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    <property name="com.jaspersoft.studio.unit.x" value="px"/>
                </reportElement>
                <text><![CDATA[Name]]></text>
            </staticText>
            <staticText>
                <reportElement style="Sans_Bold" mode="Opaque" x="250" y="-20" width="265" height="15" forecolor="#FFFFFF" backcolor="#333333" uuid="b5ff5de3-a2b4-44de-b030-004f471f9b41">
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    <property name="com.jaspersoft.studio.unit.x" value="px"/>
                </reportElement>
                <text><![CDATA[Street]]></text>
            </staticText>
        </band>
    </pageHeader>
    <detail>
        <band height="15">
            <textField bookmarkLevel="2">
                <reportElement x="0" y="-25" width="50" height="15" uuid="bb195136-2738-4182-a1b0-00d1ca640bdd"/>
                <box leftPadding="10" rightPadding="10">
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{id}.toString(">>>>9")]]></textFieldExpression>
                <anchorNameExpression><![CDATA[$F{person.name} + " (" + $F{id} + ")"]]></anchorNameExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" x="50" y="-25" width="200" height="15" uuid="b98b57f4-ebed-4a29-b69f-b243445bff2d"/>
                <box leftPadding="10" rightPadding="10">
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                </box>
                <textFieldExpression><![CDATA[$F{person.name}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" x="250" y="-25" width="265" height="15" uuid="f3f2f70e-3138-4834-840c-a87253f1dc30"/>
                <box leftPadding="10" rightPadding="10">
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textFieldExpression><![CDATA[$F{street}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <pageFooter>
        <band height="40">
            <line>
                <reportElement x="0" y="-15" width="515" height="1" uuid="35482145-facf-43d6-91ce-9d8cf372af20"/>
            </line>
            <textField>
                <reportElement x="200" y="-5" width="80" height="15" uuid="7add3ee1-e567-44b0-bde4-cd01a27cd0f2"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA["Page " + String.valueOf($V{PAGE_NUMBER}) + " of"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement x="280" y="-5" width="75" height="15" uuid="621f5978-1e1d-43f5-9d80-a79e0cd4bcfa"/>
                <textFieldExpression><![CDATA[" " + String.valueOf($V{PAGE_NUMBER})]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
    <lastPageFooter>
        <band height="60">
            <textField bookmarkLevel="1">
                <reportElement x="0" y="-15" width="515" height="15" uuid="0b75977e-3952-45e5-9c2d-67083702af6e"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA["There were " +
                    String.valueOf($V{REPORT_COUNT}) +
                    " address records on this report."]]></textFieldExpression>
                <anchorNameExpression><![CDATA["Summary"]]></anchorNameExpression>
            </textField>
            <line>
                <reportElement x="0" y="5" width="515" height="1" uuid="180900c2-a45d-4c85-82c3-286a4149f2a0"/>
            </line>
            <textField>
                <reportElement x="200" y="15" width="80" height="15" uuid="fede74ae-ec9d-4a5d-bcb8-affc9933fcc3"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA["Page " + String.valueOf($V{PAGE_NUMBER}) + " of"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement x="280" y="15" width="75" height="15" uuid="132ec5c3-9bb2-4d54-9362-523043a658bd"/>
                <textFieldExpression><![CDATA[" " + String.valueOf($V{PAGE_NUMBER})]]></textFieldExpression>
            </textField>
        </band>
    </lastPageFooter>
</jasperReport>

Generated PDF report:

Example 4GL Program to Produce an XLS Document

An example consist of XLS data cells that can be divided into page header and page body. The header is a part that is not changing from one dynamic cell row to another. To handle this the Jasperreport parameters are used. All dynamic data are handled by 4GL TEMP-TABLE record, one record per single XLS row.

The 4GL code sample to convert:

def temp-table xlsRow field est_num as char
                      field description as char
                      field item as char
                      field quantity as decimal initial ?
                      field price as char
                      field uom as char.
def var comment as char extent 5 initial "".
def var qNumDec as decimal.

define query qExcel for xlsRow scrolling.
open query qExcel for each xlsRow.

def var rptExcel as handle.
create report rptExcel.
rptExcel:report-data-source = query qExcel:handle.
rptExcel:report-design = "xls_export.jrxml".

/* set up parameters based data */

/* Quote# */
rptExcel:set-report-param("imgPath", "fwd.png").
assign qNumDec = 12345.
rptExcel:set-report-param("q_num", qNumDec).

/* Customer ID */
rptExcel:set-report-param("cust_id", "Customer 1").

/* Contact */
rptExcel:set-report-param("contact", "Big Boss").

/* Telephone */
rptExcel:set-report-param("phone", "1231234567").

/* Fax */
rptExcel:set-report-param("fax", "1231234568").

/* Sold To */
rptExcel:set-report-param("sold_to1", "Big Company").
rptExcel:set-report-param("sold_to2", "1-st line address").
rptExcel:set-report-param("sold_to3", "2-nd line address").
rptExcel:set-report-param("sold_to4", "Bill details").

/* Ship To */
rptExcel:set-report-param("ship_to1", "Big Company").
rptExcel:set-report-param("ship_to2", "1-st line shipping").
rptExcel:set-report-param("ship_to3", "2-nd line shipping").
rptExcel:set-report-param("ship_to4", "Shipping details").

/* Quote Date */
rptExcel:set-report-param("q_date", today).

/* SalesPerson */
rptExcel:set-report-param("s_person", "John Doe").

/* Terms */
rptExcel:set-report-param("terms", "Terms condition").

/* FOB */
rptExcel:set-report-param("fob", "Code").

/* Over-Under */
rptExcel:set-report-param("ovr_und", "1%-2%").

assign comment[1] = "Thanks for cooperation!" 
       comment[2] = "Second line of comment," 
       comment[3] = "five comment lines in total available.".

rptExcel:set-report-param("comment1", comment[1]).
rptExcel:set-report-param("comment2", comment[2]).
rptExcel:set-report-param("comment3", comment[3]).
rptExcel:set-report-param("comment4", comment[4]).
rptExcel:set-report-param("comment5", comment[5]).

/* setting up dynamic rows content, reserving one empty row in a beginning. */
create xlsRow.
create xlsRow. assign est_num = "12345" item = "Item no. 678" quantity = 1500 price = "$1.00" uom = "UOM".
create xlsRow. assign item = "1.0 x 1.0 x 1.0" quantity = 2500 price = "$2.50" uom = "UOM1".
create xlsRow. assign description = "200 Boxes".
create xlsRow.
create xlsRow.
create xlsRow. assign description = "Consist of:".
create xlsRow.
create xlsRow. assign description = "100 White boxes".
create xlsRow.
create xlsRow. assign description = "100 Black boxes".

rptExcel:export-report-xls("xls_export.xls").

The jrxml report file in use:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.5.1.final using JasperReports Library version 6.5.1  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="xls_export" pageWidth="700" pageHeight="841" columnWidth="700" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="3e3d9259-392c-4ccd-beb4-c4f9f219dc44">
    <property name="com.jaspersoft.studio.report.description" value=""/>
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageHeight" value="mm"/>
    <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>
    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
    <parameter name="q_num" class="java.lang.Integer"/>
    <parameter name="cust_id" class="java.lang.String"/>
    <parameter name="contact" class="java.lang.String"/>
    <parameter name="phone" class="java.lang.String"/>
    <parameter name="fax" class="java.lang.String"/>
    <parameter name="sold_to1" class="java.lang.String"/>
    <parameter name="sold_to2" class="java.lang.String"/>
    <parameter name="sold_to3" class="java.lang.String"/>
    <parameter name="sold_to4" class="java.lang.String"/>
    <parameter name="ship_to1" class="java.lang.String"/>
    <parameter name="ship_to2" class="java.lang.String"/>
    <parameter name="ship_to3" class="java.lang.String"/>
    <parameter name="ship_to4" class="java.lang.String"/>
    <parameter name="q_date" class="com.goldencode.p2j.util.date"/>
    <parameter name="s_person" class="java.lang.String"/>
    <parameter name="terms" class="java.lang.String"/>
    <parameter name="fob" class="java.lang.String"/>
    <parameter name="ovr_und" class="java.lang.String"/>
    <parameter name="imgPath" class="java.lang.String"/>
    <parameter name="comment1" class="java.lang.String"/>
    <parameter name="comment2" class="java.lang.String"/>
    <parameter name="comment3" class="java.lang.String"/>
    <parameter name="comment4" class="java.lang.String"/>
    <parameter name="comment5" class="java.lang.String"/>
    <field name="est_num" class="java.lang.String"/>
    <field name="description" class="java.lang.String"/>
    <field name="item" class="java.lang.String"/>
    <field name="quantity" class="com.goldencode.p2j.util.decimal"/>
    <field name="price" class="java.lang.String"/>
    <field name="uom" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="245" splitType="Stretch">
            <textField>
                <reportElement x="500" y="5" width="160" height="20" uuid="5a78ea94-0581-4d2b-b2d5-2f21521b162c"/>
                <textElement>
                    <font size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{q_num}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="310" y="5" width="190" height="20" uuid="a4ce9a1c-98d6-4477-b529-79940b034977"/>
                <textElement textAlignment="Right">
                    <font size="12"/>
                </textElement>
                <text><![CDATA[Quote#:]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="310" y="25" width="190" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="28a5abb6-11d4-4d4f-83a8-5d8a9c51a6b4"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[CUSTOMER ID]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="500" y="25" width="160" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="b65dd7cf-2aab-46ae-9a97-01e603db939c"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[CONTACT]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="310" y="53" width="190" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="305b79f5-7f51-47ce-988a-fcf3692cadf3"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[TELEPHONE]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="500" y="53" width="160" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="7df306d6-a431-4ad9-ba3b-0df1cf3a1cf7"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[FAX]]></text>
            </staticText>
            <image scaleImage="RetainShape" hAlign="Left">
                <reportElement x="0" y="0" width="117" height="85" uuid="db5047ab-63bb-4bbf-9e15-97e2ae77429a"/>
                <imageExpression><![CDATA[$P{imgPath}]]></imageExpression>
            </image>
            <textField>
                <reportElement positionType="Float" x="310" y="40" width="190" height="13" uuid="c19ce38a-620f-44ad-946a-b0e7c9045c76"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{cust_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="500" y="40" width="160" height="13" uuid="3a8d5c7c-703c-47d9-9e7c-b351cbc3e397"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{contact}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="310" y="68" width="190" height="16" uuid="e0c5392e-ad9e-4a45-bff7-2826786f6c06">
                    <property name="net.sf.jasperreports.export.xls.pattern">
                        <![CDATA[[<=9999999]###-####;\(###\) ###-####]]>
                    </property>
                </reportElement>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[Long.valueOf($P{phone})]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="500" y="68" width="160" height="16" uuid="fc71c757-c4bb-41ab-b07b-9fd9231be78e">
                    <property name="net.sf.jasperreports.export.xls.pattern">
                        <![CDATA[[<=9999999]###-####;\(###\) ###-####]]>
                    </property>
                </reportElement>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[Long.valueOf($P{fax})]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="116" width="310" height="15" uuid="d976a488-3d6a-41a6-a96c-e69c38773ebf"/>
                <textFieldExpression><![CDATA[$P{sold_to1}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement mode="Opaque" x="0" y="101" width="310" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="2345416f-6f7f-4c18-abb1-aba7fd7ac72f"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[SOLD TO]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="310" y="101" width="350" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="757804e9-4ffc-4358-85bb-bbca9d4c86c7"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[SHIP TO]]></text>
            </staticText>
            <textField>
                <reportElement x="310" y="116" width="350" height="15" uuid="2831de17-bdc1-4a9e-8c61-b03015288903"/>
                <textFieldExpression><![CDATA[$P{ship_to1}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement positionType="FixRelativeToBottom" mode="Opaque" x="0" y="191" width="80" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="0763d5a6-c9a1-4a6e-b47a-afa2843799ee"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[QUOTE DATE]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="80" y="191" width="230" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="7c83ea82-f6e8-41b4-843e-a0132bcf7f90"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[SALESPERSON]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="310" y="191" width="195" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="ee6e3cba-aae0-4313-a5b3-2ecf01efa325"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[TERMS]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="505" y="191" width="70" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="af1ce179-de4c-4b86-ab41-5bdcf944ee7a"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[FOB]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="575" y="191" width="85" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="c5cbca9a-c631-46b4-bfea-51bd56d0fff0"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[OVER-UNDER]]></text>
            </staticText>
            <textField>
                <reportElement x="0" y="206" width="80" height="16" uuid="66958a64-2f09-4c8c-a92e-00ff6671b0a9">
                    <property name="net.sf.jasperreports.export.xls.pattern" value="M/D/YYYY;@"/>
                </reportElement>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$P{q_date}.dateValue()]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="206" width="230" height="16" uuid="1761f79f-a237-41c4-9589-c99501b1d2d6"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{s_person}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="310" y="206" width="195" height="16" uuid="54ecfc17-ba8f-4962-b1ee-d5fe79c7ce8b"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{terms}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="505" y="206" width="70" height="16" uuid="f99d5005-70f4-4907-8453-fa9f515bfb5b"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{fob}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="575" y="206" width="85" height="16" uuid="45dbbede-1d88-4c99-bc9c-1c77908f03c1"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{ovr_und}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement mode="Opaque" x="0" y="230" width="80" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="82f3287d-78ff-463a-b821-b812f14ec8b3"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[EST#]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="80" y="230" width="230" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="5e03c61e-d233-48bf-8e8f-306c68ba686f"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[DESCRIPTION]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="310" y="230" width="130" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="753ee6df-bfd2-44f5-af34-feee7f230d60"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[ITEM]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="440" y="230" width="65" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="ece278ab-9b23-4142-97af-ad6fa8dffc98"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[QUANTITY]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="505" y="230" width="70" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="22e315e2-a576-4fbf-b98a-280f7d11bac9"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[PRICE]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="575" y="230" width="85" height="15" forecolor="#FFFFFF" backcolor="#000099" uuid="c3e54863-999a-4043-a86a-a9590ca94753"/>
                <textElement textAlignment="Center"/>
                <text><![CDATA[UOM]]></text>
            </staticText>
            <textField>
                <reportElement x="0" y="131" width="310" height="15" uuid="589213c6-6127-4454-a068-c178ff8b844b"/>
                <textFieldExpression><![CDATA[$P{sold_to2}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="146" width="310" height="15" uuid="e6f10837-2f80-46e3-aa8a-d17e23315675"/>
                <textFieldExpression><![CDATA[$P{sold_to3}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="161" width="310" height="15" uuid="dab65c71-13d3-4f7b-b5e5-72c34cc2159c"/>
                <textFieldExpression><![CDATA[$P{sold_to4}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="310" y="131" width="350" height="15" uuid="2099ef6f-c818-4aef-b145-6abf4325843d"/>
                <textFieldExpression><![CDATA[$P{ship_to2}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="310" y="146" width="350" height="15" uuid="1e83a076-19c1-4749-a5d3-b9124320ea39"/>
                <textFieldExpression><![CDATA[$P{ship_to3}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="310" y="161" width="350" height="15" uuid="19178be4-55e3-426f-8044-c1b8a9fe6139"/>
                <textFieldExpression><![CDATA[$P{ship_to4}]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="15" splitType="Stretch">
            <textField isBlankWhenNull="true">
                <reportElement x="0" y="0" width="80" height="15" uuid="3eda1012-c19b-4b0a-91be-f88f686321d7"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{est_num} == null || $F{est_num}.isEmpty() ? null : Integer.valueOf($F{est_num})]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="0" width="230" height="15" uuid="8746df42-5176-4b59-946e-1df21b1bbb3d"/>
                <textFieldExpression><![CDATA[$F{description}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="310" y="0" width="130" height="15" uuid="34744e3e-7be1-49ab-8892-fa18c46a7fb1"/>
                <textFieldExpression><![CDATA[$F{item}]]></textFieldExpression>
            </textField>
            <textField pattern="#,##0.###;(#,##0.###-)" isBlankWhenNull="true">
                <reportElement x="440" y="0" width="65" height="15" uuid="7e835a2e-8e5b-4945-908b-78fe818eeb0b"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{quantity}.isUnknown() ? null : $F{quantity}.longValue()]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="505" y="0" width="70" height="15" uuid="5b730b81-ccc8-4181-af64-c212f6ca3051"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{price}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="575" y="0" width="85" height="15" uuid="884ef408-0c1d-47e9-8dff-1c4ea421484c"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$F{uom}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="80" splitType="Stretch">
            <textField>
                <reportElement x="80" y="0" width="580" height="15" uuid="f2351de4-ba5f-44a1-b667-4b2bfda45127"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{comment1}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="16" width="580" height="15" uuid="91df48fb-f6b8-4648-bf1d-2f7952e96212"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{comment2}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="31" width="580" height="15" uuid="0a4c9db3-b566-4add-acda-ce9b7efe5749"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{comment3}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="46" width="580" height="15" uuid="2e1d9b73-eb50-4a2c-b910-fb609adc2df6"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{comment4}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="61" width="580" height="15" uuid="0b59e72e-de8c-4a62-b478-70b5b2a60fc6"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$P{comment5}]]></textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>

The output XLS file is:

Exported XLS file

The image file fwd.png and report file xls-export.jrxml must be in a current directory when running sample.

Create Browse Reports

Browse reports are conventional .jrxml reports that have only one column and one column title. These column and title are cloned to create required number of columns, then their widths are adjusted so that they are proportionally have the same width relatively to the page width as they have in browse relatively to the browse width.
Browse reports can be run from enhanced browse menu or in 4GL code. In the latter case you can get REPORT object using browse:report attribute.

At this point only one built-in browse report is available.

Java Implementation Details

The primary class that provides the handle based resource is com.goldencode.p2j.reporting.JasperReportWrapper. This class has Java methods that match each 4GL attribute and method:

4GL Attribute Java Method(s)
report-data-source getReportDataSource(), setReportDataSource(QueryWrapper), setReportDataSource(handle)
report-design getReportDesign(), setReportDesign(String), setReportDesign(character)
4GL Method Java Method(s)
set-report-param(character param-name, var param-value) setReportParameter(String, BaseDataType), setReportParameter(String, String), setReportParameter(character, BaseDataType)
export-report-pdf(character output-file-name) exportReportPdf(String), exportReportPdf(character)
export-report-xls(character output-file-name) exportReportXls(String), exportReportXls(character)
export-report-xlsx(character output-file-name) exportReportXlsx(String), exportReportXlsx(character)
export-report-csv(character output-file-name) exportReportCsv(String), exportReportCsv(character)

The Java method names were deliberately mapped directly to the keywords that were added to the language. When OO 4GL support is available, these keywords will be removed and the JasperReportWrapper class will be used directly from 4GL code. When that happens, the method names will be simplified to take advantage of the natural namespace that occurs by using an OO approach.

Please see #3342 for more implementation details.


© 2004-2018 Golden Code Development Corporation. ALL RIGHTS RESERVED.

Report.png (67.4 KB) Stanislav Lomany, 05/06/2018 06:26 AM

xls_export.jpg - Exported XLS file (334 KB) Eugenie Lyzenko, 05/07/2018 05:04 PM