Page History

...

{
         "parameters" : {
            "time_period" : "time_difference",
            "end_date" : "7-Mar-2014 14:03:01",
            "start_date" : "7-Mar-2014 13:48:01"
         },
         "datasets" : [
            {
               "parameters" : {
                  "aggregation_function" : 1,
                  "lineType" : "column",
                  "query" : "select * from nodes",
                  "value_column" : "sum",
                  "axis" : "0",
                  "groupby" : [
                     "group_column"
                  ]
               },
               "data_source" : "local_mysql",
               "options" : {
                  "datasetTitle" : "Groups"
               },
               "name" : "sqlquery_dataset",
               "type" : "sql_query"
            }
         ],
         "options" : {
            "titleText" : ""
         },
         "name" : "SQL Test",
         "type" : "non-time-chart"
      },

Dataset:

Looking at an individual dataset will help us understand: 

...

The SQL to run.  This SQL can contain almost anything you would like.  Currently there is one special "substitution" value that can be specified.  When "user.customer" is found in a query, it is replaced with the name of the customer of the current user (or in the admin's case, the one specified in the advanced menu, if none is selected the query will likely fail).

groupby:

The If the data returned from the SQL statement needs to be grouped (for summing or counting, works much like SQL GROUP BY) use this field to specify the group, as an array, order matters.  This works in tandem with the aggregation function to produce results.  Just like in an SQL GROUP BY each column requires a function to aggregate it's result.

value_column:

The column in the dataset to run the aggregation function on.  If you are not sure what the column name will be, use SELECT column AS some_unique_name. "some_unique_name" can be modified to gather the information requiredthen be used as the value column

aggregation_function:

PASSTHROUGH => 0, COUNT => 1, SUM => 2, MAX => 3, MIN => 4, AVG => 5

This function should be run on each entry in the "group" to produce one row.  The value_column will be read and have this function run on it, unless PASSTHROUGH or COUNT are used. For PASSTHROUGH no grouping or aggregation are done.  For COUNT the number of rows in each group is tallied.  both PASSTHROUGH or COUNT ignore the value_column setting.

lineType:

column is most likely, other options are available, open the chart creator (go to charts and click new chart), there is a drop down with the options.

name:

this is of little consequence, functionally not used at all right now but a way for you to name the dataset for later recognition.

type:

must be sql_query for the above parameters to work.

Back to the chart

The name is the unique name that identifies this chart when using it elsewhere (like creating a dashboard).  What is type for?

type

         "type" : "non-time-chart"

         "type" : "graph"

2 options exist for this.  "non-time-chart" means the data will not be an "over time" graph, but a snapshot of the data at a specific time.  This is the most likely candidate for an SQL chart.  "graph" is a data over time view, the time base is in unix epoc, the SQL query must return the time column in this format and "time_column" must be specified in the dataset telling it what the column is that holds the time value.