Example: Setting JDBC Data on a Scatter Graph

This example shows how to set JDBC data on a scatter graph. This example uses the setTabularData method of the graph.

Source data

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.

Graph

The following figure shows the graph that this example creates.

sample graph from previous data

Grid of data for the graph

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.

empty cell 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.

Sample code

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