SQL Views

In addition to building visualizations on the dashboard with the user interface, you can craft SQL statements on the query engine that power individual views on the dashboard.

This can be extremely useful for those tricky visualizations that just require that bit of SQL to accomplish.

These views will react to any dashboard and local filters that pertain to the underlying data set the SQL view uses, just like the ones built using the UI!

Usage

After building out your data sets, you can then use these as if they are tables in your SQL views snippets.

For example, say I had a data set named "my_vizzly_data_set", which contained employee data in, I'd be able to write a SQL view such as this;

SELECT
  avg(salary) as "field_one|metric&name=Salary",
  department as "field_two|group&name=Department"
FROM my_vizzly_data_set
GROUP BY 2
ORDER BY 2 ASC

Although this can be built very easily using the dashboard interface, you can see how this can achieve more complex calculations through the use of SQL if you need it!

The example above will create a result whereby field_one contains the average salary that is grouped by field_two - the department.

The as value

The as statement is very important, as it provides properties of the field that define its behavior on the dashboard.

Content before the |

The string before the pipe (|) is the ID of the field. Once set, this should not be changed as it is used by the dashboard to reference this field.

Content after the |

These properties can be changed without breaking the dashboard. They are formatted in the same way as query string parameters. The properties you can control are;

• Is this a metric, or a grouping value?
• The public name for the field to show on the dashboard

"Metric" or "group"

To specify the field as a metric, the parameters must include the keyword metric.

To specify the field as a grouping, the parameters must include the keyword group.

Data types

By default, a metric will be classed as a number and a grouping field will be classed as a string. To change the data type you will need to specify the type property.

For example, you might want to show the average salary over time. This query might look like this;

SELECT
  avg(salary) as "field_one|metric&name=Salary",
  date_trunc('month', payroll_date) as "field_two|group&type=date_time&name=Department"
FROM my_vizzly_data_set
GROUP BY 2
ORDER BY 2 ASC

Note the additional type=date_time in the as statement for the grouping field!