PLOT Syntax

Overview

PLOT is a SQL extension provided by Entrance for making charts. Scripts containing PLOT can be run from the Entrance desktop aplication, on the command line, from EntranceServlet, or embedded in other applications.

A Simple Example

A PLOT command consists of two parts: a chart setup part and a SELECT part. Here is a simple example and its output: 
PLOT
  AXISLABELS, ORANGE LINE
SELECT
  year, avg FROM cpi_annual;
Any SQL SELECT statement will work.   The important thing is that the LINES, BARS and other chart series in the chart setup part match up one-to-one with columns in the SELECT part.

PLOT Syntax

  PLOT [optional_chart_type]

    chart_series_type [, ...]

      [WITH]
        [SCALE (axis) {(min) (max) (incr) | LOG | LOG10 }]
        [ZEROBASED]                              

        [[NO] CLIP]

        [[HORIZONTAL | VERTICAL] GRIDLINES]    

        [TITLE [LEFT | RIGHT | CENTER] "string"] ...
        [TITLE (axis) "string"]

        [NOTE     [LEFT | RIGHT | CENTER] "string"] ...
        [COMMENT [LEFT | RIGHT | CENTER] "string"] ...

        [FORMAT (axis) {AUTO | PLAIN | SCIENTIFIC | 
	   DECIMAL "(decimal format)" | "date_time_format"]
        [LABELANGLE {0 | 90}]
        [[color] [line_type] [HORIZONTAL | VERTICAL] GRIDLINES]

        [FONT font_family font_style font_size]
        [TITLEFONT font_family font_style font_size]

        [FOREGROUND color]
        [BACKGROUND color]
        [FRAMECOLOR color]

        [[NO] SIDES]
        [[NO] COLLAR]

        [[NO] YAXIS]
        [[NO] XAXIS]
        [[NO] X2AXIS]
        [[NO] Y2AXIS]

        [[NO] LABELS (axis) [ON {TOP | BOTTOM | LEFT | RIGHT}]
        [[NO] TICKS (axis) [ON {TOP | BOTTOM | LEFT | RIGHT}]
        [[NO] AXISLINE (axis) [ON {TOP | BOTTOM | LEFT | RIGHT}]

        [[NO] LEGEND [RIGHT | BOTTOM | ON (page x) (page y)]
        [color] LEGENDITEM 

        [NO BITMAP | BITMAP "(PNG file name)"]
        [PAGEBITMAP "(PNG file name)"]

        [FRAME {TO FIT | left_x top_y right_x bottom_y}]
        [PAGE 0 0  right_x bottom_y]

        [BARWIDTH (double 0 - 1.0)]     
        [BARSEPARATION (integer)]        
        [BAROVERLAP (0 or 1)]            
        [SKINNY]

        [MARKER marker_type]

        [PALETTE [TRANSPARENT] {"Tango" | "Bright" | "Light" |
              "Very Light" | "Blue Shades" | "Red Shades" |
              "Gray Shades"}]

         [THROUGHNULLS]

         [OTHERSLICE "(label)" (amount) (color)]

         [PAGENAME "(name)"]   

         [TRANSLUCENCE (integer 0-255)]
         [LAYERS "(path to directory)"]
         [VERSION "(version string)"]

        
  SELECT 
     (SQL select statement body);

Chart Types

By default, PLOT draws a LineChart which can mix lines, bars, areas and markers.   You can also specify other chart types.   For example, use SCATTER to draw scatter plots like this one: 
PLOT SCATTER
    X, DARK BUTTER CIRCLE
  WITH 
    SCALE Y -1 1 1
    TITLE "Title"
    TITLE X "X Title"
    TITLE Y "Y Title"
SELECT
  4 * x * COS(y * 2 * PI()),
     x * SIN(y * 2 * PI()) 
  FROM ref2;
These are the chart types Entrance supports:

LINECHART
This is the work horse chart for drawing lines, markers, bars, and areas. It can also draw swaths and whenlines. There is an example here: "Lines and Markers".

BARCHART, HISTOGRAM, AREACHART
These are extensions of LineChart providing different defaults. There are examples of bar charts here: "Charting Counts with SQL and Entrance".

ROTATEDBARCHART
Rotated bars. There are examples in this blog post "Rotated Bars".

STACKEDBARCHART
Stacked bars. There are examples here: "Stacked Bars".

SCATTER (or XYCHART)
Plots (x,y) data points using markers or lines. There is an example on "Add a Second X or Y Axis".

TIMEYCHART, DATEYCHART, DATETIMEYCHART, DATETIMEDIRCHART
These plot (time,y), (date,y) or (datetime, y) data points as a ScatterChart. There are examples on "DATETIME-Y Charts" In version 1.5 and later versions, DATETIMEDIRCHART also plots vectors, see eg. Plotting Wind Direction and Speed

CORRELATION
Displays an array of scatter charts plotting pairs of columns against each other. There are examples on "Correlation Charts".

HIGHLOWCHART, CANDLESTICK, BURNDOWN
Display high, low, open close values. These charts can also contain lines and markers. There are examples on "High Low Charts".

WHISKERSCHART
Displays tick "whiskers" on a vertical axis.

BITMAPCHART
Draws a ScatterChart on a background image. There is an example in "Bitmap Charts".

EARTHCHART
Plots latitude, longitude points on images of the earth. There are examples in "EarthCharts", "Earthquakes," and "EarthChart and KML Export".

COLORINGBOOK
Paints colors on shape mask layers. Use a LAYERS series to match up strings in a database column with bitmap layers in the coloring book. Use COLOR OVERRIDE to assign the layers colors. TRANSLUCENCE determines how much layers show through each other. See "More About ColoringBooks" for an example.

USCOLORINGBOOK
Paints colors on the US states, for example to make the classic "Red-Blue States" map. See Coloring the US States for an example.

PIECHART
Draws a pie chart. There are examples on "Pies" and "Entrance Colors".

RADARCHART
Examples: Radar Charts

Using Java® it is possible to extend the system with other chart types. Contact us for more details.

Series Types

The series types in a PLOT statement should match up 1-to-1 with columns in the result set generated by the SELECT statement it includes. Following are the possible series types.

[ALL | ONLY (n)] AXISLABELS
Draws axislabels on the X axis on the basic time series charts and on the Y axis on a RotatedBarChart. Without a modifier AXISLABELS will make a reasonable choice for the number of labels to skip between drawn labels. 

    ALL        all labels will be drawn, ie. no labels are skipped
    ONLY (n)   only (n) labels will be drawn
See "Axis Labels" for examples.

[color] [line_type] [line_width] LINE
Draws lines. Fully saturated colors like RED, BLUE, GREEN and the Tango Desktop colors work well for lines.

[color] BAR
Draws bars for column values. Light colors like LIGHT RED or VERY LIGHT RED work well for bars and other filled series types.

[color] AREA
Draws areas.

[color] [marker_size] [marker_fill] marker_type [AND LINES]
Draws markers or markers and lines. CIRCLE, BOX, and TRIANGLE are commonly used markers.

[color] STEPPEDAREA
Draws stepped areas. See "Stepped Lines and Areas" for examples.

[color] [line_type] [line_width] STEPPEDLINE
Draws stepped lines. See "Stepped Lines and Areas" for examples.

[color] SWATH
Paints color between lines. The first SWATH determines one side of the swath. The next SWATH determines the second side. If a third SWATH series is specified, color is painted between it and the second SWATH, and so on. The first swath color is ignored. Thereafter, color attributes are applied.   See "Color a SWATH" for examples.

LENGTH
The length of vectors in a DateTimeDirChart. See eg. "Plotting Wind Direction and Spped"

[color]
The direction of vectors in a DateTimeDirChart, as degrees 0-360. See eg. "Plotting Wind Direction and Spped"

[color] HIGH
The high series in a HighLowChart.

[color] LOW
The low series in a HighLowChart.

[color] OPEN
The open series in a HighLowChart.

[color] CLOSE
The closer series in a HighLowChart.

SKIP
Skips a column, ie. draws nothing.

PIELABEL
Draws labels on pie slices.

PIEVALUE
Draws pie slices.

DATALABELS [ABOVE | BELOW | LEFT | RIGHT | CENTERED]
Draws data labels on points in a chart. Label alignment can be set.

[color] [line_type] WHENLINES
Draws labelled vertical lines on a LineChart or one of its derived charts. Lines are drawn for non-null values, and labelled when the column contains a non-empty string.

LAYERNAME
Sets the LAYERNAME column for a ColoringBook. Use this for states names in a USColorBook.

LAYER
Sets the column containing layer names in a ColoringBook or USColoringBook.

COLOR OVERRIDE
Color overrides change the color displayed for data points with a non-null value in the column assigned. Use color names like "RED", "BLUE" and so on in the column. &nbps; There are examples here "Legends".

SIZE OVERRIDE
Size overrides change the size of markers displayed for data points with a non-null value in the column assigned. The blog post "Bubble Charts" demonstrates the use of size overrides.

EXPLODE
In a PieChart, EXPLODE values 0-100 are the percentage amount to offset pie slices out from the center of the pie. Null values mean "no offset".

Other Keywords

The chart setup can include features other than the basic series types. These should follow WITH.

SCALE [(axis)] {(min) (max) (incr) | LOG | LOG10 }
Sets the scale of "axis", which can be X, X2, Y or Y2. When the axis is not specified it is assumed to be Y in a vertical chart, or X in a rotated chart. The scale can be set using the minimum, maximum, and increment or a logarithmic scale. LOG uses the natural log, and LOG10 is the log base 10.

ZEROBASED
Forces zero to be included in the axis scales for a chart.

[NO] CLIP
Turns clipping on and off. When clipping is on nothing will be drawn outside the chart frame.

TITLE [LEFT | RIGHT | CENTER] "string"
Uses "string" for a title above the chart. An unlimited number of titles may be drawn. When no alignment, LEFT, RIGHT or CENTER is specified, the title will be drawn centered.

TITLE (axis) "string"
Uses "string" for a title on (axis), which can be X, X2, Y, or Y2.

NOTE [LEFT | RIGHT | CENTER] "string"
Uses "string" for a note below the chart. An unlimited number of notes may be drawn. When no alignment, LEFT, RIGHT or CENTER is specified, the note will be centered.

COMMENT [LEFT | RIGHT | CENTER] "string"
Uses "string" for a comment in the chart. An unlimited number of comments may be drawn. When no alignment, LEFT, RIGHT or CENTER is specified, the comment will be centered.

FORMAT (axis) {AUTO | PLAIN | SCIENTIFIC | DECIMAL "(decimal format)" ] | "(datetime format)"
Specifies the format for labels on (axis), which can be X, X2, Y, or Y2. 

  AUTO determines the format automatically.
  PLAIN uses integer labels
  SCIENTIFIC uses scientific notation
  DECIMAL uses the Java DecimalFormat protocol for numerical scales  

    In a (decimal format) string:
       0 is a digit
       # is a digit, trailing zeroes are supppressed
       . is the decimal or money separator for the locale
       , is the grouping separator

       Decimal formats can contain can contain Unicode characters,
       eg. \u00B0 for the degree symbol

       The percentage symbol, %, triggers a multiply by 100.  To avoid
       this surround it with quotes like this:  FORMAT Y DECIMAL "##.0'%'" 


  Use these patterns for the format string on a DateYChart,
       TimeYChart, DateTimeYChart, or DateTimeDirChart:
          %Y   - four digit year
          %y   - two digit year
          %m   - month  (00-12)
          %b   - abbreviated month name (Jan-Dec)
          %d   - day of the month (00-31)
          %H - hours 00-23
          %i - minutes 00-59
          %S - seconds 00-59
          %s - seconds 00-59

LABELANGLE {0 | 90}
Determines the angle of X axis labels on a DateYChart, TimeYChart, or DateTimeYChart.

[color] [line_type] [HORIZONTAL | VERTICAL] GRIDLINES
Turns grid lines on. When neither HORIZONTAL nor VERTICAL are specified both types will be turned on. The grid line color and line type can also be specified.

FONT font_family font_style font_size
Set the font used for text in a chart.  The font families availabel depends on the OS, but "sans serif", "serif", and "monospaced" are always available. The font_style can be PLAIN, BOLD, or ITALIC. Font sizes are specified in points.

TITLEFONT font_family font_style font_size
Set the font used for titles. When TITLEFONT is absent, the FONT will be used.

FOREGROUND color
Set the color used for the foreground elements in a chart.

BACKGROUND color
Set the background color.

FRAMECOLOR color
Set the frame color. The frame color will be the background color when FRAMECOLOR is absent.

[NO] SIDES
Turns drawing the chart sides on and off.

[NO] COLLAR
Turns leaving a "collar" of space on the sides of the chart on and off.

[NO] YAXIS
Turns the Y axis on and off. It is on by default.

[NO] XAXIS
Turns the X axis on and off. It is on by default.

[NO] X2AXIS
Turns the X2 axis on and off. It is on by default.

[NO] Y2AXIS
Turns the Y2 axis on and off. It is on by default.

[NO] LABELS (axis) [ON {TOP | BOTTOM | LEFT | RIGHT}]
Turns (axis) labels on and off. It can also set whether labels ore above or below a horizontal axis, or left or right of a vertical axis.

[NO] TICKS (axis) [ON {TOP | BOTTOM | LEFT | RIGHT}]
Turns (axis) tick marks on and off. It can also set whether the ticks ore above or below a horizontal axis, or left or right of a vertical axis.

[NO] AXISLINE (axis)
Turns the (axis) line on and off.

[NO] LEGEND [RIGHT | BOTTOM | AT (page x) (page y)\
Turns the legend on and off. This can also be used to place the legned to the right of the chart, below it, or with its upper left corner at (x,y).   There are examples here: "Legends".

[color] LEGENDITEM
This keyword provides a way to add legend items to the legend, which is useful when you are using COLOR OVERRIDES. There are examples here: "Legends".

NO BITMAP | BITMAP "(PNG file name)"
In a BitmapChart, "NO BITMAP" will cause the background bitmap not to be drawn. This can be useful when you want to flip back and forth between a bitmap background and a black background with the same scaling.

PAGEBITMAP "(PNG file name)"
Specifies a PNG file to be used for the background image of the page.

FRAME {TO FIT | left_x top_y right_x bottom_y}
FRAME TO FIT, which is the default, causes Entrance to fit the frame automatically. FRAME can also be used to set a frame to pixels within the current page. Use FRAME and PAGE together to make sparklines.

PAGE 0 0 right_x bottom_y
Sets the width and height of the page. (It is not possible to change the offset to the left corner--use frame instead) For example, use PAGE when you want to make small graphics for a cell phone.

BARWIDTH (integer 0 - 100)
Sets the width of bars as a percentage of the space available. See "Bar Charts" for examples.

BARSEPARATION (integer)
Sets the separation between bars in device units. &nbps; See "Bar Charts" for examples.

BAROVERLAP (0 or 1)
Sets whether bars overlap or not: 0 is "not overlapped", 1 is "overlapped". .   See "Bar Charts" for examples.

SKINNY
Draws the second set of bars at 1/3 the current bar width. Use SKINNY with BAROVERLAP 1. "Comparing Today to Yesterday" demonstrates its use.

MARKER (marker)
Sets the default marker.

PALETTE [TRANSPARENT] {"Tango" | "Bright" | "Light" | "Very Light" | "Blue Shades" | "Red Shades" | "Gray Shades"}
Deprecated. Sets the current palette.

THROUGHNULLS
By default, line segments end at null values. THROUGHNULLS cause them to be drawn between the valid values before and after null values.

OTHERSLICE "(label)" (amount) (color)
Adds an additional pie slice and increases the pie total by (amount).

PAGENAME "(name)"
Sets the name to appear on an output tab.

TRANSLUCENCE (integer 0-255)
Sets the translucens of layers in a ColoringBook.

LAYERS "(path to directory)"]
Sets the paht to a directory containing image layers in a ColoringBook. See More "More About Coloring Books" for an example.

VERSION "(version string)"]
Sets the PLOT version required to run this script.

Colors

The "usual" colors plus colors from the Tango Desktop Palette are supported: 
    RED  GREEN  BLUE  YELLOW  CYAN  MAGENTA  WHITE  LIGHTGRAY  
    GRAY DARKGRAY  BLACK BUTTER  ORANGE  CHOCOLATE  CHAMELEON 
    SKYBLUE  PLUM  SCARLETRED  ALUMINUM  CHARCOAL
Any color can be VERY LIGHT, LIGHT, DARK, or VERY DARK. They can also be TRANSPARENT or VERY TRANSPARENT.

To specify red, green, blue values use RGB(r,g,b), and to specify them with alpha values use RGB(r,g,b,a).

There are color examples on "Entrance Colors".

Line Styles

These line styles are supported: 
   DOTTED  DASHED  SOLID

Line Widths

These line widths are supported: 
   THIN  NORMAL  THICK, VERY THICK

Marker Shapes

These marker shapes are supported: 

 BOX                   DIAMOND
 CIRCLE                HORTICK 
 TRIANGLE              VERTICK
 OTHERTRIANGLE         XSYMBOL 
 PLUS

Marker Size

Markers can be any of these sizes 
 TINY             BIG
 SMALL            VERY BIG
 VERY SMALL       BARSIZE
Use BARSIZE to make the width of HORTICKS match the width of bars in a bar chart. Specify the size using a SIZE OVERRIDE column when you need other sizes or want sizes to vary. Override sizes are set using pixels.

Marker Fill

The marker fill type can be: 
   FILLED 
   UNFILLED 
   MASKED

Related URLs

http://dbentrance.com/docs/plotsyntax.html is this document
http://dbentrance.com/blog/ has tips and tricks