This example shows how to set JDBC data on a scatter graph. This example uses
the setTabularData
method of the graph.
The source data for this example is a table named BIBDEMO_ANALYTIC_FACTS, which has the following columns:
GEOG_LEAVES (VARCHAR2)
PROD_LEAVES (VARCHAR2)
CHAN_LEAVES (VARCHAR2)
TIME_LEAVES (VARCHAR2)
SALES (NUMBER)
UNITS (NUMBER)
COSTS (NUMBER)
PROMO (NUMBER)
QUOTA (NUMBER)
The following table shows the first few rows in the BIBDEMO_ANALYTIC_FACTS table:
GEOG_LEAVES | PROD_LEAVES | CHAN_LEAVES | TIME_LEAVES | SALES | UNITS | COSTS | PROMO | QUOTA |
---|---|---|---|---|---|---|---|---|
TORONTO | PORTCD | RETAIL | JAN00 | 2192 | 14 | 2555 | 19.98 | 2862.63 |
MONTREAL | PORTCD | RETAIL | JAN00 | 648 | 3 | 770 | 10.26 | 715.66 |
OTTAWA | PORTCD | RETAIL | JAN00 | 648 | 3 | 770 | 9.58 | 715.66 |
Note: This table has data for all of the months in 2000 and for the first five months of 2001.
The following figure shows the graph that this example creates.
To get the desired graph, this example takes data from the BIBDEMO_ANALYTIC_FACTS
table and calls the setTabularData
method to set the data on the
graph. The setTabularData
method constructs a grid of data for
the graph. The following table shows a portion of that grid. Only the columns
for three Geography values appear in this table. The complete grid has columns
for all Geography values in the ResultSet
.
![]() |
TORONTO SALES | TORONTO COSTS | MONTREAL SALES | MONTREAL COSTS | OTTAWA SALES | OTTAWA COSTS |
---|---|---|---|---|---|---|
JAN01 | 2131 | 2590 | 575 | 665 | 525 | 455 |
FEB01 | 2207 | 2415 | 640 | 735 | 572 | 420 |
MAR01 | 2207 | 2590 | 639 | 735 | 572 | 455 |
APR01 | 2207 | 2520 | 639 | 700 | 572 | 420 |
MAY01 | 2206 | 2870 | 638 | 910 | 571 | 525 |
Notice that this grid has two columns for Toronto, two for Montreal, and two for Ottawa. The "TORONTO SALES" label identifies the unique column for all of the Sales values for Toronto. The "TORONTO COSTS" is a different label, ensuring the appropriate two-column group of data for Toronto for a scatter graph.
Note: This grid is appropriate if the getDataRowShownAsASeries
method of the graph returns true
. If you map columns as series,
then the grid should have two distinct rows for each Geography value.
In an OLAP data source, you could simply place the Geography and Measure dimensions
on the column edge, and the data source would create a grid that has two layers
of metadata on the column edge. The simple data source that setTabularData
creates supports only one layer in the column edge, so you must provide distinct
labels for each column. In a scatter graph, these labels appear in the tooltips
for markers, but they do not appear in any other labels in the graph.
Notice, also, that the Sales and Costs columns always occur in the same order. The first measure column for each Geography is plotted along the X-axis in the scatter graph. The second column in the group is plotted along the Y-axis.
From the BIBDEMO_ANALYTIC_FACTS table, this example retrieves the data for
tuners sold in the retail channel for the months in 2001. It takes the data
from the ResultSet
and places it in an ArrayList
of
object arrays, which it then passes to setTabularData
. For a scatter
graph, the code iterates over a Geography value twice, in order to make two
separate columns in the grid of data for the graph.
This code assumes that you have a JDBC connection and a graph.
// declared earlier ArrayList data = new ArrayList(); // this should be in a try block, after you get the connection // connection is a JDBC connection if (connection != null){ String query = "SELECT GEOG_LEAVES, TIME_LEAVES, SALES, COSTS " + "FROM BIBDEMO_ANALYTIC_FACTS " "WHERE PROD_LEAVES='TUNER'" + "AND CHAN_LEAVES='RETAIL'" + "AND TIME_LEAVES LIKE '%01'"; Statement statement = connection.createStatement(); if (statement != null){ ResultSet resultSet = statement.executeQuery(query); // identify the two fact columns in the ResultSet String [] measures = {"SALES", "COSTS"}; if (resultSet != null){ while (resultSet.next()){ // for each measure for(int i = 0; i < 2; i++){ // create a 3-element Object array Object[] rowItems = new Object[3]; // first element is the column label in the grid // concatenate Geography value with a measure rowItems[0] = resultSet.getString("GEOG_LEAVES") + " " + measures[i]; // second element is the row label in the grid // row label is the same for each measure rowItems[1] = resultSet.getString("TIME_LEAVES"); // third element is the data value // get the value from the appropriate // fact column in the ResultSet rowItems[2] = new Double(resultSet.getDouble(measures[i])); // add this array to the ArrayList data.add(rowItems); } // for each measure } // while going through ResultSet }// if resultSet != null else System.out.println("ResultSet is null"); statement.close(); } // if statement != null else System.out.println("Statement is null"); connection.close(); } // if connection != null // code omitted here; you can end the try block // and catch any SQLExceptions // pass the ArrayList to setTabularData graph.setTabularData(data);
Specifying
Graph Data Through the setTabularData Method
Data Requirements for
Different Kinds of Graphs
Handling Problems
in Graph Data
Scatter Graphs