The two appendices in this Chapter (Appendix 7.C - Getting the Chart Data and Appendix 7.D - 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. Like reports, 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 7.D - Creating the Chart discusses the dimensions, the chart type, the column mapping, and the actual creation of the chart. Appendix 7.D - Creating the Chart also contains working examples for you to try.
The data for a chart can be obtained from a report (if the chart is inserted into a report) or from an independent data source. The following sections deal with connecting to an independent data source in order to obtain data to generate a chart.
The data used to create a chart may be fetched 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;
Read the data from a plain text file, which contains database records in plain text (ASCII) format. Files of this format can be generated by most database programs;
Read the data from a spreadsheet model;
Pass the data set as an array in memory;
Pass your own data through API;
Fetch the data from an EJB data source;
Merge from multiple data sources;
In the remainder of this section, we discuss the above methods of data extraction in greater detail.
One of the powerful features of Chart 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 to 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 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 ERES. 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.
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 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 ERES Server 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 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. To construct the chart, use the following constructor containing IDatabaseInfo
:
public QbChart(Applet parent, int dimension, int chartType, IDatabaseInfo dbinfo, ColInfo colMap, String templateFile);
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 nott 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 ERES 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 2.4.4 - 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 server, relative paths should be in respect to the ERES root 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
).
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 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);
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 ERES. 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);
For maximum flexibility, you can retrieve and prepare the data set 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; } }
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 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 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); } }
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; } } }
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 | |
---|---|
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:
quantity | high | |
coffee | 1 | 30 |
Soft Drinks | 3 | 33 |
Fruit Juice | 7 | 34 |
Water | 8 | 40 |
beer | 8 | 40 |
ERES then transposes the data (this is done internally) so that the data is changed to the following:
coffee | quantity | 1 |
coffee | high | 30 |
Soft Drinks | quantity | 3 |
Soft Drinks | high | 33 |
Fruit Juice | quantity | 7 |
Fruit Juice | high | 24 |
Water | quantity | 8 |
Water | high | 40 |
beer | quantity | 8 |
beer | high | 40 |
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);
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 ERES Server batch file when using the API in conjunction with ERES Server).
Unlike reports, it is not necessary to create an EJBInfo
object to store the connection information. Provide the names directly 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);
The above code can be run both as an application and as an applet.
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);
ERES 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 | |
---|---|
After you have created a |
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:
Date | Nasdaq | Dow | SP500 |
---|---|---|---|
"12/04/2000" | 2304 | 10503 | 1240 |
"12/05/2000" | 2344 | 10486 | 1239 |
"12/06/2000" | 2344 | 10458 | 1224 |
------ | ------ | ------ | ------ |
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.
ERES 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.
ERES 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 data set 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 data set (and not the original data set).
The sections below describe the various ways the data can be transposed:
In this scenario, all the columns, except for the very first column (Column 0) is transposed. For example, given the data below:
Product | January | February | March |
---|---|---|---|
Chairs | $3872.35 | $3962.21 | $4218.57 |
Tables | $6534.98 | $6018.43 | $5928.71 |
the transposed data will appear as follows:
Product | ColumnLabel | Value |
---|---|---|
Chairs | January | $3872.35 |
Chairs | February | $3962.21 |
Chairs | March | $4218.57 |
Tables | January | $4218.57 |
Tables | February | $6018.43 |
Tables | March | $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)
Please note that the column index passed in IColumnMap
would refer to the new data set.
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:
Category | Product | January | January | March |
---|---|---|---|---|
Chairs | Side Chairs | $3872.35 | $3962.21 | $4218.57 |
Chairs | Arm Chairs | $2654.84 | $1924.83 | $2543.24 |
Tables | Round Tables | $6534.98 | $6018.43 | $5928.71 |
Tables | Rectangular Tables | $10227.32 | $9721.83 | $11748.93 |
After transposing the numeric columns, the transposed data will appear as follows:
Category | Product | ColumnLabel | Value |
---|---|---|---|
Chairs | Side Chairs | January | $3872.35 |
Chairs | Side Chairs | February | $3962.21 |
Chairs | Side Chairs | March | $4218.57 |
Chairs | Arm Chairs | January | $2654.84 |
Chairs | Arm Chairs | February | $1924.83 |
Chairs | Arm Chairs | March | $2543.24 |
Tables | Round Tables | January | $6534.98 |
Tables | Round Tables | February | $6018.43 |
Tables | Round Tables | March | $5928.71 |
Tables | Rectangular Tables | January | $10227.32 |
Tables | Rectangular Tables | February | $9721.83 |
Tables | Rectangular Tables | March | $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)
Please note that the column index passed in IColumnMap
would refer to the new data set.