Contents

Graphical Charts

The CMS supports Charts using the Google Charts API.  It currently supports ten different chart outputs:

  • Area
  • Bar
  • Column
  • Gaugeste
  • Line
  • Pie
  • Table
  • Stepped
  • Scatter
  • Geography

A full list with examples can be found here:

https://developers.google.com/chart/interactive/docs/gallery

Administration

The CMS integrates charts in two different areas:

  • Query List SQL data plugin
  • Form Tool 

We'll run through the Form Tool first since it's more basic to explain.  The order of columns, and type of data (numbers or characters) can be found in the Google Charts documentation.  For general support with creating charts, our support may be contacted for further guidance.  Although charts are interesting, they are a data feature, which can be used in many advanced ways, and may require some technical savvy to use.

Form Tool Charts

Create or edit an existing Web Form WYSIWYG plugin to begin.  Notice the Chart Box button under the Data category:

If using Form Tool Pro, you may use SQL for generating the data; however, the Query Builder that the Form Tool uses currently is quite limited to keep it as simple as possible, so currently we recommend using the Query List plugin if you are attempting to output a chart based upon data from SQL.  The Form Tool Chart Box can still be useful for including static data charts in forms, or on a page in general, if you do not want to rely on SQL data, or do not have the data within SQL.

If you are using SQL and want to create static data, you may copy the results from running a query in the SQL manager, like so:

If the data is in Excel, you may copy it from there in the same way (by selecting the area you want to copy, then using Edit > Copy, or CTRL+C):

 

Once you've copied the data, you can paste it as text while editing the Chart you inserted into the web form.  This particular example is a Geography Chart.  Notice the Geography Default Region, and Geography Default Resolution.  The Region should match the State you're in, and the Resolution should be metros, if you're using cities.  Other options are Provinces and Countries.  Refer to the Google documentation to find other values for both of these fields.  Also notice the Chart Mode is Geography.

The last thing that is important to notice, is that the pasted text includes the Column Names as the first row.  This is important when using static data.  Note: you can use commas, or you can use tabs to separate values.  Line breaks are expected to separate rows.  You cannot mix commas AND tabs for separating data, as mixing these confuses the parser and will cause unexpected results.  Also note: you may not have blank rows at the bottom, or it will potentially not render the chart.

Once saved you will notice the chart is on the Web Form; however, it does not render.  You can preview the chart using a link that is provided, which will open in a new window temporarily.  This link should not be used for persistent viewing as it is in volatile memory.

Here you can see the end result of this Web Form, including the Chart.

Query List Charts

When creating a chart with the Query List, write your SQL query as usual, but be sure it is in the specific format required for the chart to output.   More about this later.

Notice the new charts feature, which uses a plugin that resides in the HTML template field.  You cannot use an HTML template AND a chart, because the chart plugs into this field.  Other plugins may be added over time.

You'll also notice the Show only this plugin checkbox.  This can be used to hide the normal table listing output for a purely graphical example.  You can hide the normal table output, display a chart, then use the same SQL to generate the Table chart output type, for example.  Alternatively you could display the same information in several chart types, such as scatter for trends, columns for specific information, pie for percentages, then the table chart type for a nice data listing.  There are may possible ways of using the chart output plugin.

We're going to run through every single chart output and the SQL behind it, because they are closely related.  Each type of chart requires a specific data for proper rendering.  Refer to the Google documentation for more information.  All examples use the CMS database for the data source.

Area

In this example we are displaying the number of Members vs. Non Members vs. Public for the last 10 days.

Query

--Top Popular Pages
SELECT TOP 10
 p.[Name] as [Name],
 [Count]
FROM
(
SELECT
v.PrimaryID,SUM(v.[Count]) as [Count]
FROM AppVisitedAny v
WHERE
 v.TypeID=0
Group By v.PrimaryID
) as s
INNER JOIN [Page] p
 ON p.[PageID]=s.[PrimaryID]
ORDER BY s.[Count] DESC

Bar

This chart shows the same data, but is only showing the latest 5 days instead.  The SQL has simply been modified to SELECT TOP 5 from the above example.

Column

This example is the same information and SQL as the above example.

Gauge

The gauge output can have any value, but it is set to understand the range of 0 to 100, so a percentage is the recommended value system.  Each record produces another gauge output, so multiple comparisons in the same gauge are not possible with this output type.

Query

SELECT
 'Created Accounts' as [Label],
 (([Created] * 100) / [Total]) as [User Percent]
FROM
(
SELECT
 (SELECT
  COUNT(*)
 FROM [User]
  WHERE [WebCreationDateTime] IS NOT NULL
 ) as [Created],
 (SELECT
  COUNT(*)
 FROM [User]
 ) as [Total]
) as s

Line

The line chart can be used for comparisons (optionally) as with the first several output types.

Query

The line output here uses the example SQL from the second example.

Pie

The pie chart can only display a single row of data, and displays the information as a percentage pie chart.

Query

This SQL pulls the top 10 popular pages from the website in the past 30 days (popular pages only log for 30 days before recycling).

--Top Website Search Keywords
SELECT TOP 10
v.PrimaryCode as [Search Keyword],
SUM(v.Count) as [Count]
FROM AppVisitedAny v
WHERE v.TypeID=4
Group By v.PrimaryCode
Order By [Count] DESC

Table

The table chart is almost the same as the default Query List output when not using a Custom HTML template.  The main difference is that it looks nicer and allows sorting and row selection.

Query

The SQL used in this example is the same as the second example SQL.

Stepped

The stepped chart is very similar to columns and other outputs and may be used for comparisons similarly.

Query

The SQL in this example is the same as the second example SQL.

Scatter

The scatter chart essentially charts two number values relatively, in a combined grid dot.  You may then see both values when hovering over the dot.

Query

This query pulls two columns, both with numbers of Non Member and Member page requests for that day.  In the above each set of values represents a dot in the grid.  The information this conveys is the overall amount of traffic for both, as well as the details of non member and member requests for that day.  You'll notice there is no day output; this is because the data represents a trend, not specific information.  This type of chart would be more useful for something like age vs. money spent, for instance; however, such an example is outside the scope of this documentation.

--Scatter Non Members vs. Members
SELECT
 ISNULL([Non Members],0) as [NonMembers],
 ISNULL([Members],0) as [Non Members vs Members]
FROM
(
SELECT
 (SELECT
  SUM(v.[Count]) as [Count]
 FROM AppVisitedAny v
 INNER JOIN [Page] p
  ON p.[PageID]=v.PrimaryID
 WHERE
  v.TypeID=0
  AND v.[RequestDate]=s.[RequestDate]
  AND v.[UserTypeID]=0
  AND v.[UserID] > 0
 ) as [Non Members],
 (SELECT
  SUM(v.[Count]) as [Count]
 FROM AppVisitedAny v
 INNER JOIN [Page] p
  ON p.[PageID]=v.PrimaryID
 WHERE
  v.TypeID=0
  AND v.[RequestDate]=s.[RequestDate]
  AND v.[UserTypeID]=1
 ) as [Members]
FROM
(
SELECT DISTINCT TOP 10
 a.[RequestDate]
FROM AppVisitedAny a
WHERE
 [TypeID]=0
ORDER BY
 a.[RequestDate] DESC
) as s
) as t

Geography

This type of chart can be scoped in World (show countries), Country (show provinces/states), or State (show cities/metros).  In 99% of use cases for a typical state society, it is assumed the information that would be most useful is State, displaying cities and information within them.

Query

This particular query displays the number of members in the top most populated cities (by membership).

--Top 10 member cities
SELECT TOP 10
 a.[City],
 SUM(1) as [Count]
FROM [User] u
INNER JOIN [Address] a
 ON u.[ContactAddressID]=a.[AddressID]
WHERE
 LEN(a.[City]) > 0
 AND u.[TypeID]=1
GROUP BY a.[City]
ORDER BY [Count] DESC

Additional Example

A final non comparison example for most chart outputs.

Query

This query selects the top 10 most popular keyword searches and displays the counts.

--Top Website Search Keywords
SELECT TOP 10

v.PrimaryCode as [Search Keyword],
SUM(v.Count) as [Count]

FROM AppVisitedAny v
WHERE v.TypeID=4
Group By v.PrimaryCode
Order By [Count] DESC

Conclusion

The Charts feature of the CMS is a powerful albeit complex tool for displaying easily understood information for reporting, real time, or analysis.