One of the powerful features of Report API is its ability to fetch data from a database directly through JDBC or ODBC. With this approach, all you need to do is to specify the information necessary to connect to the database and the precise form of the SQL statement. Thus, your program can connect to virtually any database provided that a JDBC driver is available or if an ODBC data source has been created for the particular database.

The database and query information must be stored in a DBInfo object prior to constructing the report. Use the following code to instantiate the DBInfo object.

DBInfo dbinfo = new DBInfo(

      "jdbc:hsqldb:help/examples/DataSources/database/woodview",     // URL
      "org.hsqldb.jdbcDriver",                                       // JDBC driver
      "myName",                                                      // Username
      "myPassword",                                                  // Password
      "select * from sales");                                        // SQL Query 
    

Since DBInfo implements the interface IDatabaseInfo, you can use the DBInfo object in the following QbReport constructor:

QbReport(java.lang.Object parent, int reportType, IDatabaseInfo dbinfo, ColInfo[] mapping, java.lang.String template)
    

In some cases, you may not wish to pass the entire database information (such as userid, password, location, driver and the query) to EspressReport. You may want to make the connection yourself and just provide the resultset of the query directly to the API. This can be done by creating a QueryResultSet object from the ResultSet object you have generated and passing that QueryResultSet object as the data source, instead of the DBInfo object.

// Create a QueryResult object from the ResultSet object resultSet
QueryResultSet queryResultSet = new QueryResultSet(resultSet);

// QueryResultSet implements IResultSet, use it in the following QbReport constructor
QbReport(java.lang.Object parent, int reportType, IResultSet data, ColInfo[] mapping, java.lang.String template)
    

In the above example, an instance of class QueryResultSet is created from the ResultSet object passed to the API and this instance is passed to the QbReport constructor to create the report object. Please note that in this scenario, the report is not refreshable. To update the report, you will need to make the connection to the database again and pass the ResultSet object to the report and refresh it yourself.

For a working example using data from a database, see Appendix 2.C.4.2 - Creating the Report.

// create a DBInfo object with JNDI lookup name and query
String JNDIName = "java:comp/env/jdbc/TestDB";
String query = "select * from testdata";

// The environment hashtable is empty for tomcat because EspressManager is
// running inside Tomcat context. If other application server is used,
// need to set INITIAL_CONTEXT_FACTORY and PROVIDER_URL.
Hashtable env = new Hashtable();
DBInfo dbInfo = new DBInfo(JNDIName, query, env);
      

public QbReport(Object parent, int reportType, IDatabaseInfo dbinfo, ColInfo[] colMap, String templateFile);
      

Data for generating a report can also be imported from a data file, which can be a text file as well as an XML formatted file. A report data file (with a .dat extension) is a plain text file where each line represents one record, except the first two lines, which contain the data types and field names, respectively. Here is an example of a data file, which contains four records and where each record has three fields. The first line specifies the data type of each field and the second line specifies the field name.

string, date, decimal
Name, Day, Volume
"John", "1997-10-3", 32.3
"John", "1997-4-3", 20.2
"Mary", "1997-9-3", 10.2
"Mary", "1997-10-04", 18.6
    

The use of the comma character is optional, but most database programs can output a file in this format (except for the first two lines) when exporting records from a table in text format. As a result, even if you do not have a JDBC driver for your database, you can still quickly produce reports. You can simply export the data from your database in text format and then your program can read it using Report API.

For XML format, the specification is as follows:

<EspressData>

      <DataType>string</DataType>
      <DataType>date</DataType>
      <DataType>decimal</DataType>

      <FieldName>Name</FieldName>
      <FieldName>Day</FieldName>
      <FieldName>Volume</FieldName>

      <Row>
            <Data>John</Data>
            <Data>1997-10-3</Data>
            <Data>32.3</Data> </Row>

      <Row>
            <Data>John</Data>
            <Data>1997-4-3</Data>
            <Data>20.2</Data> </Row>

      <Row>
            <Data>Mary</Data>
            <Data>1997-9-3</Data>
            <Data>10.2</Data> </Row>

      <Row>
            <Data>Mary</Data>
            <Data>1997-10-4</Data>
            <Data>18.6</Data> </Row>

</EspressData>
    

For more details about XML Data, please refer to Section 1.7.1.2 - XML Encoding.

Specifying the text file to use is very straight forward. Use the following constructor and replace the variable dataFile with the path (relative or full path) of the data file. If you are connecting to the server, relative paths should be in respect to the EspressReport root directory. Otherwise, the path will be relative to the current working directory.

QbReport(java.lang.Object parent, int reportType, java.lang.String dataFile, ColInfo[] mapping, java.lang.String template)
    

For a working example using data from a text file, see Appendix 2.C.2.2 - Creating the Report.

In addition to the above, EspressReport allows you to retrieve data and query XML files. XML data can be in virtually any format, but you need to specify a DTD file or an XML schema along with the XML data. The following code demonstrates how to set up an XML query:

// Set up the XML Data Source
String xmlfilename = "Inventory.xml";
String dtdfilename = "Inventory.dtd";
String xmlcondition = "/Inventory/Category/Product/ProductID < 45";

XMLFieldInfo[] fields = new XMLFieldInfo[5];
fields[0] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product"}, "ProductID");
fields[0].setAttributeDataType(DTDDataType.INT);

fields[1] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "ProductName"});

fields[2] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "UnitPrice"});
fields[2].setElementDataType(DTDDataType.DOUBLE);

fields[3] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "UnitsInStock"});
fields[3].setElementDataType(DTDDataType.INT);

fields[4] = new XMLFieldInfo(new String[] {"Inventory", "Category", "Product", "ShipDate"});
fields[4].setElementDataType(DTDDataType.DATE);
fields[4].setDateFormat(XMLDataTypeUtil.YYYY_MM_DD);

XMLFileQueryInfo xmlInfo = new XMLFileQueryInfo(xmlfilename, fields, xmlcondition, fields, dtdfilename, false, null);
    

The XMLFieldInfo instance is created using one of two constructors. The first constructor, used to select xml fields, contains one parameter. For this constructor, you need to pass in a String array that specifies each xml tag in the hierarchy leading to the target field. In the above example, fields[1-4] are created using the first constructor. The second constructor, used to select xml attributes, contains two parameters. In addition to the String array parameter, the second constructor also requires another string for the attribute name. In the above example, field[0] is created using this constructor because ProductID is an attribute of Product.

You may have also noticed that for any non-String field, you must explicitly set the data type. Once you have created the XMLFileQueryInfo instance, you can use the following constructor to create the QbReport.

public QbReport(Object parent, int reportType, XMLFileQueryInfo xmlInfo, ColInfo[] colMap, String templateFile, boolean sideBySideLayout);
    

You can also pass in an XML stream instead of an XML file, when using XML data as a data source. To pass in an XML stream, you would pass in the byte array containing the XML data instead of the XML data file name.

In the above example, you can pass in a XML stream via a byte array (for example, a byte array called xmlByteArray) in the XMLFileQueryInfo constructor:

XMLFileQueryInfo xmlInfo = new XMLFileQueryInfo(xmlByteArray, fields, xmlCondition, fields);
    

For a working example using data passed in from an xml file source, see Appendix 2.C.5.2 - Creating the Report.

The API allows input data to be passed directly in memory, as an array. This is made possible by the interface IResultSet (defined in quadbase.reportdesigner.util package). This interface is used to read data in tabular form and is quite similar to the java.sql.ResultSet interface used for JDBC result sets (but is much simpler). Users can provide their own implementation of IResultSet, or use one provided by EspressReport. The simplest implementation is the class DbData (Other classes that provide an IResultSet implementation are QueryResultSet and StreamResultSet). If you can fit all the data you need for the report in memory, you can simply pass the array as an argument in DbData with one line of code. There are three constructors for DbData:

      DbData(java.lang.String s)
            Construct DbData by parsing the data value argument from an HTML page 

      DbData(java.lang.String[] fieldName, java.lang.Object[][] records)
            Construct a new DbData class

      DbData(java.lang.String[] dataType, java.lang.String[] fieldName, java.lang.String[][] records)
            Construct a new DbData class
    

Note that when passing in data, using this approach, for a Summary Break or Cross Tab report, make sure that the data is applicable and is already grouped and sorted to improve performance and generate a report successfully.

We will use the following constructor in the example here.

public DbData(String dataType[], String fieldName[], String records[][])
    

Here, the first argument presents the data types (the first line in the data file) and the second argument presents the field names (the second line). The third argument, records[][], provides an array of records, records[i] being the ith record. The following shows how it works:

String dataType[] = {"varchar", "decimal"};
String fieldName[] = {"People", "Sales"};
String records[][] = {{"Peter", "93"}, {"Peter", "124"},
                     {"John", "110"}, {"John", "130"},
                     {"Mary", "103"}, {"Mary", "129"}};

DbData data = new DbData(dataType, fieldName, records);
    

To create the report, use the following QbReport constructor:

public QbReport(Object parent, int reportType, IResultSet data,
               ColInfo[] colMap, String templateFile);
    

You can pass sorted data to EspressReport to improve performance considerably. If the data is already sorted, you can set a flag to true in the constructor to avoid EspressReport sorting the data again. The constructor is given below:

public QbReport(Object parent, int reportType, IResultSet data,
                ColInfo[] colMap, String templateFile, boolean sideBySideLayout, boolean isDataSorted);
    

This is important especially when you are generating a summary break report with multiple break levels. If data in memory is already sorted (e.g. sorted by a database engine), you can take advantage of the fact and use this feature to prevent the report engine from unnecessarily building extra internal data structures and performing sorting, which consumes a large amount of memory and processor resources. Hence, performance can be greatly improved.

For a working example using data passed in an array in memory, see Appendix 2.C.1.2 - Creating the Report.

For maximum flexibility, you can retrieve and prepare the data set in any way you want and pass it to the report engine. To pass in your class file as the data source, your class file must implement the IDataSource interface. Given below is a simple example that implements IDataSource:

public class CustomClassData extends Applet implements IDataSource {

      // Setting DbData for passing data as arguments
      String dataType[] = {"string", "String", "double"};
      String fieldName[] = {"Destination", "Time", "Price"};
      String records[][] = {{"Mayfair", "13:43", "3.50"},
            {"Bond Street", "13:37", "3.75"},
            {"RickmansWorth", "13:12", "5.25"},
            {"Picadilly", "13:24", "3.00"}};
      DbData data = new DbData(dataType, fieldName, records);

      public IResultSet getResultSet()
      {
            return data; } 

}
    

Full Source Code

The example above creates data (DbData instance) and stores it in memory. When the getResultSet() method is called, it returns the DbData object which implements IResultSet. Keep in mind that it is not necessary to create your data in this manner. As long as you are able to return an object that implements IResultSet, you can obtain the data from any data source. Use the following constructor to create your report:

QbReport(java.lang.Object parent, int reportType, int fileType, java.lang.String filename, ColInfo[] mapping, java.lang.String template)
    

For custom class files, set the fileType to QbReport.CLASSFILE and the filename to the name of the classfile.

Please note that if you are passing in your own class file as the data source and you are using the EspressManager, the class file must be accessible from the CLASSPATH of the EspressManager.

You can also pass in a parameterized class file as the data source for the report. The parameter is obtained at run-time from the user and the data is then fetched and used to generate the report. Here is an example for a parameterized class file, this is the same file used in Section 2.3.5.7.11.5 - Open Report Designer with a Specific Class File Data Source .

public class ParamClassFile implements IParameterizedDataSource
{
	String url = "jdbc:hsqldb:help/examples/DataSources/database/woodview";
	String driver = "org.hsqldb.jdbcDriver";
	String username = "sa";
	String password = "";

	//	Specify what the parameter properties are.
	public IQueryInParam[] getParameters()
	{
		SimpleQueryInParam[] params = new SimpleQueryInParam[2];
		//		SimpleQueryInParam(name, promptString, mapToColumn, TableName, ColumnName, dataType, defaultValue, actualValue)
		params[0] = new SimpleQueryInParam("price", "Max price:", false, null, null, Types.DOUBLE, new Double(500.00), null);
		params[1] = new MySimpleQueryInParam("popular", "Popular Items Only:", false, null, null, Types.VARCHAR, new String("NO"), null);
		return params;
	}

	private class MySimpleQueryInParam extends SimpleQueryInParam implements IQueryParamValuesProvider
	{
		public MySimpleQueryInParam(String paramName, String promptName, boolean mapToColumn, 
				String tableName, String columnName, int sqlType, Object defaultValue, Object value)  {
			super(paramName, promptName, mapToColumn, tableName, columnName, sqlType, defaultValue, value);
		}
		public Vector getSelectionChoices() {
			Vector choices = new Vector();
			choices.add(new String("Yes"));
			choices.add(new String("No"));
			return choices;
		}
	}

	//	Specify what data is to be returned
	public IResultSet getResultSet(IQueryInParam[] params)
	{
		double price = 500.00;
		int units = 999;

		if ((params != null) && (params.length >= 1))
		{
			Object obj = params[0].getValue();
			if ((obj != null) && (obj instanceof Double)) price = ((Double)obj).doubleValue();
			obj = params[1].getValue();
			if((obj != null) && (obj instanceof String) && ((String)obj).equalsIgnoreCase("yes")) units = 15; 
		}

		try{
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			String query = "SELECT ProductName, Description, UnitPrice, UnitsInStock FROM Products WHERE UnitPrice < " 
				+ price + " AND UnitsInStock < " + units;
			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			QueryResultSet qry = new QueryResultSet(rs);

			return qry;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return null; 
	}

}
    

Full Source Code

The parameterized class file must implement IParameterizedDataSource. The above example obtains data from the WoodView HSQL database, so you will need to add database HSQL JDBC driver (hsqldb.jar) to your classpath. The driver is located in the <EspressReportInstall>/lib directory.

The query contains two parameters. The first parameter allows the user to set the maximum price for the query results. The parameter is of type double and the default is set to 500.00. The second parameter is a custom parameter and its purpose is to give the user a yes or no option to determine whether or not to show popular items only. To construct a custom parameter, create a class that extends the SimpleQueryInParam class, implements the IQueryParamValuesProvider interface and overwrites the getSelectionChoices() method. If the user selects yes, then in getResultSet() only items with less than 15 UnitsInStock will be returned.

You can use the same constructor as before to create a report using this parameterized class. When using parameterized class file as the data source, keep in mind that you can not map it to a column directly (i.e. set the table name and column name in the SimpleQueryInParam constructor). In order to map a parameter to a column, you will have to use a custom parameter similar to the one shown above. However, the getSelectionChoices() method will look as follows:

public Vector getSelectionChoices() {
	System.out.println("getSelectionChoices called");
	try {
		Class.forName("org.hsqldb.jdbcDriver");

		String url = "jdbc:hsqldb:help/examples/DataSources/database/woodview";
		Connection conn = DriverManager.getConnection(url, "sa", "");
		Statement stmt = conn.createStatement();
		String query = "SELECT DISTINCT " + getColumnName() + " FROM " + getTableName();
		ResultSet rs = stmt.executeQuery(query);
		Vector v = new Vector();

		while (rs.next()) {
			switch (getSqlType()) {
			case Types.INTEGER:
				v.add(new Integer(rs.getInt(1)));
				break;

			case Types.VARCHAR:
				v.add(rs.getString(1));
				break;
			}
		}

		stmt.close();
		conn.close();

		return v;
	} catch (Exception ex) {
		ex.printStackTrace();
	}
	return null;
}
    

Full Source Code

In addition to mapping the parameter to a column, this example also uses a multi-value parameter. Although the process is similar to using a single value parameter, there are some places where the code deviates from the norm. Please see the comments in the above source code for details. Also, more information on using parameters from the API can be found in Appendix 2.C.7 - Parameterized Report.

For a working example using data passed in from a custom class file, see Appendix 2.C.3.2 - Creating the Report.

Data can be passed from an EJB data source to the report by allowing users to query data directly from an entity bean. To add an EJB as a data source, the EJB must first be deployed in the application server, and the client JAR file containing the appropriate stub classes must be added to your classpath (or the -classpath argument of the EspressManager batch file when using the API in conjunction with EspressManager).

To construct a report using an EJB as a data source, first you must construct a EJBInfo object with information for connecting to the EJB. The constructor is shown below:

public EJBInfo(java.lang.String jndiName,
               java.lang.String homeName,
               java.lang.String remoteName,
               java.lang.String selectedMethodName,
               java.lang.Object[] selectedMethodParamVal)
    

For example, the following lines create an EJBInfo instance that connects to the ProductEJB data source.

Object[] vals = new Object[1];
vals[0] = new String("Printer");

EJBInfo ejbInfo = new EJBInfo("ProductEJB", "ejb.ProductHome",
                              "ejb.Product", "findByCategory", vals);
    

Finally, use the following constructor to create the QbReport object:

public QbReport(Object parent, int reportType, EJBInfo ejbInfo, ColInfo[] mapping, String template);
    

EspressReport allows to retrieving data from SOAP services. To use a SOAP data source, you need to provide a location of WSDL file, which contains all the necessary information. The location can be either absolute path on the server or path relative to your EspressReport installation directory or URL.

Constructor for SOAP data source looks like this:

SOAPQueryFileInfo(String wsdlURI, QName serviceName, String portName, String operationName, XMLFieldInfo[] xmlFieldInfo, SOAPParam[] parameters)
    

The wsdlURI is URL or absolute/relative path to the WSDL file. The serviceName of type QName is name of SOAP service. The following constructor is used:

QName(String namespaceURI, String localPart),
    

where the namespaceURI is URI namespace (a part of the serviceName string in curly brackets) and the localPart is a service name (the rest of the string). For example, in the following servicename {http://www.webservicex.net}WeatherForecast . The http://www.webservicex.net is namespaceURI and the WeatherForecast is a service name.

The portName and operationName in the SOAPQueryFileInfo constructor are names of a port and operation. The XMLFieldInfo array is the same as for XML data sources (see Appendix 2.B.3 - Data from an XML Data Source). Finally, the SOAPParam field is dealing with parameters and uses the following constructor:

SOAPParam(String paramName, int sqlType, String paramPrompt, Object defaultValue, Object value, boolean alwaysUseDefault),
    

where the paramName is a parameter name, the sqlType represents parameter data type, which is a constant from java.sql.Types. The paramPrompt is the parameter prompt string, the defaultValue is the parameter default value, the value is the parameter value and the alwaysUseDefault of boolean type says whether the default value will be always used or not. This means that this parameter value will be fixed and users will not be prompted for it.

To create the QbReport/QbChart object use the constructors below:

      QbReport(Object parent, int reportType, SOAPQueryFileInfo soapInfo, ColInfo[] mapping, String template, Properties props)

      QbChart(Applet applet, int dimension, int chartType, SOAPQueryFileInfo soapInfo, boolean doTransposeData, int[] transposeCol, IColumnMap cmap, String template)
    

Full Source Code

EspressReport also provides the functionality to merge multiple data sources together. You can create a DataSheet object from any combination of data sources, for example, data file, database or IResultSet. You can use a QbReport constructor to create a report object from an array of DataSheet objects.

The following example program fragment demonstrates how to combine the data from the examples in the above sections:

// Declaration of DataSheet object to be used to merge data
DataSheet dataSheet[] = new DataSheet[3];

// Declaration For Database (Data from Database section)
DBInfo dbinfo = new DBInfo("jdbc:hsqldb:help/examples/DataSources/database/woodview",
"org.hsqldb.jdbcDriver", "sa","","select * from Orders");

//Declaration For Data Passed in from Memory (Data Passed in from Memory section)
String dataType[] = {"varchar", "decimal"};
String fieldName[] = {"People", "Sales"};
String records[][] = {{"Peter", "93"}, {"Peter", "124"},
                      {"John", "110"}, {"John", "130"},
                      {"Mary", "103"}, {"Mary", "129"}};
DbData data = new DbData(dataType, fieldName, records);

// Create DataSheet from data file (Data from a Text File section)
// DataSheet(Applet applet, String dataFile)
dataSheet[0] = new DataSheet(this, "../data/1_A_7_TextData.dat");

// Create DataSheet from database (Data from a Database section)
// DataSheet(Applet applet, IDatabaseInfo dbInfo)
dataSheet[1] = new DataSheet(this, dbinfo);

// Create DataSheet from IResultSet  (Data Passed in from Memory section)
// DataSheet(Applet applet, IResultSet data)
dataSheet[2] = new DataSheet(this, data);
    

Then, use the following QbReport constructor to create the report:

QbReport(java.lang.Object parent, int reportType, DataSheet[] dataSheet, ColInfo[] mapping, java.lang.String template)
    

Note that after you have created a DataSheet object, you can modify it (add, delete, or update row values) by using DataSheet API. Data is not refreshable if merging data from IResultSet.