'}".
If you use HTML in the formatted name or in the tooltip you must specify the extra option ",allowHtml:true".
35TREE MAP CHARTS
Tree map charts are interactive. Left clicking on a node displays its child nodes. Right clicking moves back up
the tree.
Query
Requirements
Column 1: Text - name
Column 2: Text - parent
Column 3: Text - size
Column 4: Text - (optional) colour
The query must return at least the first three columns, the fourth is optional. The root
node should have a blank parent column.
Multiple Plots No
Axis Overrides No
Data Formatting No
Extra Columns
No
The Google Charts API does allow custom tooltips but they are not supported by
Plotalot for this chart type.
36GEO CHARTS
Plotalot support Geo Charts in four modes:
● Region Mode shows each defined region in a separate colour, with a numeric value that determines the
region's colour, and can be used to convey numeric data for the region. The example above is Region
Mode.
● Markers by Address Mode places markers at named locations on the map with tooltips that can convey
numeric data.
● Markers by Latitude/Longitude Mode is like Markers by Address Mode but uses coordinates instead of
names.
● Text Mode shows the location name on the map.
Query
Requirements
Region Mode Column 1: Text location
Column 2 - optional: Numeric value for relative colour
Markers by
Address
Column 1: Text location
Column 2 - optional: Numeric value for relative colour
Column 3 - optional: Numeric value for relative marker size
Markers by
Latitude /
Longitude
Column 1: Numeric latitude
Column 2: Numeric longitude
Column 3 - optional: Numeric value for relative colour
Column 4 - optional: Numeric value for relative size
Text Mode Column 1: Text location
Column 2 - optional: Numeric value for relative text size
Multiple Plots No
Axis Overrides No
Data Formatting No
Extra Columns Yes
tooltip
You need to obtain a free Google Maps API Key for this chart type:
https://developers.google.com/maps/documentation/javascript/get-api-key
37GANTT CHARTS
Gantt charts show project tasks and their dependencies, and optionally, the critical path.
Query
Requirements
Column 1: Text - Task ID
Column 2: Text - Task Name
Column 3: Text - Resource ID (or null or an empty string)
Column 4: Numeric - Start Date as a Unix Timestamp (or 0)
Column 5: Numeric - End Date as a Unix Timestamp (or 0)
Column 6: Numeric - Duration in milliseconds (or 0)
Column 7: Numeric - Percentage complete
Column 8: Text - Dependencies as a comma separated list of Task ID's (or
null or an empty string)
The query must return eight columns. Some columns may be omitted by specifying 0,
null, or an empty string. At least two of columns 4, 5 and 6 must be specified.
Multiple Plots No
Axis Overrides No
Data Formatting No
Extra Columns No
38CALENDAR CHARTS
Calendar charts show daily activity or volume over months or years.
Query
Requirements
Column 1: Numeric - Date as a Unix Timestamp
Column 2: Numeric - A value that determines relative colour.
An optional third column for customized styling is coming in a future Google Charts
release.
Multiple Plots No
Axis Overrides No
Data Formatting No
Extra Columns No
Calendar charts are not responsive. For use on small screens you need to enclose it in a scrollable container
element.
39ANNOTATION CHARTS
Annotation charts are timeline charts with annotations shown in full on the right hand side.
Query
Requirements
Column 1: Numeric - Date as a Unix Timestamp
Column 2: Numeric - The Y value
Column 3: Text - Annotation Title (or null or an empty string)
Column 4: Text - Annotation Detail (or null or an empty string)
The query must return four columns, the first being a number representing a Unix
timestamp.
Multiple Plots Yes
Each plot has its own SQL query, a colour, and a name.
Axis Overrides
Y Axis
You can override the automatically calculated axis limits and extend the Y axis to
values of your choice.
Data Formatting No
Extra Columns No
40AXIS OPTIONS
X and Y Axis Titles
The X and Y axis titles are optional labels that you can add to the chart. They can be fixed text, or the results of
SQL queries.
Axis Start and Axis End
If you leave the axis start and axis end options blank, the chart axes are determined automatically. The axis
start and axis end options can be used to extend the chart's visible range. Here's a small dataset to illustrate.
X 4 5 6 9
Y 15 20 35 10
The chart on the left does not specify axis limits, so they have been set automatically. The chart on the right
sets the X axis limits at 3 - 10 and the Y axis limits at 0 - 50:
You can specify the limits as fixed numbers, or the result of SQL queries.
If the plot data extends outside the specified limits, Plotalot ignores the specified values and use the limits of
the actual data instead. In such cases a warning is shown in the chart editor (warnings are never shown on the
front end of the site). You can use this feature to set "preferred" axis values, which dynamically extend when
necessary. Let's add a new row to the dataset above, X = 10, Y = -15, but keep the axis limits set the same as
in the right hand chart above.
Now Plotalot generates a warning in the chart editor:
Y start [0] > first Y value in data [-15]
And extends the Y start value to -15.
41More About Data Ranges
Plotalot analyses the data returned by the query (or queries) to determine the axis ranges for the chart. You
can also extend the range of the chart using the axis range options.
For charts with multiple plots, the axis ranges of the chart are the entire range of all the data returned by all
the plots. For example, this chart shows temperature readings for two different years. The X axis range covers
the whole two years:
In this case, we can "trick" Plotalot into plotting both years on top of each other by arranging the queries so
that they both return dates in the same date range. For example, you could add a year to the dates returned by
one of the plots:
SELECT UNIX_TIMESTAMP(CAST(DATE_ADD(date, INTERVAL 1 YEAR) AS DATETIME)),…
If you use this trick, you will probably need to switch off the tooltips because the values shown in them may not
make proper sense. You can do this with the "enableInteractivity" extra option.
X Labels and Y Labels
These options determine the number of labels and gridlines on each axis. The default is -1, which means that
the number of labels will be decided automatically, which is useful if the data is very variable and the number of
labels needs to be dynamic. To set a fixed number of labels, specify an integer greater than zero.
Label Formats
These options determine the format of the axis labels.
Numeric Label Formats
The numeric formats ("99,999.9" etc) format numbers using the thousands and decimal separators of the
user's browser locale.
There is an exception to this for pie charts with values. For pie charts only, the numeric values are formatted by
Plotalot on the server, which does not know the user's browser locale. So for the Y axis formats, you will notice
42that there are more numeric formats ("99,999.9", "99 999,9", etc). For most charts there will be no difference
between "99,999.9" and "99 999,9". But for the numeric values of pie charts, there will.
Inverted Axes
Most chart types have a "vAxis.direction" option:
https://developers.google.com/chart/interactive/docs/gallery/linechart
You can use this in the chart's Extra Options (see below) to make charts with inverted axes. For example:
,vAxis:{title:'Rank',gridlines:{color:'transparent',count:7},format:"#,##0",direction:-1}
Date Label Formats
The date and time options require the source data to be a Unix timestamp. For example, a query for a chart of
temperatures for a range of dates might be:
SELECT UNIX_TIMESTAMP(Date), Value FROM Temperature_History
You can then use any of Plotalot's date or time formats to format the X axis labels.
The date and time format options only work correctly if the data value being formatted is a true Unix DateTime
value, that is, the number of seconds since the 1st of January 1970. They won't work correctly for any other
type of date or time value. You need to make sure your SQL query returns date/times as Unix DateTimes.
Custom Date Formats
One of the X label formatting options is "Custom Date". When you select it, a field appears for you to enter a
custom date format, according to the rules given here:
https://unicode-org.github.io/icu/userguide/format_parse/datetime/#date-field-symbol-table
The custom format only works for dates and times. You can't use it to format ordinary numbers.
PLOT OPTIONS
Some types of chart can only show one data series, for example pie charts. But line graphs and bar charts can
show several sets of data on the same graph. For these charts, Plotalot lets you specify multiple plots.
To change the number of plots, just change the number of plots in the Chart Options and click Save.
Each plot has four or five properties.
Name The name is used as the legend label for the plot. It can be text or a SQL query.
Colour The colour used for the plot. If you leave this blank the colour is decided automatically.
43Style Options for the lines on line graphs and the colouring of pie charts.
Enable Enable toggles the plot between enabled and disabled modes. Disabled plots are not drawn.
Query The large text area is where you write the SQL query that returns data for the plot.
44EXTRA OPTIONS
The Google Charts API has far too many options for them all to be accommodated in a reasonable user
interface, and they are continually being added to. So Plotalot just shows a manageable number of options that
can satisfy many common needs, but it does not stop you from using the full power of the Google API. The
Extra Options field can be used to add additional Google Charts options to a chart, or override the options
chosen by Plotalot. All of the options are described in the Google Charts API documentation.
To find the relevant options, go to the Google Chart Gallery, then click on the chart type you are working with,
and scroll down to "Configuration Options". Some options are common to several charts, but it's always best to
look at the options for the specific chart you are dealing with.
Plotalot will quite happily work with many options that we have never tried, or even heard of. However, please
note that we can't guarantee it will work with every option.
Please don't ask us questions about Extra Options. We have some experience of some of the options, but we
don't know about all of them. The correct place to ask detailed questions about API Options is:
https://groups.google.com/forum/#!forum/google-visualization-api
Option Syntax
The format of the Extra Options field is critical. Any errors will cause a Javascript error, and the chart will not
be drawn.
Plotalot's main job is to construct the Javascript that will draw your chart. You can see the Javascript
constructed in the Script tab. Part of the script is the options. Here's an example from one of the sample charts.
We have re-formatted it to make it easier to read.
var options = {
title:'Sample Stacked Bar Chart',
width:1200,
height:400,
backgroundColor:'#E6E5E5',
titleTextStyle:{color:'#0000FF'},
isStacked:true,
series:[{color:'00CC00'},{color:'FFFF00'},{color:'FAA500'}],
legend:{position:'bottom'},
hAxis:{title:'',gridlines:{count:16},format:'#,##0'},
vAxis:{title:'',gridlines:{color:'transparent'}}
};
Plotalot chooses the options and parameters to add based on your selections in the chart editor. Each option is
separated by a comma. Any extra options are added at the end of the options chosen by Plotalot. When you
add extra options, you must always place a comma before each new option, to separate it from the previous
option. You must not place a comma after the last option because it will cause an error in some older browsers.
Simple Options
Let's examine the options shown above in detail. Some options are easy, for example:
title:'Sample Stacked Bar Chart'
If you look up the "title" option in the Google documentation, you will see that it is described as a "string".
String values must be enclosed in single or double quotes.
45Width and height are numbers. They do not need quotes. isStacked is a boolean, which is either true or false,
again without quotes.
Objects
Now look at titleTextStyle. Notice the different construction, with the curly brackets:
titleTextStyle:{color:'#0000FF'}
If you look up "titleTextStyle" in the documentation, you will see that it is described as an "Object", and that it
has more options than just the colour. An Object is a container for several options. Objects can be nested
within other Objects (you may have noticed that the whole "options" list is in fact an Object). The curly
brackets are Javascript notation for an Object. In fact, titleTextStyle can contain several more options:
{color: , fontName: , fontSize: }
So we could increase the title font size of the chart by adding this string in Extra Options:
,titleTextStyle:{fontSize: 18}
If you added that extra option and examined the chart script again, you would now see two entries for
titleTextStyle :
titleTextStyle:{color:'#0000FF'},
…
titleTextStyle:{fontSize: 18}
Extra Options are always added at the end of all the options generated by Plotalot, so they always override the
options chosen by Plotalot. In this case, the second titleTextStyle Object completely replaces the first, which
means that the color option from the first titleTextStyle Object is no longer active. Although the text is now
larger, it is no longer blue. If we want to keep both options, we would have to re-specify the color in the second
titleTextStyle Object, like this:
,titleTextStyle:{color:'#0000FF', fontSize: 18}
Some options are Objects with more Objects nested inside them. Notice that the hAxis option is an Object, and
that the gridlines option inside it is also an Object.
Just remember that if you want to override any property of an object, you must re-specify the entire object. It's
easy enough once you get used to it, but you must take care to get the curly brackets exactly right.
Arrays
There is one more construct you may need to know about. Look at the series option above, and notice the
square brackets:
,series:[{color:'00CC00'},{color:'FFFF00'},{color:'FAA500'}]
If you look up "series" in the documentation, you will see that it is described as an "Array of objects, or object
with nested objects". The square brackets are Javascript notation for an Array. In fact, the Objects have several
options that you could use, for example:
,series:[{color:'00CC00',curveType:'function'},
{color:'FFFF00',visibleInLegend:false,lineWidth:'3'},
{color:'FAA500'}]
46Documentation Syntax
Javascript's curly bracket syntax is not very documentation-friendly, so the Google documentation uses the
"dot" notation to describe the properties and structure of Objects, for example:
chartArea.left
This means that "left" is a member of the "chartArea" Object. You need to write this in Extra Options using
Javascript Object notation, like this:
,chartArea:{left:'10%'}
Examples
We'll now give a few examples of useful Extra Options. There are many more possibilities.
titleTextStyle
This option applies to all chart types that have a title, and controls the style of the main chart title. You can use
html colour names or # colour notation.
,titleTextStyle:{color:'blue', fontName:'Comic Sans MS', fontSize:18}
hAxis.titleTextStyle and vAxis.titleTextStyle
These options control the style of the axis titles. Note that since hAxis and vAxis are also used by Plotalot, you
must repeat the options that Plotalot sets for these options.
,hAxis:{title:'X Axis Title',
gridlines:{count:16},format:'#,##0',textStyle:{color:'white'},
titleTextStyle:{color:'lightgray', fontName: 'Tahoma', fontSize: 11}}
hAxis.textStyle and vAxis.textStyle
These options control the style of the axis labels. Note the comments above about Plotalot's use of these
options.
,vAxis:{textStyle:{color:'lightgray'}}
backgroundColor.stroke and backgroundColor.strokeWidth
Controls the chart border.
,backgroundColor:{stroke:'gray',strokeWidth:2}
chartArea
This option applies to most chart types and is very useful for controlling the spacing around the chart. It makes
a huge difference to pie charts. These two charts have the same dimensions, but the one on the right has:
,chartArea:{top:'3%',width:'100%',height:'100%'}
47With some chart types, giving the chart area 100% of the drawing area leaves no room for titles or axis labels,
so you need to adjust the option values to get the best results in each case.
These two charts have the same dimensions, but the one on the right has:
,chartArea:{left:'8%',top:'10%',width:'90%',height:'75%'}
bar.groupWidth
This option applies only to bar charts, and controls the width of a group of bars, with the width specified in
pixels, or a percentage of the available width for each group, where '100%' means that groups have no space
between them. The chart on the left does not have this option. The chart on the right does:
,bar:{groupWidth:'85%'}
curveType
This option applies only to line charts, and controls the curve of the lines.
,curveType:'function'
It works well for line charts that don't have many data points, creating smooth curves. The chart on the left
does not have this option. The chart on the right does:
enableInteractivity
You can switch off the tooltips for a chart using this option.
48,enableInteractivity:false
isStacked
You can create a stacked area chart using this option. The chart on the left does not have this option. The chart
on the right does:
,isStacked:true
Trendlines
Many chart types support automatically drawing trendlines, for example:
,trendlines: { 0: {}}
Check the documentation at https://developers.google.com/chart/interactive/docs/gallery/trendlines
Dynamic Extra Options
If the Extra Options field begins with "Select", it is assumed to be a SQL query, and will be resolved to form the
extra options string.
EXTRA COLUMNS
For some chart types, the Google Charts API supports extra columns that tell the API more about how to draw a
chart. The Google documentation calls this feature "DataTable Roles":
https://google-developers.appspot.com/chart/interactive/docs/roles
Plotalot allows you to specify which extra column types your query will return, and will then pass the data to
the API in the correct format.
The extra columns supported by Plotalot are:
Name Data type
annotation string
annotationText string
certainty number (0=false, 1 = true)
emphasis number (0=false, 1 = true)
interval number
scope number (0=false, 1 = true)
tooltip string
49style string
Example 1 - Bar Chart Annotations
Let's add some annotations to the Sample Bar Chart. First we specify one extra column, "annotation". This tells
Plotalot that our query will supply one additional string column after the two normally returned for a bar chart.
Plotalot will now require that your query returns three columns.
Then we'll add the extra column to the sql. For this example the values are hard coded, but of course you can
use any combination of SQL functions to calculate and format the values. Note that since the annotation data
type is "string", the query must return the column value as a string.
SELECT 'Monday', 1,'25%'
UNION SELECT 'Tuesday', 3,'75%'
UNION SELECT 'Wednesday', 2 ,'50%'
UNION SELECT 'Thursday', 4, '100%'
Here's the result:
Example 2 - Annotations on a Line Chart
Now we'll add two extra columns to the Sample Line Chart. We will specify "annotation, annotationText"
(without the quotes) in the Extra Columns field. This tells Plotalot that our query will supply two extra columns
after the two normally returned for a line chart. Plotalot will now require that your query returns four columns.
The first extra column is the annotation that will appear on the chart, and the second is the contents of the
tooltip that shows when you hover the mouse over the annotation. Here's the new sample data:
Plot 1:
SELECT 1, 3, 'A', "Long annotation A"
UNION SELECT 2.5, 2, 'B' , "Long annotation B"
UNION SELECT 4, 4, 'C', "Long annotation C"
Plot 2:
SELECT 0.5, 2, 'D', "Long annotation D"
UNION SELECT 1, 3, 'E', "Long annotation E"
UNION SELECT 2.5, 5, 'F', "Long annotation F"
UNION SELECT 4,4, 'G', "Long annotation G"
UNION SELECT 4.5,3, 'H', "Long annotation H"
Here's the result, hovering the mouse over annotation H.
50Example 3 - HTML Tooltips
Let's add some tooltips to the sample line chart.
● We need to add the extra column "tooltip".
● We also need the extra option ",tooltip: {isHtml: true}".
Here's the data for one of the rows:
UNION SELECT 2.5, 5,'Hello X=2.5 and Y=5.0 '
Example 4 - Style
The style role can change the styling of bars, lines and data points. In this example we added the style "strokewidth:7; stroke-color:green" to one of the data points.
We also added an annotation "The big event" to the previous data point.
51ERRORS AND WARNINGS
The display area of the chart editor shows any errors or warnings that apply to your chart.
Plotalot Warnings
Warnings tell you about a setting that you have made that has been ignored because it cannot be applied to the
current chart. Warnings do not prevent the chart being drawn. They are shown for information. They are never
shown when the chart is drawn on your website. The most common warnings are when axis overrides cannot
be applied because they are inconsistent with the retrieved data. For example:
Warning: Y start [0] > first Y value in data [-6.25]
This warning is saying that you have asked for the chart's Y axis to start at the value zero, but Plotalot is
ignoring your request because the lowest value in the relevant dataset is -6.25. You can read more about this
in the section on Axis Options.
Plotalot Errors
Errors prevent the chart being drawn. There are two main types of errors, Plotalot errors and SQL errors. This is
a typical Plotalot error:
Error: For a Line graph, the first two columns must be numeric.
Plot 2 column 1 [Date] is non-numeric, and column 2 [Value] is numeric
This error has occurred because the SQL query for plot 2 has returned the wrong column type(s) for the
selected chart type. In this case, a line graph was selected, which requires two numeric columns, but the query
returned a non-numeric column called Date and a numeric column called Value. The chart cannot be created
until you correct the query to return the correct types of columns.
This is a typical SQL error:
Error: Plot 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'DATE(CURDATE())) ORDER BY UnixDateTime' at line 1 [1064]
The SQL error code [1064] is included at the end of the error message. Sometimes it helps to Google the error
number, for example, "SQL 1064".
The chart cannot be created until you correct the error.
Javascript Errors
Javascript errors are detected by your browser, and indicate a problem parsing the chart script. The most likely
cause of a Javascript error is an error in your Extra Options.
A typical Javascript error looks like this:
Javascript error: Uncaught SyntaxError: Unexpected token }
Clear the Extra Options field, and if the error goes away, check your option syntax carefully.
52Tables
Plotalot can draw tables in three different ways.
Plotalot
Tables
Html tables using
,
, and
elements.
No Javascript.
Not responsive.
Cannot be dynamically sorted by column.
No CSV download.
Easy to re-style.
Suitable for small tables that do not need to be responsive.
Plotalot can add a totals row.
Responsive
Tables
Css tables using display:table, display:table-row and display:table-cell
No Javascript.
Highly responsive.
Cannot be dynamically sorted by column.
No CSV download.
Plotalot can add a totals row.
Google
Tables
Generated in the browser using Javascript provided by Google.
Scrollable on small screens.
Clicking column headings sorts by column.
Can add a CSV download button.
Extra Options include row numbers, paging, frozen columns, etc.
Cells can have their own scroll bars.
Plotalot cannot add a totals row.
COMMON FIELDS
The fields of the table editor change depending on the table type. Some fields are common to all types.
Name
The table name is just for your reference. It is not displayed with the table generated.
Type
Plotalot, Responsive, or Google.
Database
To use a database other than the current Joomla site you must first create it using the "Databases" menu. You
will then be able to select it here.
Max Rows
You may want to limit the number of rows shown on your web page. If you want the first 10 rows you can use
the SQL LIMIT function that we introduced in the SQL section. But what if you need to show a representative
sample of a large dataset? You can't easily do that in SQL, but that's exactly what Max Rows does. You specify
how many rows you want, and Plotalot samples the data set to display the number of rows you asked for.
Leave Max Rows blank to show all rows.
53Totals
See Table Totals below.
Query
The query can be any valid select query. The table will include the relevant number of rows and columns.
PLOTALOT TABLES
These are HTML tables using
,
, and
elements. The HTML is constructed by the Plotalot code
running on the server, and is delivered to the user's browser in the usual way. No Javascript is involved.
The following additional fields are shown for Plotalot tables.
Title
This can be fixed text or a SQL query. If the result is non-blank, it is added to the table as the top row.
Column Headings
This option will either include the column names as headings or not, as you choose. Your query can modify the
heading names using the AS clause.
SELECT `product_name` AS `Product`, FORMAT(`net_price`, 2) AS `Net Price`
Classes and Styling
The table editor lets you enter your own class names for the various elements of Plotalot tables. The sample
table uses the class names shown, which have default styles defined in the supplied plotalot.css file.
Name Html Element to Which the
Class is Applied
Sample Table
Class Name
Table Style
plots1
Title row style
title
Heading row style
heading
Odd row style
odd
Even row style
even
If you apply the sample table class names to your tables, your tables will be styled like the sample table above.
If your front end site template loads the Bootstrap CSS, you can create well formatted tables simply by setting
the table class to "table table-bordered table-striped table-condensed width-auto".
The Plotalot front end CSS file is loaded for Plotalot tables, so you can add your own styling there.
54Responsive Behaviour
Plotalot tables are not responsive so are best used for small tables with only a few columns.
RESPONSIVE TABLES
These are also HTML tables, but instead of
,
, and
elements, they use
elements with
classes that correspond to the table, rows and cells. The supplied front end CSS file provides the styling. The
HTML is constructed by the Plotalot code running on your server, and is delivered to the user's browser in the
usual way. No Javascript is involved. In their full width format, responsive tables look exactly the same as
Plotalot tables.
The following additional fields are shown for Responsive tables.
Title
This can be fixed text or a SQL query. If the result is non-blank, it is added to the table as the top row.
Column Headings
This option will either include the column names as headings or not, as you choose. You can modify the heading
names as described for Plotalot tables.
Width and Height
If you leave these fields blank, the table takes as much space as it needs. If you enter numbers (in pixels) in
either of these fields, the table is contained within the dimensions specified, with scrollbars if necessary. In
responsive "narrow" mode, the width and height are ignored.
Classes and Styling
The table editor lets you enter a class name for the outermost
element of the table, which enables you
to style tables individually. The names of the classes used for the inner table, rows and cells are fixed. You can
re-style them as required by changing them in the Plotalot CSS file, or override them based on the outer div
class. Use your browser developer tools to discover the classes applied to the various elements.
The Plotalot front end CSS file is loaded for Responsive tables, so you can add your own styling there.
Responsive Behaviour
On larger screens, the supplied styling makes Responsive
Tables look exactly the same as the Plotalot sample table.
But on small screens, Responsive Tables completely
change their layout, with each row becoming a separate
list, in a format that works well on even the smallest of
screens
55GOOGLE TABLES
Google tables are constructed using the Google Charts API. Plotalot constructs the Javascript and the Javascript
data object, and the Javascript running in the user's browser constructs the HTML using
,
, and
elements. These tables provide scrolling, paging, sorting, and other active features.
Clicking on a column heading dynamically sorts the rows by that column. Numeric columns sort numerically if
the data in the column is numeric. Note that numbers that include commas are treated as strings and will not
sort as you would expect. See the Number Formatting section below for details.
The following additional fields are shown for Google tables.
Width and Height
If you leave these fields blank, the table takes as much space as it needs. If you enter numbers (in pixels) in
either of these fields, the table is contained within the dimensions specified, with scrollbars if necessary.
Row Numbers
Whether to include a row number column as the first column of the table.
Extra Options
If you can find your way around the Google Charts API Reference documentation, you can manually add
additional options here. There are more details and examples in the Extra Options section. Plotalot cannot
validate the options you enter, so you can easily create Javascript errors here.
You could enable page-forward and page-back buttons with:
,page:'enable'
CSV Link
If you have the Plotalot Plugin, you can add a link below the table to download a Comma Separated
Variable .csv file containing the table data. The link can include HTML, so it can be something like
. Leave this field blank for no CSV link.
Classes and Styling
Google tables assign default class names to most table elements. The table editor lets you enter your own class
names to replace the default Google classes, in case you need different styles in different tables.
Note that the
element itself always has class "google-visualization-table-table". It can't be changed.
56Name Element Default Class
Heading row
elements in google-visualization-table-tr-head
Odd rows
elements of odd rows in
google-visualization-table-tr-odd
Table row
elements of even rows in
google-visualization-table-tr-even
Selected row
of the selected row google-visualization-table-tr-sel
Hover row
of the row hovered over google-visualization-table-tr-over
Header cell
elements in google-visualization-table-th
Table cell
elements in
google-visualization-table-td
Row number cell The first
of each row google-visualization-table-seq
If you only have one table, or if you want all of your Google tables to have the same style, there is no need to
override the default class names. You can just make your own CSS styles override the Google styles.
The Plotalot front end CSS file is loaded for Google tables, so you can add your own styling there.
Google tables can be tricky to re-style. Use your browser developer tools to see the classes applied to the
various elements, and the CSS precedence required to override them.
Examples
Override the background colour for the odd and even rows:
tr.google-visualization-table-tr-even {background-color: pink}
tr.google-visualization-table-tr-odd {background-color: yellow}
Override the background colour for the heading:
This is more complex because Google assigns the class "google-visualization-table-tr-head" to the
element, and "google-visualization-table-th gradient" to the
elements. To assign new colours and remove
the gradient, we need:
tr.google-visualization-table-tr-head {background-color:black;color:white}
tr.google-visualization-table-tr-head th.gradient {background-image:none}
Responsive Behaviour
On narrower screens, Google tables acquire a horizontal scrollbar, which some people regard as responsive
behaviour, and some don't.
57NUMERIC FORMATTING
The easiest way to format numeric data is to use the MySql FORMAT function in your SQL query. For example,
to format a number in a typical European format with two decimal places, you could use something like:
SELECT FORMAT(`column_name`, 2, 'de_DE');
Note that the inclusion of dots and/or commas in the formatted number results in the column being determined
as a string column. For Plotalot and Responsive tables this is fine and makes no difference. However for Google
tables it does make a difference.
Google tables allow the front end user to dynamically sort columns by clicking on the
column headings. When numbers with thousands separators are sorted as strings, the sort
sequence is incorrect, as shown on the right.
For Google tables, you should have your SQL return numeric values unformatted, so that
the raw data sent to the browser is numeric. You can then add a custom formatter to the
Extra Javascript field, so that the table Javascript can format the numbers correctly for
display. Your numeric columns will then sort correctly
To format a number in a typical European format with two decimal places, you would use
something like:
var formatter = new
google.visualization.NumberFormat({decimalSymbol:',',groupingSymbol:'.'});
formatter.format(window.plotalot_chart_117_data, 3);
In this example the chart number is 117 and we are formatting the 4th column (counting from 0, so column 3).
Here's another example, formatting a column as 12 digits without thousands separators:
var formatter = new
google.visualization.NumberFormat({pattern:'############'});
formatter.format(window.plotalot_chart_126_data, 3);
The parameters for the NumberFormat function are here:
https://developers.google.com/chart/interactive/docs/reference#numberformat
CONDITIONAL FORMATTING
Sometimes you might want to format values differently depending on their value. Again, you could use SQL to
include your formatting in the retrieved data. For example:
SELECT `id`,`title`, `language`,
CONCAT('',`hits`,'') AS `hits`,
SUBSTRING(`introtext`,1,40) AS `introtext`, `created`, `modified`
FROM `#__content`
58But the resulting data becomes a string rather than a number, and for Google tables this could mean that the
column no longer sorts as expected. Again, we can use Extra Javascript to add the required formatting to the
data, while keeping the column data numeric. In this example the Javascript is more complex as we need to
iterate through the Google data table, assigning different formatting to each row:
var rows = window.plotalot_chart_103_data.getNumberOfRows();
for (var i=0; i < rows; i++)
{
var hits = window.plotalot_chart_103_data.getValue(i, 3);
if (hits < 100)
window.plotalot_chart_103_data.setProperties(i,3,
{style:'background-color:red;color:white;font-style:bold;'});
if (hits >= 100)
window.plotalot_chart_103_data.setProperties(i,3,
{style:'background-color:green;color:white;font-style:bold;'});
}
HYPERLINKS IN TABLES
The sample table shows an example of how to build hyperlinks using the SQL CONCAT function. Here's the SQL
used by the sample table. The %%J_ROOT_URI%% variable is used to provide the current site address.
SELECT `title` AS `Article`, DATE(`modified`) AS `Updated`,
CONCAT('', `title`, '') AS `Link`
FROM `#__content`
ORDER BY `modified` DESC
LIMIT 10
TABLE TOTALS
Plotalot can add a totals row to Plotalot tables and Responsive tables. It can't do this for Google tables because
the sort function could move the totals row into the body of the table.
Specify a list of columns to total, e.g. 1,2,3 to add totals for the first three columns. For numeric columns, the
total is the sum of the row values for that column. For non-numeric columns, the total is the number of rows.
You can optionally specify the number of decimal places, decimal separator, and thousands separator. If you
don't specify them they default to 0, ".", and none (prior to version 6.19 they defaulted to 0, ".", and ",").
Use semi-colons (;) to specify these, for example:
1,2,3 formats the totals with no decimals and no thousands separators
1,2,3;2;.;, formats the totals with 2 decimal places, decimal separator ".", and thousands separator ","
1,2,3;0;,;. formats the totals with no decimal places and thousands separator "."
You can use SQL number formatting for column values that are to be totalled, provided that the decimal and
thousands separators used by the SQL formatting match the separators specified for the totals. Plotalot will use
the separators specified to interpret the SQL-formatted numbers. For example, if the decimal and thousands
separators specified for the totals are "," and ".", Plotalot will interpret a column value "123.456,789" as the
decimal number 123456.789.
59Single Items
Single items return a single value from a SQL query with no added formatting, so that when they are used in an
article they appear to be part of your article text. For example: “Our download count today stands at 436,991",
or "today is Monday the 15th of March". A single value just returns the first column of the first row returned by
the query, with no formatting or html at all. Here’s another example:
SELECT CASE WHEN HOUR(NOW()) >= 18 THEN "Good evening"
WHEN HOUR(NOW()) >= 12 THEN "Good afternoon"
ELSE "Good morning" END
There are no rules about the number of columns or their type.
If you set the Plotalot component configuration option to allow non-select queries, you can use a single item to
insert or update a database row every time a page is visited. For example:
UPDATE `#__hit_counts` WHERE `page` = '51' SET `counter` = `counter` + 1
60External Databases
The default database for new charts is "This Site", which means that the chart's SQL queries will run against the
Joomla site database.
Plotalot can also query external databases. To do that, you first create a record in the "Databases" list, that
tells Plotalot how to access the external database.
DEFINING A DATABASE
When you create a new database record, you give it a name that you will use to select it in chart definitions.
You also supply all of the connection and authorisation parameters. The editor shows a list of the database
drivers found on your system, and whether or not your server has the runtime libraries they need.
Name
The name is used to select the database in chart definitions. You can rename database records at any time.
They are linked to charts by their internal ID number, not by name.
Driver
Specify the name of a database driver from the right hand panel, for example "oracle".
61Host
If a database runs on the same computer as the web server, the host is normally "localhost". If not, you may
need to enter its IP address, or its name in the form "db.myhost.com". If the database uses a non-standard
port, you can add the port number to the host string, like "db.myhost.com:3307".
Db name
The name of the database on the database server. For Sqlite databases, the database name must be the full
absolute pathname of the SqLite database file.
User and Password
The username and password that will be used to connect to the database. These will be stored in the
xxx_plotalot_databases table of the Joomla database. The password is obfuscated (not human readable) but
not encrypted, so consider creating a special user with the minimum necessary access rights.
Prefix
The table name prefix for the external database, if there is one. If queries to the database use the "#__"
convention, Plotalot will substitute the "#__" for the prefix specified here.
THE TEST BUTTON
When you have saved the database record, you can use the Test button to
check that Plotalot can access it correctly. Plotalot will attempt to connect to
the database and query the database version. If the query works correctly
you should see a green message with the version number.
If the connection fails you should see a pink message giving
details of the error. In this example, the system does not have
the Microsoft Sql Server extension for PHP.
62Component Configuration
You can access the Component Configuration for Plotalot from Joomla Global Configuration
or from the Options button on the Plotalot chart list. The Options button is only shown for
Super Users.
PERMISSIONS
"Access Administration Interface" controls whether users can access the backend of Plotalot. By default, only
Super Users have access, but you can grant access to other user groups if you wish.
Regardless of the "Access Administration Interface" setting, only Super Users can save changes to the Plotalot
CSS file, import charts, or edit or save Databases
OPTIONS
Hide Sub-menu
There is an option to hide the Plotalot component sub-menu. In Joomla 3 you will most likely want to keep the
sub-menu active. In Joomla 4, depending on the size of your screen and whether you have the main menu
expanded or collapsed, you might prefer to hide the sub-menu.
Background colour
The background colour for the chart area of the chart editor. You will only see this colour behind a chart with a
transparent background. You might want to set it the same as the background colour of your website, so that
you can get a better idea of how charts will look on your site. The default setting is 'transparent'.
Select Only
Defaults to On, which means that Plotalot only allows SQL queries that start with SELECT or (SELECT. With this
setting you can be confident that Plotalot cannot make any changes to your data. If you switch this option off,
you can use any valid SQL query, including queries that update data.
Multiple Queries
Defaults to Off, which means that plots can only use a single query. If you enable this option you can use
multiple queries, separated by semi-colons. When a plot query uses multiple queries, all except the last query
are merely executed. The result set for the chart is obtained from the last query. For example you might want
to call a stored procedure before generating your chart values:
CALL `my-procedure`; SELECT `col1`, `col2` FROM `my-table`;
Fix Nulls
Defaults to On, which means that when Plotalot finds nulls in your sql results, it changes them to a zero (0) for
numeric columns, and an empty string ('') for text columns. It also issues a warning in the chart editor. No
warning is issued when such a chart is drawn on the front end.
If you want null values to be included in your chart data as actual null values, you can set this option to "No".
You might want to do this if you plan to use the Google Charts InterpolateNulls option to guess the value of
missing data points, or if you want to see actual gaps in your charts.
63If neither of these options is right for you, your query can make use of the SQL IFNULL( ) function to return a
specific value in place of a null value.
The Experimental API
If you need features of the Google Charts API that have not yet been released, you can set this option to "On".
This should not be used on live sites. You can read more about the Google Charts API release process here:
https://developers.google.com/chart/interactive/docs/release_notes#ReleaseProcess
Specific API Version
If a non-zero value is specified here, the specified Google Charts API is used, overriding the "Experimental API"
option.
At the time of writing, the current version of the Google Charts API is 50, and it is causing problems on some
sites. If your charts show the error "b.split is not a function" or "Cannot read property 'offset' of undefined",
please set this option to 49 to load the previous version of the API.
To revert to using the "current" version of the API, set this option to 0 (zero).
Hide Sample Charts
If you no longer need to see the sample charts in the chart list, use this option to hide them.
Locale
You can set the language used by the Google Charts API, as described here:
https://developers.google.com/chart/interactive/docs/basic_load_libs#loadwithlocale
This affects the formatting of values such as currencies, dates, and numbers. For most purposes you can
control formats yourself using the features of Plotalot, but in some case you may need to specify a locale here.
64The Front End And The Plugin
SHOWING CHARTS USING A MENU ITEM
The best way to add charts to your site is to use the Plotalot Plugin, which allows you to add as many charts
as you like, anywhere in any article. The plugin also performs caching of charts to improve performance.
You can, however, add charts to your site in a simplistic way by using the Component View.
Configure a menu item of type Plotalot, "Simple List of Charts".
On the Options panel, you can then enter a page title, some text to be displayed above and below your charts,
and a list of chart ID’s. You can show more than one chart by supplying a comma delimited list. But there is no
way to control the layout or appearance here.
65THE PLOTALOT PLUGIN
The best way to show charts on your site is to use the Plotalot Plugin and include charts in articles.
Install the Plugin and make sure it is enabled.
Once you have defined a chart and installed and enabled the plugin, it's easy to add the chart to an article. In
its simplest form, the syntax to include the chart with an ID of 1 in an article is simply:
{plotalot id="1"}
The chart ID must be enclosed in double quotes. Note that Joomla Article Text Filtering and some article editors
can interfere with the quotes, causing the chart ID not to be recognised. See the Troubleshooting section for
more details.
The plugin cannot display the same chart more than once on the same page. If you need to do that you must
make copies of the chart so that each instance has a different chart ID.
Full Width
By default, the plugin draws charts in a containing
like this:
...
This makes the
the size of the chart, and allows other content to flow around the chart.
The plugin has an optional parameter, "full_width", which removes the "display:inline-block;" attribute. The
now defaults to display:block, and occupies the full width of its container.
{plotalot id="1" full_width}
Results in this:
...
If the chart has its width set to blank or zero, the chart will now occupy the width the container, whatever that
may be. If the window resizes (for example when a mobile device is rotated), the chart will be re-drawn at the
new width. This is a quick and easy way to make charts responsive. A more powerful method is described later.
66Auto-Refresh
The "refresh" parameter automatically re-draws charts at regular intervals. The chart data is re-loaded from the
server using Ajax, and the chart is re-drawn in-situ without re-drawing the entire page. This works for all charts
and Google Tables, but not for Plotalot tables, Responsive tables, or single items.
{plotalot id="42" refresh="30"}
In this example the chart will re re-drawn using the latest data every 30 seconds.
You can see auto-refresh in action by making a Gauge chart with a query of:
SELECT "Time", SECOND(CURRENT_TIME())
Adding a Class
The "class" parameter also removes the style="display:inline-block;", and adds a class instead.
{plotalot id="1" class="half"}
Results in this:
...
Error Handling
Some kinds of errors can prevent a chart being drawn. You may want to specify what replaces a chart if it
cannot be drawn, for example if its source database goes offline, or its data feed fails,. If you do not specify
custom error handling the default is for the plugin to report the error like this:
{Plotalot Plugin: chart 6 - No rows}
This is useful during development but is not ideal for a live site. You have several options.
You can specify an image to be shown in place of the chart. For example:
{plotalot id="42" error_img="images/stories/alternate_image.jpg"}
Or you can specify some text to appear in place of the chart. For example:
{plotalot id="42" error_txt="Sorry, there is no chart data today"}
Or you can allow the plugin to use the last cached version of the chart. See "Caching" below.
Caching
The Plotalot Plugin can cache your charts on your server, reducing the amount of work needed to re-produce
the chart for the next user who requests it. For charts that use complex queries or charts that don't change
very quickly, this is a worthwhile optimisation.
When it produces a chart, Plotalot first has to execute the database query(ies), then analyse the data and
produce the Javascript that will draw the chart. The Javascript becomes part of the web page delivered to the
browser, and the browser executes the Javascript to render the chart. When the Plotalot Plugin caches a chart,
it stores the Javascript in a file on the web server. If the same chart is requested within the allowed cache time,
Plotalot retrieves the text file instead of accessing the database and creating the chart from scratch.
The "Use Cache If Error" option determines if the plugin can use an old, expired, cached version of the chart if a
new chart cannot be drawn. For example, if the data feed to your database fails, it might be better to show the
67most recent chart rather than an error image or error message. If set to "Yes", this option takes precedence
over the "error_img" and "error_txt" parameters.
You can specify the "Cache Time" and "Use Cache If Error" options globally for all charts in the Plugin Manager,
or on the plugin call. Parameters passed on the plugin call take precedence. To enable caching for all charts, set
the "Cache Time" in the Plugin Manager to a non-zero number. This is the number of seconds the cached
version is valid.
Here's how to specify the options on the plugin call:
{plotalot id="42" cache_time="60" use_cache_if_error="yes"}
Summary of Plugin Parameters
Parameter Notes
id="nnn" The chart number to draw. This parameter must always be specified.
If the value is non-numeric, it is resolved as a request variable.
full_width Draws the chart in a
without style="display:inline-block;".
error_img="xxx" Pathname of an image to draw if the chart cannot be drawn.
error_txt="xxx" Text to be shown if the chart cannot be drawn.
cache_time="nnn" Number of seconds to re-use charts without re-generating them.
use_cache_if_error="yes" "yes" means that an expired cached chart can be use if the chart cannot be
drawn.
refresh="nnn" The chart will be re-generated and re-drawn every nnn seconds.
class="xxx" A CSS class to add to the chart
element.
csv="xxx" Optional parameters for CSV downloads.
csv="headrow" adds the column names as the first row of the file.
csv="sep" adds an extra first row "sep=," (without the quotes) which forces
Excel to interpret comma as separator. Do not use this unless your users
exclusively use Excel as other software will treat this as a data line.
csv="headrow,sep" specifies both options.
P000 - P999 Parameters that can be passed into the chart query, as described in the
Advanced Techniques section.
LAYOUT AND POSITIONING
It's very much up to you to construct the layout of your pages and charts, but here are some ideas.
You can use an inline style to float the chart to the right, so that text flows around it to the left.
{plotalot id="2"}
You can place two charts side by side using a table.
{plotalot id="1"}
{plotalot id="2"}
68
Using tables like this is not considered good practice but is quick and easy for inexperienced users. See the
Responsive Charts section for a better solution.
You can place a background image behind a transparent or semi-transparent chart.
{plotalot id="23"}
You can place one chart on top of another. With care, this can be an effective way of showing compound charts,
or additional axes.
{plotalot id="1"}
{plotalot id="2"}
SHOWING A CHART IN A MODULE
You can show one or more charts in a module by using the Joomla Module Manager to create a new Custom
HTML module. In the Custom HTML field, you can put any content you like, including calls to the Plotalot plugin.
It's very important to enable the "Prepare Content" option (on the Options tab). This tells Joomla to process
plugins in the content. Without this option enabled you will just see the unprocessed {plotalot} call.
69CSS, And Responsive Charts
THE PLOTALOT CSS FILE
Whenever Plotalot loads charts on your site, it loads this CSS file, if it exists:
/media/com_plotalot/plotalot.css
The "CSS" menu item is a quick and easy way to edit this file. You can add CSS rules here to control the styling
of your tables, or to control the responsive properties of your charts.
You can edit the file using a text editor if that is more convenient. The file initially contains some styling for the
sample table. You can delete these rules if you don't need them. This file is really for you to use for your own
charts and tables.
MAKING CHARTS RESPONSIVE
The first step in making charts responsive is to set the width of the chart to blank or 0 (zero). Charts that have
a defined width and height will always be drawn at the specified size and will never change their size. Charts
with zero width are drawn to match the size of their containing element. We can then use CSS to control the
size of the containing element, and its behaviour at different window sizes.
Google charts do not automatically re-size in the way that ordinary images do. In order to be responsive, the
browser must be told when to re-draw them. The Plotalot Plugin includes Javascript to do this. You can see
this working on our website:
https://www.lesarbresdesign.info/extensions/plotalot
This dynamic resizing works in all modern browsers, but not in IE8 or below.
The Plotalot Component view does not include the responsive Javascript. If you use the Component view to
display charts of width zero, they will initially be drawn the size of their containing element (which is partially
responsive behaviour) - but they will not dynamically resize if the window size changes, for example if a mobile
phone or tablet is rotated.
A few templates (for example Gridbox templates from Balbooa) do not allow their grid elements to resize as the
browser window resizes. Google charts cannot be made responsive in such templates.
70FULL WIDTH CHARTS
The easiest way to make a chart responsive is to set its width to zero and use the plugin's "full_width" option.
This will make the chart the full width of the article it appears in, whatever that happens to be at any given
time. As the window size changes, the plugin will re-draw the chart. In many cases this is a simple and
effective solution.
Normally charts are drawn in a
element with a style="display:inline-block" attribute so that the chart
merges inline with the flow of the article. But when "full_width" is specified, Plotalot omits the
style="display:inline-block" attribute from the
element that it draws the chart in. Since the
is now
display:block, it occupies the full width of its container. As the window size changes, so does the
containing the chart, and the plugin re-draws the chart to match it.
SIZING CHARTS WITH CSS
If you want to control exactly how your charts resize you can use CSS. Plotalot always draws charts in a
element with an "ID" attribute that includes the chart ID, which means you can target them with the CSS #
selector:
HTML:
CSS: #chart_17 {height:200px; width:350px;}
You can define your chart
sizes using pixels or percentages, and define their minimum and maximum
sizes using the CSS properties "min-width" and "max-width". You can use CSS media queries to control how
they respond to different window sizes.
In this example, the chart is initially set to 350 x 200 pixels. But when the screen width is below 750 pixels, the
height changes to 150px and the width changes to 100%, meaning that it will occupy the full width of its
containing element.
#chart_17 {height:200px; width:350px;}
@media screen and (max-width:750px)
{
#chart_17 {height:150px; width:100%; }
}
This works on both desktops and mobile devices.
Two Charts Side-by-Side and Responsive
Let's put two charts side-by-side. This time we'll use the plugin's "class" parameter instead of targeting the
charts by ID.
{plotalot id="40" class="half"} {plotalot id="42" class="half"}
This results in two
elements, like this:
On a wide enough screen, we want them to sit side-by-side. But on a mobile phone, we want them to be full
width, one below the other. This CSS will do that:
.half {display:inline-block; height:200px; width:48%;}
71@media screen and (max-width:750px)
{
.half {height:150px; width:100%;}
}
On a wide enough screen, each chart will be a little less than half the width of the page, allowing them to sit
side by side. As the screen width reduces they will reduce proportionally, remaining remaining side-by-side until
the window falls below 750 pixels, at which point both charts become 100% wide, forcing them to flow one
below the other.
For non-trivial layouts, you need to be careful that your article editor does not add unwanted html elements
around or between your charts. Some wysiwyg editors add lots of
and
elements that can upset your
responsive layouts. For this kind of work, we usually set the Joomla article editor to CodeMirror or None.
Do remember that the Plotalot CSS file gets over-written when you re-install or upgrade Plotalot. Keep a
master copy of your CSS somewhere else.
Styling the PNG and CSV buttons
The PNG and CSV links can include HTML, so you can specify the links as buttons with CSS classes. For
example, if your template loads bootstrap.css, you can use the "btn" classes, like this:
and
The buttons are wrapped in a
of class "pl_pbuttons". The PNG button is wrapped in a
of class
"pl_plink", and the CSV button is wrapped in a
of class "pl_clink".
72Advanced Techniques
This section is intended only for advanced users with relevant Joomla and web development experience. Nontechnical users are unlikely to succeed in using the techniques described in this section. Since discussions about
these techniques can be complex and time-consuming, support for the features and techniques described in this
section is only available for users who have purchased Plotalot Option 3.
PLOTALOT VARIABLES
You can make use of the following variables in any text or query.
%%J_USER_ID%% The id of the currently logged on user
%%J_USER_NAME%% The name of the current user
%%J_USER_USERNAME%% The login/screen name of the current user
%%J_USER_EMAIL%% The email address of the current user
%%J_ROOT_URI%% The URI of the current site, e.g. http://www.mysite.com/
%%J_ROOT_PATH%% The file system path of the current site, e.g. G:\xampp\htdocs\test
%%J_CURRENT_DATE%% The current date as YYYY-MM-DD, retrieved using Joomla's date functions.
%%Pn%% Any number of Plugin Variables. These are defined by an upper case 'P'
followed by a number. For example P0, P1 ... P9, P10, P11, etc.
%%Pn=default value%% Plugin variables can have default values.
Plotalot resolves variables before executing queries. For example:
SELECT `name`, `username` FROM `#__users` WHERE `id` = '%%J_USER_ID%%'
In the back end, this will retrieve the name and username for the currently logged on administrator. In the
front end it retrieves the name and username for the currently logged on user.
PLUGIN VARIABLES
Plugin Variables allow you to pass variables on the call to the Plotalot plugin. For example:
SELECT UNIX_TIMESTAMP(`date`), `%%P2%%` FROM `#__daily_summary`
WHERE `date` BETWEEN '%%P1%%-01-01' AND '%%P1%%-12-31'
This query uses two variables. P1 is the year number used as part of the where clause. P2 is the name of
second column to retrieve.
On the plugin call, you could populate these variables like this:
{plotalot id="68" P1="2009" P2="average_value"}
Plugin Variables only apply when using the Plugin.
You cannot currently use Plugin Variables with Auto-Refresh.
73DEFAULT VARIABLE VALUES
Plugin Variables can have default values. The default value is used if the plugin cannot find a value for the
variable. For example:
SELECT UNIX_TIMESTAMP(`date`), `%%P2=average_value%%` FROM `#__daily_summary`
WHERE `date` BETWEEN '%%P1=2007%%-01-01' AND '%%P1=2007%%-12-31'
Default values are also useful for testing your chart in the back end, where Plugin Variables do not have values.
Default values can only be literal strings. They cannot be variables. However, you can work around this
limitation in SQL, for example:
IF("%%P1%%" = "", "%%J_USER_USERNAME%%", "%%P1%%")
VARIABLES IN PLAIN TEXT
Variables don’t have to be used in sql queries. They can also be used in plain text. For example, in the above
example, the chart title could be:
%%P2=Average%% Temperatures for %%P1=2016%%
The default chart title would then be drawn as “Average Temperatures for 2016”. But the title would change if
P1 or P2 resolved to different values.
USING GET AND POST DATA
You can tell the Plotalot plugin to retrieve data values from Get or Post variables, by specifying their names
with a leading underscore. For example:
{plotalot id="nn" P1="_temperature"}
Passes the value of the Get or Post variable "temperature" as the value of plugin parameter P1.
Get Variable Example
Make a gauge chart with this SQL:
SELECT "Temperature", %%P1=0%%
P1 is a plugin variable with a default value of zero. In the chart editor, the gauge shows zero. Now make an
article with this plugin call:
{plotalot id="nn" P1="_temperature"}
Where nn is the id of the gauge chart. Make a menu item to access the article, and look at the article on the
front end of your site. The gauge still shows zero. Now add the temperature parameter
to the article URL:
http://mysite.com/index.php/plotalot-test?temperature=23
The plugin retrieves the "temperature" variable from the URL, and sets it as the value of
P1. The SQL statement now returns 23 as the second column of the query, and the
gauge shows this value.
74Post Variable Example
It is possible to construct forms in Joomla articles, although you do need to disable your wysiwyg editor to do
so. Build an HTML form in an article, like this:
{plotalot id="68" P1="_year" P2="_column_name"}
The empty
'
Example 4 - Style
The style role can change the styling of bars, lines and data points. In this example we added the style "strokewidth:7; stroke-color:green" to one of the data points.
We also added an annotation "The big event" to the previous data point.
51ERRORS AND WARNINGS
The display area of the chart editor shows any errors or warnings that apply to your chart.
Plotalot Warnings
Warnings tell you about a setting that you have made that has been ignored because it cannot be applied to the
current chart. Warnings do not prevent the chart being drawn. They are shown for information. They are never
shown when the chart is drawn on your website. The most common warnings are when axis overrides cannot
be applied because they are inconsistent with the retrieved data. For example:
Warning: Y start [0] > first Y value in data [-6.25]
This warning is saying that you have asked for the chart's Y axis to start at the value zero, but Plotalot is
ignoring your request because the lowest value in the relevant dataset is -6.25. You can read more about this
in the section on Axis Options.
Plotalot Errors
Errors prevent the chart being drawn. There are two main types of errors, Plotalot errors and SQL errors. This is
a typical Plotalot error:
Error: For a Line graph, the first two columns must be numeric.
Plot 2 column 1 [Date] is non-numeric, and column 2 [Value] is numeric
This error has occurred because the SQL query for plot 2 has returned the wrong column type(s) for the
selected chart type. In this case, a line graph was selected, which requires two numeric columns, but the query
returned a non-numeric column called Date and a numeric column called Value. The chart cannot be created
until you correct the query to return the correct types of columns.
This is a typical SQL error:
Error: Plot 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'DATE(CURDATE())) ORDER BY UnixDateTime' at line 1 [1064]
The SQL error code [1064] is included at the end of the error message. Sometimes it helps to Google the error
number, for example, "SQL 1064".
The chart cannot be created until you correct the error.
Javascript Errors
Javascript errors are detected by your browser, and indicate a problem parsing the chart script. The most likely
cause of a Javascript error is an error in your Extra Options.
A typical Javascript error looks like this:
Javascript error: Uncaught SyntaxError: Unexpected token }
Clear the Extra Options field, and if the error goes away, check your option syntax carefully.
52Tables
Plotalot can draw tables in three different ways.
Plotalot
Tables
Html tables using