Login | Register
My pages Projects Community openCollabNet

eReport Manual

2003


Chapter 1. Basics

Overview

Disclaimer

No liability for the contents of this document can be accepted. Use the concepts, examples and information at your own risk. There may be errors and inaccuracies, that could be damaging to your system. Proceed with caution, and although this is highly unlikely, the author(s) do not take any responsibility.

All copyrights are held by their by their respective owners, unless specifically noted otherwise. Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark. Naming of particular products or brands should not be seen as endorsements.

General

eReport hopes to be an easy to use reporting tool to allow you to view your data in microsoft excel files. eReport is written in java to allow use on other operating systems other than windows. This will allow you to fetch data from most databases. To generate each report eReport parses a XML file created by you (maybe from within Eclipse using the Eclipse Plugin) and either creates or updates an Excel SpreadSheet using SQL and other information defined in the XML file. Fonts and colors etc. can be defined in the XML file for each cell and there is some support for formulas. There is no way to define a chart in the XML definition but if you have a spreadsheet with an existing chart or charts you can modify the information in the cells referenced by the chart or charts.

Requirements

To run eReport you will need a Java2 runtime environment and a computer capable of running it. eReport will not be of much use if you do not have a datasource of some kind that has a jdbc driver, eReport has been used with accounting packages such as BusinessVision32 (through jdbc-odbc bridge) as well as with linux RDBMS such as PostgreSQL and MySQL. The Eclipse plugin's visual designer will help you create your report definition file. Finally if you want to view your reports you will need Microsoft Excel. To use the eclipse plugin you must have Eclipse 2.1 and the eclipse GEF plugin 2.1 both available from www.eclipse.org

Please Note:

If you wish to use paramerterized SQL statments you need jdk1.4 as we use the regex packages which are new to jdk1.4.

This software also relies on several third party open souce libraries. You should not need to worry about this if you are running your reports from within Eclipse as the libraries are included in the plugin. If you are distributing your own application or creating reports standalone from batch files or shell scripts you will have to make sure these packages are included in your classpath. You will need poi and commons_lang both from apache.org . Jdom will also have to be on you classpath. Xerces is also used to parse the XML file.

Please Note:

To run the Eclipse plugin you will need the Eclipse Plugin GEF 2.1 installed.

Chapter 2. Eclipse Plugin

Overview

The Eclipse plugin for eReport was created to help create the eReport XML definition file. It includes a visual designer as well as a text view. You can run the Parser from within eclipse to create your spreadsheet. To use the plugin you must have the Eclipse GEF plugin 2.1 installed.

Eclipse Plugin Installation

If you do not have Eclipse 2.1 or the GEF 2.1 plugin you will have to get them from www.eclipse.org. Once you have Eclipse installed you can download the eReport plugin (The plugin is in the file sharing area of ereport.tigris.org). Once you have downloaded the plugin you extract the eReport zip file in your Eclipse plugins directory. This will create two new plugins.

Eclipse Plugin use

To get started with eReport you need an open project. If you do not have a existing project you can create a new one by clicking on File, selecting New and clicking Project. If this project is only for eReport use select Simple and click the Next button. From here you give your project a name and click finish. Once you have an open project you can use the eReport wizard to get a template eReport definition file. To start the wizard click on File select New and click Other. Choose Simple and select eReport Wizard and click the Next button. If needed choose a folder and give the eReport definition file a name or use the defaults. Click finish and the wizard will create skeleton eReport definition file and open it for you.

To use all the features of the visual designer you should open a properties view, outline view, navigator view, and eReport Console View. These are all available from the window menu if they are not already open (you may have to click other to see all available options). To add Elements to the visual designer you click on the Element you want on the pallete and then click on the visual designer where you want to drop the Element. The properties view allows you to modify the value of each Element and its attributes. You can also edit the XML file by hand by choosing to edit the file in source mode by using the source tab. Once you have your definition file created you can create your excel file by clicking File and then Run XML to run the parser.

Chapter 3. Report Definition File

XML Overview

We tried to keep the XML DTD as simple as possible. Each XML file must have a "WORKBOOK" tag and inside that tag we must define a "SHEET" tag or multiple "SHEET" tags. Inside a SHEET we must have at least one "ROW" or "RESULTSET" tag. A "ROW" represents a row in the Excel WorkSheet. A "RESULTSET" tag represents multiple rows and is generated from a SQL query sent to a database. To make use of a "RESULTSET" tag you must have already configured a database connection using the "DB" tag. The "DB" tag is optional but if you want to fetch data from a datasource you need it. Inside the "DB" tag you must specify a "DB_DRIVER", "DB_PASSWORD", "DB_USER", "DB_PATH", and "DB_URL". "DB_PATH" is the path to your jdbc driver and the rest is required by your JDBC driver. Inside the "RESULTSET" or "ROW" tags you need to define at least one "COLUMN" element. A "COLUMN" can be thought of as a spreadsheet cell if defined within a "ROW" element. If a "COLUMN" element is defined in a "RESULTSET" you can think of it as a column in the spreadsheet. Within a "RESULTSET" element you should have a column defined for every column you expect the SQL query to return. Inside the "COLUMN" element we must specify a "VALUE" element which represents the data in the cell. Also inside a "RESULTSET" element we must write a "SQL" element that represents the SQL query we will sent to the database. There are also some optional tags we will discuss later. Below is a snippet of XML that might help clear things up.


 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE TOEXCEL SYSTEM "TOEXCEL.dtd">
<TOEXCEL>
  <DB>
    <DB_DRIVER>org.postgresql.Driver</DB_DRIVER>
    <DB_PASSWORD>*********</DB_PASSWORD>
    <DB_USER>postgres</DB_USER>
    <DB_PATH>c:\jdbc7.0-1.2.jar</DB_PATH>
    <DB_URL>jdbc:postgresql://*****/******</DB_URL>
  </DB>
  <WORKBOOK Name="c:\changeme.xls" Exists="false">
    <SHEET Name="sheet1" Exists="false" Crop="false">
      <ROW Exists="false" id="-1" Height="-1">
        <COLUMN Width="25" Align="0" DataFormat="General" id="0">
          <VALUE Type="Text"># html_escaped=YES</VALUE>
        </COLUMN>
      </ROW>
      <ROW Exists="false" id="-1" Height="-1">
        <COLUMN Width="25" Align="0" DataFormat="General" id="0">
          <VALUE Type="Text"># quoted=YES</VALUE>
        </COLUMN>
      </ROW>
      <ROW Exists="false" id="-1" Height="-1">
        <COLUMN Width="25" Align="0" DataFormat="General" id="0">
          <VALUE Type="Text">product_url</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="1">
          <VALUE Type="Text">name</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="2">
          <VALUE Type="Text">description</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="3">
          <VALUE Type="Text">price</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="4">
          <VALUE Type="Text">image_url</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="5">
          <VALUE Type="Text">offer_id</VALUE>
        </COLUMN>
      </ROW>
      <RESULTSET Exists="false" id="-1" Height="-1">
        <COLUMN Width="25" Align="0" DataFormat="General" id="0">
          <VALUE Type="Database">1</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="1">
          <VALUE Type="Database">2</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="2">
          <VALUE Type="Database">3</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="3">
          <VALUE Type="Database">4</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="4">
          <VALUE Type="Database">5</VALUE>
        </COLUMN>
        <COLUMN Width="25" Align="0" DataFormat="General" id="5">
          <VALUE Type="Database">6</VALUE>
        </COLUMN>
        <SQL IsPreparedStmt="false" ParamFile="null">select  
	'http://www.somedomain.com/pmemories/store/details.jsp?item='||
	item||'&amp;caption='||name AS product_url,  name,name||' '||item||' '
	||description AS  description,price,
	'http://www.somedomain.com/pmemories/store/images/images'||item||'
	.jpg' AS image_url,item from  invent where picture=true</SQL>
      </RESULTSET>
      <HEADER>
        <HEADER_LEFT>header left</HEADER_LEFT>
        <HEADER_RIGHT>Header right</HEADER_RIGHT>
        <HEADER_CENTER>Paul Pound</HEADER_CENTER>
      </HEADER>
      <FOOTER>
        <FOOTER_LEFT>footer left</FOOTER_LEFT>
        <FOOTER_RIGHT>footer right</FOOTER_RIGHT>
        <FOOTER_CENTER>footer center</FOOTER_CENTER>
      </FOOTER>
    </SHEET>
  </WORKBOOK>
</TOEXCEL>

DTDs used by eReport

There are two dtds used by eReport. One defines the layout of the Excel Spreadsheet and its relationship to a database or multiple databases. The other dtd allows you to use parameters in your sql statements.

TOEXCEL.dtd

The main dtd used by eReport to create or edit Excel spreadsheets.

<?xml version='1.0' encoding='UTF-8'?>

<!--
    Typical usage:

    <?xml version="1.0"?>

    <!DOCTYPE TOEXCEL SYSTEM "TOEXCEL.dtd">

    <TOEXCEL>
    ...
    </TOEXCEL>
-->

<!ELEMENT BORDER EMPTY>
<!ATTLIST BORDER
    Right CDATA #IMPLIED
    Bottom CDATA #IMPLIED
    Left CDATA #IMPLIED
    Top CDATA #IMPLIED
    Color CDATA #IMPLIED
  >

<!ELEMENT COLUMN (FONT|BORDER|VALUE)*>
<!ATTLIST COLUMN
    id CDATA #IMPLIED
    Width CDATA #IMPLIED
    Align CDATA #IMPLIED
    Heading CDATA #IMPLIED
    DataFormat CDATA #IMPLIED
  >

<!ELEMENT DB (DB_URL|DB_PASSWORD|DB_USER|DB_DRIVER|DB_PATH)*>

<!ELEMENT DB_DRIVER (#PCDATA)>

<!ELEMENT DB_PASSWORD (#PCDATA)>

<!ELEMENT DB_PATH (#PCDATA)>

<!ELEMENT DB_URL (#PCDATA)>

<!ELEMENT DB_USER (#PCDATA)>

<!ELEMENT FONT EMPTY>
<!ATTLIST FONT
    Offsettype CDATA #IMPLIED
    Underline CDATA #IMPLIED
    Strikeout CDATA #IMPLIED
    Name CDATA #IMPLIED
    Weight CDATA #IMPLIED
    Color CDATA #IMPLIED
    Size CDATA #IMPLIED
  >

<!ELEMENT FOOTER (FOOTER_LEFT|FOOTER_CENTER|FOOTER_RIGHT)*>

<!ELEMENT FOOTER_CENTER (#PCDATA)>

<!ELEMENT FOOTER_LEFT (#PCDATA)>

<!ELEMENT FOOTER_RIGHT (#PCDATA)>

<!ELEMENT HEADER (HEADER_RIGHT|HEADER_LEFT|HEADER_CENTER)*>

<!ELEMENT HEADER_CENTER (#PCDATA)>

<!ELEMENT HEADER_LEFT (#PCDATA)>

<!ELEMENT HEADER_RIGHT (#PCDATA)>

<!ELEMENT RESULTSET (SQL|COLUMN)*>
<!ATTLIST RESULTSET
	Exists CDATA #IMPLIED
	id 		CDATA #IMPLIED
	Height CDATA #IMPLIED
	>

<!ELEMENT ROW (COLUMN)*>
<!ATTLIST ROW
    Exists CDATA #IMPLIED
    id 		CDATA #IMPLIED
    Height CDATA #IMPLIED
>

<!ELEMENT SHEET (HEADER|FOOTER|ROW|RESULTSET)*>
<!ATTLIST SHEET
    Name CDATA #IMPLIED
    Exists CDATA #IMPLIED
    Crop CDATA #IMPLIED
  >

<!ELEMENT SQL (#PCDATA)>
<!ATTLIST SQL
	IsPreparedStmt CDATA #IMPLIED
	ParamFile	CDATA #IMPLIED
>

<!ELEMENT TOEXCEL (DB|WORKBOOK)*>

<!ELEMENT VALUE (#PCDATA)>
<!ATTLIST VALUE
    Type CDATA #IMPLIED
  >

<!ELEMENT WORKBOOK (SHEET)*>
<!ATTLIST WORKBOOK
    Name CDATA #IMPLIED
    Exists CDATA #IMPLIED
  >

TOEXCELParameters.dtd

This file defines the xml file that we use to hold parameters if you are using parameterized SQL statements.


<?xml version='1.0' encoding='UTF-8'?>
<!ELEMENT TOEXCELPARAMS (PARAMETERS)*>

<!ELEMENT PARAM (#PCDATA)>
<!ATTLIST PARAM
    id CDATA #IMPLIED
   >
<!ELEMENT PARAMETERS (PARAM)*>

Below is an example of a SQL query that uses parameters and an example paramaters.xml that would allow you to insert the actual values into your SQL.

EXAMPLE SQL Element from a RESULTSET ELEMENT


 <SQL IsPreparedStmt="true" ParamFile="c:\auctions\params.xml">
		select ?,I_DESCRIPTION,I_PROD_CODE,I_COST_CURRENT,I_COST_AVERAGE
		,I_SELL_PRICE01,I_ONHAND_QTY,I_COMMITTED_QTY,?,I_ON_PURCHASE_QTY
		,I_TYPE,I_USER_DEF2 FROM INVFULL where I_PROD_CODE=? 
      </SQL>

and the params.xml file that goes with it.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE TOEXCEL SYSTEM "TOEXCELPARAMS.dtd">
<TOEXCELPARAMS>
	<PARAMETERS>
		<PARAM id="0">
			I_PART_NO
		</PARAM>
		<PARAM id="1">
			I_BACKORDER_QTY
		</PARAM>
		<PARAM id="2" >
			'FC'
		</PARAM>
	</PARAMETERS>
	<PARAMETERS>
		<PARAM id="0">
			I_PART_NO
		</PARAM>
		<PARAM id="1">
			I_BACKORDER_QTY
		</PARAM>
		<PARAM id="2" >
			'FD'
		</PARAM>
	</PARAMETERS>	
</TOEXCELPARAMS>

Elements Defined

  • <FROMEXCEL>
    

    Opening element in the XML file has no attributes

  • <DB>
    

    This element has no attributes but contains other elements that define a JDBC connection to a database that has JDBC drivers. Defined within a

  • <DB_USER>
    

    Has no attributes and contains the username you want to use to connect to the database with.

  • <DB_PATH>
    

    Has no attributes and contains the path to the JDBC driver you want to use to connect to the database.If you are using the jdbc-odbc bridge this should be the path to the java runtimes rt.jar.

  • <DB_PASSWORD>
    

    Has no attributes and contains the password to use to connect to the database. Currently this is stored in plan text.

  • <DB_URL>
    

    This element has no attributes and contains the url used to connect to the database. Your JDBC driver should contain documentation on how this should be formatted.

  • <DB_DRIVER>
    

    This element has no attributes and contains the actual class to use to connect to the specified database with.

  • <WORKBOOK Name="c:\changeme.xls" Exists="false">
    

    This element marks the starting of a new workbook. Everything within this element refresences this workbook. Attribute Name is the actual path to this workbook. For example c:\myWorkbook.xls. Attribute Exists tells the parser if it is updating an existing workbook or creating a new one. Valid values for the Exists attribute are "true" or "false". If the attribute Name points to an existing file and the attribute exists="false" than the existing workbook is overwritten. This Element has to have at least one <SHEET> element defined.

  • <SHEET Name="sheet1" Exists="false" Crop="false">
    

    This defines a worksheet within a workbook. Attribute Name specifies the name of the worksheet. Attribute Exists tells us if we are updating an existing SHEET or creating a new one. Valid values for the Exists attribute are true or false. If the attribute Exists="false" for the WORKBOOK that references this SHEET than the attribute Exists for this SHEET must be false as well. The attribute Crop really only applies to existing worksheets. It lets you eliminate any rows that may have existed before we modified the sheet but we do not want now. Basically if we have less rows in the new version of our sheet it lets you decide if you want to keep the rows from the old version of the sheet that are greater than the last row you defined in the new version. This Element has to have at least one <ROW> element defined.

  • <ROW Exists="false" id="-1" Height="-1">
    

    This element represents a row in a worksheet. Attribute Exists tells the parser whether the ROW exists or not. The Attribute id is the row # in the worksheet. The attribute id should be a number 0 or greater or -1 if you want this refer to the next row in the worksheet. Attribute Height lets you set the height of the row. Valid values for height are positive numbers or -1 if you want to use the default height. This Element has to have at least one <COLUMN> element defined.

  • <RESULTSET Exists="false" id="-1" Height="-1">
    

    This element represents mutliple rows in a worksheet. The attributes for RESULTSET are the same and have the same valid values as the attributes for a ROW element. This element must contain a <SQL> element. This element must also define at least one <COLUMN> element and you will probably want to define a <COLUMN> element for each column you expect the SQL query to return. You can also define other columns that do not correspond to the columns in the result set. For Instance one <COLUMN> in the <RESULTSET> may be used to hold a formula that performs some action on the data taken from the SQL query.

  • <COLUMN Width="25" Align="0" DataFormat="General" id="0">
    

    If this element is within a <ROW> element than you can think of it as an actual cell in the worksheet. If this element is within a <RESULTSET> than you can think of it as a column of worksheet cells. Attribute Width sets the width of the cell or column. The actual width of this column will be the width you set the last reference to this column to. Valid values for width are numbers greater than 0. The attribute Align lets you set the alignment within this cell or column. Valid values for Align are 0 through 6 with 0=general, 1=left, 2=center, 3=right, 4=fill, 5=justify, 6=center across selection. The attribute DataFormat specifies how to format the data within the cell. Valid values for DataFormat are the strings:

    "General"<br>
    "0"
    "0.00"
    "#,##0"
    "#,##0.00"
    "($#,##0_);($#,##0)"
    "($#,##0_);[Red]($#,##0)"
    "($#,##0.00);($#,##0.00)"
    "($#,##0.00_);[Red]($#,##0.00)"
    "0%"
    "0.00%"
    "0.00E+00"
    "# ?/?"
    "# ??/??"
    "m/d/yy"
    "d-mmm-yy"
    "d-mmm"
    "mmm-yy"
    "h:mm AM/PM"
    "h:mm:ss AM/PM"
    "h:mm"
    "h:mm:ss"
    "m/d/yy h:mm"
    "(#,##0_);(#,##0)"
    "(#,##0_);[Red](#,##0)"
    "(#,##0.00_);(#,##0.00)"
    "(#,##0.00_);[Red](#,##0.00)"
    "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"
    "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"
    "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
    "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
    "mm:ss"
    "[h]:mm:ss"
    "mm:ss.0"
    "##0.0E+0"
    "@" - This is text format.
    "text" - Alias for "@"
    

    The attribute id is the number of the column in the worksheet. column "a" would be an id=0, "b" would be id=1, "c" would be id=2 etc.Each column must contain one <VALUE> element.

  •  <VALUE Type="Database">3</VALUE>
    

    This elements the data in a cell. Attribute Type is the type of data inside this cell. Valid values for type are Type="Text", Type="Database", Type="Formula" or Type="Number". If type is equal to "Number" or "Text" than the value of the element is displayed in the cell. If Type="Formula" than this cell is set to contain a formula and the formula will be parsed and the result will be displayed in the cell. The actual calculations are done by Excel. If Type="Database" than the value of the element <VALUE> represents the column in the ResultSet to get the data from. In the above example this cell would display whatever was in the third column of the resultset. The first column is = to 1 and the second column would be 2.

  • <SQL IsPreparedStmt="false" ParamFile="null">
    

    This element contains a SQL query to execute. The attribute IsPreparedStmt specifies whether or not this SQL query contains parameters that need to be replaced by values (represented by ?).Valid values for IsPreparedStmt are "true" or "false". The attribute ParamFile is the file to get the parameters value from. This parameter file should follow the TOEXCELParameters.dtd defined earlier in this manual.

  • <FONT Name="Arial" Size="12" Offsettype="0" Weight="8380" Color="8" 
    Strikeout="no" Underline="0" />
    
    

    This element is a child of a <COLUMN> element. It is optional, but if used yets you define a font for the <COLUMN> it is part of. Attribute Name is the name of the Font to use. It can be any font that Excel itself can use. The attribute Size specifies the size of the font. The attribute Offsettype is the Offset type to use. Valid values for Offsettype are 0,1, or 2. The attribute weight is the weight of the Font. Valid values for Weight are integers between 100 and 1000. The attribute Color specifies what color to use for this Font. Valid values are 8 to 64 inclusive. The Strikeout attribute can = "yes" or "no". The Underline attribute specifies what type of underline to use for this font. Valid values are 0=no underline, 1=single, 2=double, 21=single accounting, 22=double accounting.

  • <BORDER>
    
    

    to do

Using Formula

eReport depends on poi so you should be able to use any formula that is valid using poi. Currently we are using an older version of poi but hope to test eReport with the latest version soon.

eReport includes constants that can be used in your formula expressions. When designing your report you may not know exactly what row you are referencing, these contants allow you to reference the row you are currently processing as well as the last row defined and the first row referenced by a RESULTSET element. You can also reference rows by adding or subtracting from any of these constants. These constants include

  • [%CurrentRow%] represents the row we are currently processing
  • [%LastRow%] is the last row we processed in the worksheet
  • [%FROWOFRS%] is the first row of the last RESULTSET element defined.

An example:

  •  ><VALUE Type="Formula">D[%CurrentRow%]&E[%CurrentRow-1%]</VALUE>  
     would become D3&E2 if we were processing row #3.
    

Please Note:

Not all types of formula are supported. As stated we use poi to create the spreadsheets and only formula that are valid in poi will work here as well.

Headers and Footers

We also support some constants for use with headers and footers. These inlude

  • =Date:
  • =NumOfPages:
  • =Page:
  • =File:
  • =Sheet:
  • =Time:

An example:

<HEADER>
        <HEADER_LEFT>=Date: =Time:</HEADER_LEFT>
        <HEADER_CENTER>text</HEADER_CENTER>
        <HEADER_RIGHT>=File:</HEADER_RIGHT>
      </HEADER>


All of the above should include the equals sign. Their use should be pretty obvious.

Chapter 4. Known Problems

Table of Contents

Known Issues

Known Issues

todo

Chapter 5. Future

Table of Contents

Improvements

Improvements

Most of the immediate improvements I would like to see right now besides better documentation and the additions of some examples are related to the Eclipse Plugin. We need to create more editParts. Right now all elements are represented by the same EditParts. We also need to create or integrate a layout manager or managers to put our figures on the screen in logical places. Right now we are using xylayout and some pretty basic layout methods. If you have more ideas please let us know.

I would also like to create some convenience or wrapper classes to make the XML file easier to create. Such as refering to columns asa,b,c etc instead of using 0,1,2.

 

Unique Chrisitian Gifts including Holy Cards, Pins, Plates, Prints, Crosses, Crucifixes, Rosaries, Pendants and gifts related to the Roman Catholic Church