Entrance can plot query results in graphical form as bar, line, marker, scatter, or correlation charts. It also supports "color overrides", which make it possible to “paint” items in a chart with colors.
The quickest way to start a chart is to bring up the column popup menu on a table column (right click or control click on the column or its header) and then select "Make a line chart":
This will create a new script that will plot the columns you have selected automatically. With two or more column selected you can also "Make an XY chart".
You can jump back and forth between the chart and the script that made it by clicking the "Chart" and "SQL" tabs at the bottom of the window. Notice that the SQL script begins with a PLOT command. PLOT is an extended keyword provided by Entrance, like REPORT.
The PLOT Command
Use the PLOT keyword, placed before a SELECT statement, in a normal MySQL script when you want graphical query output. For example, here is the complete script for the bar chart at the top of this page:
PLOT
XLABELS, TRANSPARENT LIGHT BLUE BARS
WITH
TITLE Y "Population"
DOTTED HORIZONTAL GRIDLINES
NO SIDES
SELECT SUBSTRING(name, 1,3), population
FROM country
ORDER BY population DESC
LIMIT 15;
As you can see, commands for making charts consists of two parts: a PLOT part and a SELECT part. The SELECT part of a plot command is just a normal SQL select statement.
The PLOT part describes how the chart should be drawn. First, you list column "type" for all columns you want displayed in the chart. These column types should be listed in the order matching the order they appear in the SELECT part. They can be XLABELS, BARS, LINES or any of the marker types. You can also choose to SKIP a column.
The marker types available are: BOX, CIRCLE, TRIANGLE, OTHERTRIANGLE, HORTICK, VERTICK, X, and PLUS. Markers can be TINY, VERY SMALL, SMALL, BIG, or VERY BIG, and they can be FILLED or UNFILLED. You can also draw both markers and lines using AND LINES after a marker type.
There are a number of different ways to specify colors for any of the column types. You can use simple named colors like RED, GREEN, BLUE, YELLOW, and ORANGE, you can use Tango color names like BUTTER or PLUM, or you can specify colors by their RGB values. Color names can be modified with LIGHT, VERY LIGHT, DARK, or VERY DARK and they can be TRANSPARENT or VERY TRANSPARENT . (In the first example, the grid lines are showing through transparent bars)
Here are some possible column types, to give you the idea:
PLUS
RED X
TINY FILLED BLACK CIRCLES
VERY BIG DARK RED TRIANGLES
SMALL FILLED BLACK CIRCLES AND LINES
This example hints at the color possibilities: 
Titles and Notes
You can add an unlimited number of titles with the TITLE keyword, and titles lines can be LEFT, RIGHT or CENTERED. Here are some examples:
TITLE “This is a title”
TITLE RIGHT "So is this"
TITLE LEFT "And this"
You can place titles on the axes like this:
XTITLE “the x title in quotes”
YTITLE “the y title in quotes”
and you can add an unlimited number of notes below the chart with NOTE:
NOTE "Too many fall from great and good"
NOTE "for you to doubt the likelihood"
Data labels
Beginning with Entrance version 0.75.0, you can label data points with strings taken from a result set column. For example, this script:
plot scatter
x, small red box, datalabels
with
title x "area"
title y "population"
select area,population,name
from country2
where area > 6.0e06;
generated this chart:
Other Chart Settings
Chart axes are scaled automatically, or you can set their scales manually like this:
SCALE [X or Y] (minimum) (maximum) (increment)
You can set the number of labels drawn on the x axis in a LINECHART or BARCHART with these commands:
AUTO XLABELS
ALL XLABELS
ONLY n XLABELS
AUTO is the default and causes the chart code to guess how many labels to draw on the xa axis. ALL causes all non-null labels and their tick marks to be drawn. ONLY n limits the
number of labels to n. You will find that ONLY 5 XLABELS is often a good choice.
You can change the format of numerical labels on a value axis with FORMAT:
FORMAT [x or y] [PLAIN SCIENTIFIC AUTO or (a printf format)]
in versions after 0.72.0.
One font is used for all text in a chart, and you specify it like this:
FONT (family name) (size) (style)
You can change the foreground and background colors used for to draw chart elements other then the column types, like this:
FOREGROUND (color)
BACKGROUND (color)
Use LEGEND to draw a legend.
Use GRIDLINES, HORIZONTAL GRIDLINES or VERTICAL GRIDLINES to draw gridlines.
Chart frames are calculated automatically, or you can specify a fixed chart frame with:
FRAME (x0) (y0) (x1) (y1)
You can hide a chart’s sides with: NO SIDES.
You can leave a "collar" of space around the chart frame with COLLAR.
On a line chart, the keyword WHENLINES can be used to draw additional vertical lines on a standard chart. Vertical lines to be drawn wherever the WHENLINES column is non-null, eg. “”, and non-empty strings become the labels of those lines.
Pie Charts
Beginning with version 0.77.0, Entrance draws pie charts. Here is a complete script for making a pie:
create table pietest (
name varchar(255),
val int,
color varchar(255)
);
insert into pietest values
('one', 30, 'very light red'),
('five', 10, 'light red'),
('two', 5, 'red'),
('six', 40, 'very light blue'),
('three', 10, 'light blue'),
('seven', 20, 'blue'),
('four', 80, 'very light yellow'),
('eight', 10, 'light yellow'),
('nine', 3, 'yellow'),
('ten', 100, 'very light green'),
('eleven', 10, 'light green'),
('twelve', 1, 'green')
;
plot pie
label, value, color override
with
title "Hello Pie World"
select * from pietest;
Scatter Plots and Bubble Charts
Beginning with version 0.30.0, Entrance draws Scatter, Bubble and Correlation charts, and they can be used for graphical data exploration. Define a scatter plot like this:
PLOT SCATTER
X, BLACK CIRCLE
SELECT i,j FROM TheTable;
Make one of the data columns the “X” column and select other columns to provide the y values for markers or lines. You can assign colors and marker shapes and sizes, just as you would for a standard chart. Here is another example:
PLOT SCATTER
X, DARK BLUE CIRCLE, BUTTER BOX, LIGHT RED TINY TRIANGLE
SELECT index, ad1,ad2,ad3 FROM Sales;
Color and Size Overrides
Color and size overrides allow you to set chart attributes row by row, and a color override is required to do data painting. Include COLOR OVERRIDE in the PLOT section and add a column for it in the SELECT section of a chart command to specify one.
For example, this script causes color overrides to be taken from column myColors:
PLOT SCATTER
X, FILLED CIRCLE, COLOR OVERRIDE
SELECT index, lat, long, myColors FROM TempColors;
Column myColors is expected to contain color names like “RED”, “GREEN”, “VERY DARK BLUE” and so on. Case of the color names doesn’t matter.
Color overrides make it possible to paint items in a chart with color from a SQL query. This example will give you the idea:
UPDATE TempColors
SET myColors = 'light blue'; // make everything blue
UPDATE TempColors // now some highlights in red
SET myColors = 'red'
WHERE (something is true);
PLOT SCATTER
X, FILLED CIRCLE, COLOR OVERRIDE
SELECT lat, long, myColors FROM TempColors;
Now the user can add more highlights by dragging out a box on the scatter chart and selecting "Paint items red" from the popup menu.
Graphical Data Exploration
You can do graphical data exploration on Entrance Scatter Plots. To try this out, draw a scatter plot including a color override then drag out a rectangle over some of the markers and release the mouse. You will see a popup menu giving you the option to zoom in on the rectangle. You can also select rows corresponding to the markers in the rectangle you specified.
This turns out to be a very useful capability. Watch for a tutorial on graphical data exploration here, coming soon.
Correlation Charts
Entrance also supports correlation charts (as of version 0.33.0). Correlation charts and color overrides work together beautifully to reveal patterns in data sets:
Please note: This web page and other Entrance documentation web pages are copyrighted material and have not been released under the terms of the GPL.
Copyright (c) Tod Landis 2006,2007 All Rights Reserved Modified: May 29, 2007
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.
How to make a chart
