10.A. Getting the Chart Data

The two appendices in this Chapter (Appendix 10.A - Getting the Chart Data and Appendix 10.B - Creating the Chart) contain information to help you build a chart from scratch (without using the designer). Keep in mind that this method is typically not recommended as it will make the chart more difficult to deploy and maintain. However, under certain circumstances, it may be necessary to construct charts in this manner.

The following is an example of a QbChart constructor. There are numerous chart constructors available. The typical QbChart constructor requires at least four parameters. To create a new chart, you must specify the chart dimensions, chart type, the input data source information, and a mapping of the data columns to the respective columns of the chart.

QbChart(java.applet.Applet applet, int dimension, int chartType, IDatabaseInfo dbinfo, IColumnMap cmap, java.lang.String template)

The template is not required (can be null) and the applet is only required if the chart is to be displayed in an applet, but the other four parameters must always contain meaningful information. This appendix takes an in depth look at the data source parameter and the methods used to connect to different data sources. Appendix 10.B - Creating the Chart discusses the dimensions, the chart type, the column mapping, and the actual creation of the chart. Appendix 10.B - Creating the Chart also contains working examples for you to try.

The data used to create a chart may be fetched from one of several different types of sources. These sources include:

In the remainder of this section, we discuss the above methods of data extraction in greater detail.

10.A.1. Data from a Database

One of the powerful features of Chart API is its ability to fetch data from a database directly through JDBC. 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.

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

DBInfo dbinfo = new DBInfo(

                  "jdbc:odbc:ODBCDatabase",           // URL
                  "sun.jdbc.odbc.JdbcOdbcDriver",     // JDBC driver
                  "myName",                           // username
                  "myPassword",                       // password
                  "select * from sales");             // SQL

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

public QbChart(Applet parent, int dimension, int chartType, IDatabaseInfo dbinfo, ColInfo colMap, String templateFile);

In some cases, you may not wish to pass the entire database information (such as userid, password, location, driver, and the query) to EspressChart. You may want to make the connection yourself and just provide the result set 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 a DBInfo object.

// Create a QueryResult object from a java.sql.ResultSet object resultSet
QueryResultSet queryResultSet = new QueryResultSet(resultSet);

// use queryResultSet in place of IResultSet data in the following constructor
QbChart(java.applet.Applet applet, int dimension, int chartType, IResultSet data, IColumnMap cmap, 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 QbChart constructor to create the chart object. Please note that in this scenario, the chart is not refreshable. To update the chart, you will need to make the connection to the database again and pass the result set object to the chart and refresh it yourself.

10.A.1.1. JNDI

EspressChart 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 charts between different environments. If the data sources in both environments are setup with the same lookup name, charts can be migrated without any changes.

To connect to a JNDI data source, you must have a data source deployed in your application server. 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 EspressManager must be running as a servlet 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 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); 

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. To construct the chart, use the following constructor containing IDatabaseInfo:

public QbChart(Applet parent, int dimension, int chartType, IDatabaseInfo dbinfo, ColInfo colMap, String templateFile);

10.A.1.2. JNDI example using Tomcat and EspressManager running from servlet

Click to view larger image

Woodview database connection

10.A.2. Data from a Data file (TXT/DAT/XML)

Data for generating a chart can also be imported from a data file, which can be a text file as well as an XML formatted file. A chart 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 charts. You can simply export the data from your database in text format and then your program, using the Chart API, can read it.

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 the Section 4.3 - Data from XML and XBRL Files.

Specifying the text file to use is very straight forward. Use the following constructor and replace the variable filename with the file path (relative or full path) of the data file. If you are connecting to the EspressManager, relative paths should be in respect to the EspressManager working directory. Otherwise, the path will be relative to the current working directory. Also replace the fileType parameter with one of the following options: QbChart.DATAFILE, QbChart.QUERYFILE, or QbChart.XMLFILE

public QbChart(Applet parent, int dimension, int chartType, int fileType, String filename, boolean doTransposeData, ColInfo colMap, String templateFile);

The same constructor can also be used for passing in XML data generated by a servlet. You can specify the fileType as QbChart.XMLFILE and the filename as the URL to your servlet (e.g. http://localhost:8080/servlet/XMLDataGenerator).

10.A.3. Data from a XML Data Source

In addition to the above, EspressChart 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 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);

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 QbChart.

public QbChart(Applet applet, int dimension, int chartType, XMLFileQueryInfo xmlInfo, boolean doTranspose, int[] transposeColumns, IColumnMap colMap, String templateFile);

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);

10.A.4. Data Passed in an Array in Memory

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.util package). This interface is used to read data in the 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 implementations of IResultSet (which is discussed in the next section), or use one provided by EspressChart. The simplest implementation is provided by 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 chart 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

We will use the following constructor in the example here.

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

This is a similar construction to reading in data from a data file. 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 chart, use the following QbChart constructor:

public QbChart(Applet parent, int dimension, int chartType, IResultSet data, ColInfo colMap, String templateFile);

10.A.5. Data Passed in your Custom Implementation

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

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 the IResultSet interface. 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 chart:

public QbChart(Applet parent, int dimension, int chartType, int fileType, String filename, ColInfo colMap, String templateFile);

For custom class files, set the fileType to QbChart.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 chart. The parameter is obtained at run-time from the user and the data is then fetched and used to generate the chart. Note that this will only work for a stand-alone chart configuration.

public class ParameterizedClassFile implements IParameterizedDataSource {
	public IQueryInParam[] getParameters()
	{
		SimpleQueryInParam[] params = new SimpleQueryInParam[1];
		params[0] = new SimpleQueryInParam("param2", "Enter the price:", false, null, null, Types.INTEGER, new Integer(2), null);
		return params;
	}
	
	public IResultSet getResultSet(IQueryInParam[] params) {
		double price = 3.5;
		if ((params != null) && (params.length >= 1))
		{
			Object obj = params[0].getValue();
			if ((obj != null) && (obj instanceof Integer)) price = ((Integer)obj).intValue();
		}
		String dataType[] = {"string", "String", "double"};
		String fieldName[] = {"Destination",  "Time", "Price"};
		String records[][] = {{"Mayfair",  "13:43", price+""},
				{"Bond Street", "13:37", price+""},
				{"Rickmansworth ", "13:12", price+""},
				{"Picadilly",  "13:24", price+""}};
		return new DbData(dataType, fieldName, records);
	}
}

Full Source Code

When using a parameterized class file as the data source, the parameter dialog box is usually a text box with no choices available. However, you can specify what the selection choices can be (available via a drop-down box) by implementing the IQueryParamValuesProvider interface.

public class CustomParamClassFile implements IParameterizedDataSource {

	public IQueryInParam[] getParameters() {
		mySimpleQueryMultiValueInParam[] params = new mySimpleQueryMultiValueInParam[1];
		params[0] = new mySimpleQueryMultiValueInParam("region", "Select Region(s):", true, "Customers", "Region", Types.VARCHAR, "East", null);
		return params; 
	}

	public IResultSet getResultSet(IQueryInParam[] params) {
		QueryResultSet data = null;
		ResultSet rs = null;

		String paramValue = "'East'";
		if ((params != null) && (params.length >= 1)) {
			Vector selectedValues = null;
			if (params[0] instanceof IQueryMultiValueInParam)
				selectedValues = ((IQueryMultiValueInParam)params[0]).getValues();

			for (int i = 0; i < selectedValues.size(); i++) {
				if ((selectedValues.get(i) != null) && (selectedValues.get(i) instanceof String)) {
					if (i == 0) paramValue = "'" + (String)selectedValues.get(i) + "'";
					else paramValue += ",'" + (String)selectedValues.get(i) + "'"; 
				} 
			} 
		}
		String myQuery = "select cu.region, c.categoryname, count(o.orderid), sum(od.quantity), sum(p.unitprice * od.quantity) from customers cu, categories c, products p, orders o, order_details od where cu.customerid = o.customerid and c.categoryid = p.categoryid and p.productid = od.productid and o.orderid = od.orderid and cu.region in (" + paramValue + ") group by cu.region, c.categoryname";

		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();

			rs = stmt.executeQuery(myQuery);
			data = new QueryResultSet(rs);
			// conn.close(); 
		} catch (Exception ex) {
			ex.printStackTrace(); 
		}
		return data; 
	} 

	public class mySimpleQueryMultiValueInParam extends SimpleQueryMultiValueInParam implements IQueryParamValuesProvider {

		public String paramName, promptName, tableName, colName;
		boolean mapToColumn;
		int sqlType;
		Object defaultValue;
		Vector values;
		public mySimpleQueryMultiValueInParam(String paramName, String promptName, boolean mapToColumn, String tableName, String colName, int sqlType, Object defaultValue, Vector values) {
			super(paramName, promptName, mapToColumn, tableName, colName, sqlType, defaultValue, values); 
		}

		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

10.A.6. Data from a Spreadsheet Model

A chart can function as a view to a spreadsheet model in a Model-View-Controller (MVC) architecture. It automatically reads the spreadsheet data and plots itself. The chart registers itself as a listener to the spreadsheet model and updates itself when notified of any changes to the spreadsheet data. A spreadsheet (Java) object is provided by the user, which is an instance of a class that implements the ISpreadSheetModel interface. The event class SpreadSheetModelEvent is used by the model to notify its listeners of any changes to data. The following example shows how a spreadsheet model can be used for a chart. It uses the utility class SimpleSpreadSheet (defined in the quadbase.util package), which implements the ISpreadSheetModel interface.

[Note]Note

This method is applicable only to live Java program spreadsheet objects that contain data to be plotted, and hence complements the methods for reading data from a database or data file in spreadsheet format.

String[] columnVals = {"quantity", "high"};
String[] rowVals = {"coffee", "Soft Drinks", "Fruit Juice", "Water", "beer"};
Double[][] vals = { {new Double(1), new Double(30)},

                        {new Double(3), new Double(33)},
                        {new Double(7), new Double(34)},
                        {new Double(8), new Double(40)},
                        {new Double(8), new Double(40)} }; 

// Please see quadbase.util.SimpleSpreadSheet
sss = new SimpleSpreadSheet(rowVals, columnVals, vals); 

Here the data is given in a spreadsheet format and looks like the table below:

 quantityhigh
coffee130
Soft Drinks333
Fruit Juice734
Water840
beer840

Data in Spreadsheet Format

EspressChart then transposes the data (this is done internally) so that the data is changed to the following:

coffeequantity1
coffeehigh30
Soft Drinksquantity3
Soft Drinkshigh33
Fruit Juicequantity7
Fruit Juicehigh24
Waterquantity8
Waterhigh40
beerquantity8
beerhigh40

Data After Transpose

The data mapping for the chart is then done with respect to the transposed data.

Use the following constructor to create the QbChart object:

public QbChart(Applet applet, int dimension, int chartType, ISpreadSheetModel spreadsheet, IColumnMap colMap, String templateFile);

10.A.7. Data from Enterprise Java Beans (EJBs)

Data can be passed from an EJB data source to the chart 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).

Provide the connection information into the following constructor to create a QbChart object.

public QbChart(Applet applet,
               int dimension,
               int chartType,
               String jndiName,
               String homeName,
               String remoteName,
               String selectedMethodName,
               Object[] selectedMethodParamVal,
               IColumnMap cmap,
               String template);

Full Source Code

The above code can be run both as an application and as a JNLP (More about Applets in JNLP: Section 2.6 - Run Applets in WebStart with JNLP file).

The constructor for this class is:

public QbChart(Applet applet, int dimension, int chartType, String jndiName, String homeName, String remoteName, String selectedMethodName, Object[] selectedMethodParamVal, IColumnMap cmap, String template);

10.A.8. Data from a SOAP Data Source

EspressChart allows to retrieve data from SOAP services. SOAP data sources are similar to XML data sources. The main difference is that the XML files are transfered in a SOAP message. To use the SOAP data source, you need a SOAP service, which will be sending the XML files. This service can be written in any programming language.

The request SOAP message has no parameters and expects a SOAP response in this form:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <soapenv:Body>
            <XMLTYPE>QUADBASE</XMLTYPE> </soapenv:Body> 

</soapenv:Envelope> 

The message has only one body element – XMLTYPE. Its value specifies the type of the XML file. Possible values are:

QUADBASE

XML is in Quadbase format

DTD

XML with DTD schema

XSD

XML with XML Schema

The XML file and schema (if used) are sent as MIME attachment of the SOAP message. If the QUADBASE XMLTYPE is used, the SOAP message has to have one attachment – the XML file in Quadbase format. If DTD or XSD XMLTYPE is used, the message has to have two attachments – the first is the XML file and the second is the DTD or XSD file. The MIME type of these attachments is text/xml.

This is the constructor for creating a chart from a SOAP data source. The XML has to be in the Quadbase format:

QbChart(java.applet.Applet applet, int dimension, int chartType, java.lang.String SOAPURL, java.lang.String serviceName, java.lang.String methodName, IColumnMap cmap, java.lang.String template, boolean doTransposeData, boolean[] transposeCol) 

10.A.8.1. Example SOAP Service

EspressChart provides an example SOAP service. Its source code can be found in <EC-installation-directory>/help/examples/soap/SOAPService.java. To use the example, please edit the source code and replace all the occurrences of <ECInstall> with your EspressChart installation directory. Then compile the file (you need to include all the jar files from the <EC-installation-directory>/lib directory in your classpath to do that).

Next, you have to deploy this SOAP service. The instructions below shows how to deploy it on the Apache Tomcat. The <EC> refers to the EspressChart installation directory.

  1. Download Tomcat 5.0 or higher from the Apache web sites and install it. The Tomcat installation directory is further referenced as <TOMCAT>.

  2. Copy the following files from <EC>/lib to <TOMCAT>/common/lib: activation.jar, axis.jar, commons-discovery-0.2.jar, commons-logging-1.0.4.jar, jaxrpc.jar, log4j-1.2.8.jar, mail.jar, saaj.jar, wsdl4j-1.5.1.jar.

  3. Copy the following files from <EC>/help/examples/soap/ to <TOMCAT>/webapps/axis/WEB-INF/classes/quadbase/soap directory (you have to create this directory structure): deployDS.wsdd, SOAPService.class, undeployDS.wsdd.

  4. Copy the <EC>/help/examples/soap/web.xml file to the <TOMCAT>/webapps/axis/WEB-INF/ directory.

  5. Start Tomcat (using <TOMCAT>/bin/startup.bat)

  6. Run the following command in the <TOMCAT>/webapps/axis/WEB-INF/classes/quadbase/soap/, having all the JAR files from the step 2 in your classpath. It will deploy the SOAPService.

    java org.apache.axis.client.AdminClient deployDS.wsdd

    If your Tomcat is not running at default port (8080), you can use –p argument to specify different port.

Three web services are deployed in this example: SOAPService-fetchData returns XML in Quadbase format, SOAPService-fetchData2 returns XML and a DTD file and SOAPService-fetchData3 returns XML and a schema file (XSD). The necessary connection informations for the example are below.

Server URL is http://machine:port/axis/services/SOAPService for all three services. Please replace machine and port with machine name and port Tomcat is running on (the default port is 8080). If Tomcat is not running on the local machine, please remember to modify the URL links within the XML files.

Service lookup name and method lookup name for all the examples are listed below.

  • service lookup name: SOAPService

    method lookup name: fetchData

    check XML is in Quadbase format

    This service sends XML in Quadbase Format. You can use the data source in the same way as XML File data source.

  • service lookup name: SOAPService

    method lookup name: fetchData2

    uncheck XML is in Quadbase format

    This one uses a DTD file. You can add XML query using this data source and use the query in the same way as XML File Query.

  • service lookup name: SOAPService

    method lookup name: fetchData3

    uncheck XML is in Quadbase format

    This one uses a Schema file. You can add XML query using this data source and use the query in the same way as XML File Query.

10.A.9. Data from multiple Data Sources

EspressChart also provides a 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 (see Data Passed as an Argument section). You can use a QbChart constructor to create a chart object from an array of DataSheet.

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 a Database section)
DBInfo dbinfo = new DBInfo("jdbc:quadbase:/machine/schema",
"quadbase.jdbc.QbDriver", "myName","myPassword","select * from sales"); //Declaration For Data Passed as an Argument (Data Passed as an Argument 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, "help/examples/data/Columnar1.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 as an Argument section)
// DataSheet(Applet applet, IResultSet data)
dataSheet[2] = new DataSheet(this, data); // create QbChart
QbChart chart = new QbChart

                        (this,                 // applet
                        QbChart.VIEW2D,        // Two-Dimensional
                        QbChart.PIE            // Pie Chart
                        dataSheet,             // DataSheet
                        colInfo,               // column information
                        null);                 // No template
[Note]Note

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

10.A.10. Data in Spreadsheet Format

Data obtained using the above methods may also be interpreted as a spreadsheet. A spreadsheet has a grid structure, much like a table. The leftmost column and first row in a spreadsheet contain labels (or headings). Each cell represents a distinct data point comprising its row label, column label, and the cell value (in contrast to the normal tabular notation, where each row represents a distinct data point).

Consider the following example:

DateNasdaqDowSP500
"12/04/2000"2304105031240
"12/05/2000"2344104861239
"12/06/2000"2344104581224
------------------------

Suppose the data in your database is arranged as four columns as shown. You can plot the three indices (Nasdaq, Dow, and SP500) as three lines with Date as the category axis if you treat the data as in spreadsheet format.

Several QbChart constructors are provided to deal with this issue. The three main ones are listed below.

QbChart(java.applet.Applet applet, int dimension, int chartType, IResultSet data, boolean

      doTransposeData, IColumnMap cmap, java.lang.String template);

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType,

      java.lang.String filename, boolean doTransposeData, IColumnMap cmap, java.lang.String
      template);

QbChart(java.applet.Applet applet, int dimension, int chartType, IDatabaseInfo dbinfo, boolean

      doTransposeData, IColumnMap cmap, java.lang.String template);

To specify that data is in spreadsheet format, you need to set the doTransposeData flag to true.

The above constructors transpose all the columns from the second column to the last column into a 3-column table. Thus, the data type from the second column onwards must be numeric otherwise the transpose will not be successful.

EspressChart also allows transposing of selective columns. The columns to be transposed must share the same data type. After transposing, the original columns are removed and the new columns inserted at the end of the table data. Selective transposing can be done only once; you cannot transpose certain numeric columns and then try to transpose other numeric or string columns again.

As with the complete transposing, QbChart has several constructors to allow selective transposing. The three main ones are listed below:

QbChart(java.applet.Applet applet, int dimension, int chartType, IResultSet data, boolean

      doTransposeData, int[] transposeCol, IColumnMap cmap, java.lang.String template);

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType,

      java.lang.String filename, boolean doTransposeData, int[] transposeCol, IColumnMap cmap,
      java.lang.String template);

QbChart(java.applet.Applet applet, int dimension, int chartType, IDatabaseInfo dbinfo, boolean

      doTransposeData, int[] transposeCol, IColumnMap cmap, java.lang.String template);

To specify the columns to be transposed, you need to pass in an array containing the column indices for transposeCol and set the doTransposeData flag to true.

10.A.11. Transposing Data

EspressChart allows data to be obtained from various data sources. You can also transpose the data (i.e., transform the data so that the column names become part of the data) before passing the data to the desired chart type.

When the data is transposed, the original data columns (used in the transpose) are removed from the dataset and two new columns are added at the end. These two new columns would contain the transposed column names as well as the values of the original columns. For example, if the original data set has five columns and three are selected for transpose, the new data set would have five minus three (the number of transposed columns) plus two (the new columns added) or four columns.

When transposing data columns they are two things to note:

  • The data columns must all have the same datatype;

  • The column index passed to the chart's column mapping will refer to the new dataset (and not the original data set).

The sections below describe the various ways the data can be transposed:

10.A.11.1. Non-Selective Transposing

In this scenario, all the columns, except for the very first column (Column 0) is transposed. For example, given the data below:

ProductJanuaryFebruaryMarch
Chairs$3872.35$3962.21$4218.57
Tables$6534.98$6018.43$5928.71

the transposed data will appear as follows:

ProductColumnLabelValue
ChairsJanuary$3872.35
ChairsFebruary$3962.21
ChairsMarch$4218.57
TablesJanuary$4218.57
TablesFebruary$6018.43
TablesMarch$5928.71

To non-selectively transpose the data using the API, you use any QbChart constructor that has a doTransposeData boolean parameter, such as the following constructor:

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType, java.lang.String filename, boolean doTransposeData, IColumnMap cmap, java.lang.String template)

Full Source Code

Exported Results

Please note that the column index passed in IColumnMap would refer to the new dataset.

10.A.11.2. Selective Transposing

In this scenario, you choose which columns are to be transposed. You can only transpose those columns that share the same data type. With selective transposing, you can choose the very first column to be transposed as well. For example, given the data below:

CategoryProductJanuaryJanuaryMarch
ChairsSide Chairs$3872.35$3962.21$4218.57
ChairsArm Chairs$2654.84$1924.83$2543.24
TablesRound Tables$6534.98$6018.43$5928.71
TablesRectangular Tables$10227.32$9721.83$11748.93

After transposing the numeric columns, the transposed data will appear as follows:

CategoryProductColumnLabelValue
ChairsSide ChairsJanuary$3872.35
ChairsSide ChairsFebruary$3962.21
ChairsSide ChairsMarch$4218.57
ChairsArm ChairsJanuary$2654.84
ChairsArm ChairsFebruary$1924.83
ChairsArm ChairsMarch$2543.24
TablesRound TablesJanuary$6534.98
TablesRound TablesFebruary$6018.43
TablesRound TablesMarch$5928.71
TablesRectangular TablesJanuary$10227.32
TablesRectangular TablesFebruary$9721.83
TablesRectangular TablesMarch$11748.93

To selectively transpose the data using the API, you use any QbChart constructor that has a doTransposeData boolean parameter and integer array that takes the indices of the columns to be transposed, such as the following constructor:

QbChart(java.applet.Applet applet, int dimension, int chartType, int fileType, java.lang.String filename, boolean doTransposeData, int[] transposeCol, IColumnMap cmap, java.lang.String template)

Full Source Code

Exported Results

Please note that the column index passed in IColumnMap would refer to the new dataset.