The two appendices in this Chapter (Appendix 7.A - Getting the Report Data and Appendix 7.B - Creating the Report) contain information to help you build a report from scratch (without using the Designer). Keep in mind that this method is typically not recommended as it will make the report more difficult to deploy and maintain. However, under certain circumstances, it may be necessary to construct reports in this manner.
The following is an example of a QbReport
constructor. Although there are numerous variations available, the typical QbReport
constructor requires at least three parameters. To create a new report, you must specify the report type, the input data source information, and a mapping of the data columns to the respective columns of the report.
QbReport(java.lang.Object parent, int reportType, IResultSet data, ColInfo[] mapping, java.lang.String template)
The report template is not required (can be null) and the parent object is only required if the report is used in an applet, but the other three parameters must always contain meaningful information. This appendix takes an in depth look at the data parameter and the methods for connecting to different data sources. Appendix 7.B - Creating the Report discusses the report type, the column mapping, and the actual creation of the report. Appendix 7.B - Creating the Report also contains working examples for you to try.
The first step to creating any report is to obtain the data. Data may be retrieved from one of several different types of sources. These sources include:
Fetch the data from a local or remote database using a JDBC or ODBC driver. All you need to do is to provide the information of how to connect to the database and the exact form of the query. The report would then fetch the result automatically.
Read the data from a plain text file, which contains database records in plain text (ASCII) format or XML format. Files of this format can be created by most database programs.
Pass your own dynamic data, either as an EJB or a class file, through API.
Pass the data set as an array in memory.
In the following sections, we will take a close look at each of these data sources and the methods used to obtain data from them.
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:odbc:woodview", // URL "sun.jdbc.odbc.JdbcOdbcDriver", // 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 ERES. 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 7.B.4.2 - Creating the Report.
ERES also allows data to be obtained from a JNDI source. JNDI data sources are treated like database data sources and support the same functionalities. Using a JNDI data source makes it easier to migrate reports between different environments. If the data sources in both environments are setup with the same lookup name, reports can be migrated without any changes.
You must have a data source deployed in your application server to connect to a JNDI data source. You must also provide the INITIAL_CONTEXT_FACTORY
and PROVIDER_URL
to successfully make the connection. Please note that when connecting to a JNDI data source deployed in Tomcat, the INITIAL_CONTEXT_FACTORY
and PROVIDER_URL
need not be provided (although ERES Server must be running under the Tomcat environment).
// 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 ERES Server 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);
In the above example, an instance of class DBInfo
provides the information that the program needs to connect to a JNDI data source and retrieve data. Use the following constructor containing IDatabaseInfo
to construct the report:
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 3.5.1.1 - 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 ERES 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 7.B.2.2 - Creating the Report.
In addition to the above, ERES 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 7.B.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 ERES. 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 ERES to improve performance considerably. If the data is already sorted, you can set a flag to true
in the constructor to avoid ERES 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 7.B.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; } }
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 ERES Server, the class file must be accessible from the CLASSPATH of the ERES Server.
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 7.1.5.8.5 - Open Report Designer with a Specific Class File Data Source.
public class ParamClassFile implements IParameterizedDataSource { String url = "jdbc:odbc:woodview"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String username = ""; 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; } }
The parameterized class file must implement IParameterizedDataSource. The above example obtains data from the woodview (ODBC) database.
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; }
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 7.B.7 - Parameterized Report.
For a working example using data passed in from a custom class file, see Appendix 7.B.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 ERES Server batch file when using the API in conjunction with ERES Server).
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);
ERES 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 ERES 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 7.A.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)
ERES 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:odbc:woodview", "sun.jdbc.odbc.JdbcOdbcDriver", "","","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
.