× Go Premium Support Blog Login Sign Up

SQL Dashboard

The SQL dashboard widget lets you create custom dashboards using data from your MySQL and Microsoft SQL databases.

How It Works

0 1

Configure a new custom SQL widget inside your dashboard and add your database account to it by clicking the Add Account button.

0 2

Select the type of chart you want to use with your widget (e.g. line, area, table) and a refresh rate to specify how often you want us to fetch new data (e.g. every 5 mins).

0 3

Enter the SQL query you want to use with your widget and click the Save Widget button. Make sure your query's resulting data format matches the format of the chart type you selected in step 2 (see below for data format examples).

SQL Examples

Let's walk through a few examples to get you started. We'll use this database table named "users" as a basis for our SQL queries and the MySQL syntax. You can adapt these examples to fit your specific use cases as needed.

uid full_name gender revenue registered
1001 John Doe Male 372.29 2017-01-13 10:21:47
1002 Adrian Miller Female 473.48 2017-01-15 23:34:53
1003 Tim Taylor Male 952.78 2017-01-16 07:56:29
1004 Bill Simpson Male 173.40 2017-01-16 16:42:41
1005 Mona Junior Female 821.64 2017-01-20 03:20:12
Note: The best way to troubleshoot your SQL queries is by using our "Table" chart type. This will allow you to easily see the resulting data format for your query before applying it to a graph (e.g. line, area, gauge).

Example 1: Line Chart

This example shows you how to write a SQL query that displays the number of registered users in our database table over time in a line chart. We're going to use "Date" as the first header label to take advantage of the historical data feature in your dashboard (i.e. this will allow you to change time periods using your dashboard date picker).

Example

SELECT DATE_FORMAT(`registered`, "%Y%m%d") AS `Date`, COUNT(*) AS `Users` FROM `users` GROUP BY `Date`
 
Date Users
20170727 1
20170729 1
20170730 2
20170803 1
 

Example 2: Line + Column Charts

This example shows you how to write a SQL query that displays two metrics in a single widget, the number of registered users in a line chart and the amount of revenue generated in a column chart over time. To do this we'll append the optional "Type" parameter to the end of our query using the UNION syntax. Additionally, we'll specify that the revenue metric is currency based by appending "($)" to the end of the revenue header label.

Example

SELECT DATE_FORMAT(`registered`, "%Y%m%d") AS `Date`, COUNT(*) AS `Users`, SUM(`revenue`) AS `Revenue($)` FROM `users` GROUP BY `Date` UNION SELECT 'Type', 'line', 'column'
 
Date Users Revenue($)
20170727 1 372.29
20170729 1 473.48
20170730 2 1126.18
20170803 1 821.64
Type line column

Example 3: List Chart

This example shows you how to write a SQL query that displays a leaderboard of top users by amount of revenue generated. We'll append the optional "Color" parameter to the end of our query using the UNION syntax along with a hexadecimal color code to use with our chart (this replaces the default color the widget would normally use).

Example

SELECT * FROM (SELECT `full_name` AS `Name`, `revenue` AS `Revenue($)` FROM `users` ORDER BY `revenue` DESC) as `tb1` UNION SELECT 'Color', '#009dee'
 
Name Revenue($)
Tim Taylor 952.78
Mona Junior 821.64
Adrian Miller 473.48
John Doe 372.29
Bill Simpson 173.40
Color #009dee

Example 4: Pie Chart

This example shows you how to write a SQL query that displays the total number of males and females in our database table in a pie chart.

Example

SELECT (SELECT COUNT(*) FROM `users` WHERE `gender`='Male') AS 'Male', (SELECT COUNT(*) FROM `users` WHERE `gender`='Female') AS 'Female'
 
Male Female
3 2

Example 5: Gauge Chart

This example shows you how to write a SQL query that displays the total revenue generated against a target value in a gauge chart. We'll also take advantage of a special feature called "Merge Tags" by adding the {date_start} and {date_end} merge tags directly to our query. These tags will be automatically replaced by the respective dates in YYYYMMDD format when your query is executed based on the selected time period in your dashboard (i.e. the data automatically updates when you change time periods using your dashboard date picker).

Example

SELECT SUM(`revenue`) as `Revenue($)`, '5000.00' as `Target` FROM `users` WHERE `registered`>='{date_start}' AND `registered`<'{date_end}'
 
Revenue($) Target
2793.59 5000.00

Chart Types

As of today, the following chart types are available for you to use.

Line

Spline

Area

Area-Spline

Stacked Area

Column

Stacked Column

Stacked Percentage Column

Bar

Stacked Bar

These charts are used to show trends and performance over a period of time.

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your metrics. You can also specify the type of value your labels will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label. The first value in this row must always be "Date" if you would like to take advantage of historical data features like data archival and date range selections.

Data

Required. Starting from the second row, append a list of values for your metrics starting with the date (YYYYMMDD) as the first value in each row.

Colors

You can use your own color scheme for your metrics by appending a row of hexadecimal color codes to the end of your list. The first value in this row must always be "Color".

Types

You can specify the type of chart you want to see for each individual metric by appending a row of chart types to the end of your list. The first value in this row must always be "Type". By default all metrics will use the chart type selected in the widget configuration screen.

Cumulative

Use this row to indicate whether you would like to see cumulate values for particlar metrics in the widget header. The first value in this row must always be "Cumulative". Use the values "0" or "1" to indicate which metric to cumulate values for (e.g. "Cumulative,1,0,0,0").

Average

Use this row to indicate whether to display averages for particular metrics in the widget header. The first value in this row must always be "Average". Use the values "0" or "1" to indicate which metric to average (e.g. "Average,1,0,0,0").

Total

If you use "Date" as the first column label in your data, by default the widget will automatically calculate the averages for percentage based metrics or the sums for all other metrics based on the current time period and display them in the widget header. This row enables you to overwrite these values in the widget header. The first value in this row must always be "Total" (e.g. "Total,542,23,42,49").

Comparison

If you use "Date" as the first column label in your data, by default the widget will automatically calculate percent changes for each metric by comparing the current time period against the last based on the current time period and display it in the widget header. This row enables you to overwrite these values in the widget header. The first value in this row must always be "Comparison" (e.g. "Comparison,-10,50,42,0").

Reverse

Use this row to indicate whether high values are good or bad for particlar metrics (e.g. high "unlikes" count on Facebook is bad). This row is used to caclulate percent differences (comparisons) in the widget header. The first value in this row must always be "Reverse". Use the values "0" or "1" to indicate which metric to reverse (e.g. "Reverse,1,0,0,0").

ReverseGraph

Use this row to indicate whether you want to display a graph upside down (e.g. when displaying ranking information). The first value in this row must always be "ReverseGraph". Use the values "0" or "1" to indicate which metric to reverse the graph for (e.g. "ReverseGraph,1,0,0,0").

YAxis

By default each metric you add to the widget falls on its own y-axis. This row enables you to use a single y-axis across all your metrics in the widget thereby syncing the data. The first value in this row must always be "YAxis". Use the value "0" for all metrics to sync them (e.g. "YAxis,0,0,0,0").

YAxisMin

The minimum value of the y-axis. By default this value is "0" for each metric. The first value in this row must always be "YAxisMin" (e.g. "YAxisMin,0,20,100,0").

YAxisMax

The maximum value of the y-axis. By default this value is automatically calculated for each metric. The first value in this row must always be "YAxisMax" (e.g. "YAxisMax,1000,2000,500,600").

YAxisShow

By default all y-axes are hidden. This row enables you to turn on the y-axis for a particlar metric. The first value in this row must always be "YAxisShow". Use the values "0" or "1" to indicate which metric's y-axis to show (e.g. "YAxisShow,1,0,0,0").

LabelShow

By default all data labels are hidden. This row enables you to turn on the data labels for a particlar metric. The first value in this row must always be "LabelShow". Use the values "0" or "1" to indicate which metric's data labels to show (e.g. "LabelShow,1,0,0,0").

Example

Date Revenue($) Users Activity Level(%)
20170731 29 82 10
20170801 87 38 82
20170802 30 63 48
20170803 70 65 41
20170804 12 52 14
20170805 78 41 36
20170806 10 23 51
20170807 71 24 86
Color #52ff7f #ff7e0e #9d8cf9
Type area line line

Pie

Donut

These charts are used to show the breakdown of data into its constituents, i.e. parts of a whole.

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your metrics. You can also specify the type of value your labels will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label.

Data

Required. In the second row, append a list of values for your metric.

Colors

You can use your own color scheme for your metric values by appending a row of hexadecimal color codes to the end of your list. The first value in this row must always be "Color".

Example

Plan 1 Plan 2 Plan 3
13 53 34
Color,#ff7f00 #00cb13 #ee0000

List

This chart is used to display information in list format.

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your metrics. You can also specify the type of value your labels will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label.

Data

Required. Starting from the second row, append a list of values for your metric.

Colors

You can use your own color scheme for your metric values by appending a row of hexadecimal color codes to the end of your list. The first value in this row must always be "Color".

Example

Sales Rep Revenue($)
Jane Doe 100132
Crystal Smith 52035
Jack Carter 10342
Mona Junior 5413
Homer Simpson 100
Color #ff7f00

Funnel

This chart is used to display information in a funnel.

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your metrics. You can also specify the type of value your labels will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label.

Data

Required. Starting from the second row, append a list of values for your metric.

Example

Type Count
Visitors 135
Leads 110
Customers 103
Repeat Customers 100

Table

This chart is used to display information in table format.

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your metrics. You can also specify the type of value your labels will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label.

Data

Required. Starting from the second row, append a list of values for your metric.

Example

Sales Rep Revenue($) Sales
Jane Doe 100132 213
Crystal Smith 52035 102
Jack Carter 10342 54
Mona Junior 5413 21
Homer Simpson 100 1

Gauge

This chart is used to display information in a gauge.

Data Format

Labels

Required. In the first row of your data, you must specify what label you would like to use for your metric. You can also specify the type of value your label will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label.

Data

Required. In the second row, append the current and target values for your metric.

Colors

You can use your own color scheme for your metric by appending a hexadecimal color code to the end of your list. The first value in this row must always be "Color".

LabelShow

By default the target data label is hidden. This row enables you to turn it on for your metric. The first value in this row must always be "LabelShow". Use the values "0" or "1" to indicate whether to show the target data label (e.g. "LabelShow,1").

Example

Revenue($) Target
600000 1000000
 

Number

This chart is used to display information as a simple number.

Data Format

Labels

Required. In the first row of your data, you must specify what label you would like to use for your metric. You can also specify the type of value your label will represent (i.e. dollar amount, percentage, time) by appending "($)", "(%)", or "(:)" at the end of the label.

Data

Required. In the second row, append the value you want to display for your metric.

Colors

You can use your own color scheme for your metric by appending a hexadecimal color code to the end of your list. The first value in this row must always be "Color".

Example

Revenue($)
700000

Cohort

This chart is used to display behaviors of a group of subjects over a time span (e.g. customer retention rates).

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your metric.

Data

Required. Starting from the second row, append a list of values for your metric.

Colors

You can use your own color scheme for your metric by appending a hexadecimal color code to the end of your list. The first value in this row must always be "Color".

Example

Month Sign Ups 1 2 3 4 5 6
May 2017 1504 1203 984 504 201 145 45
Jun 2017 1640 1540 1423 1354 1245 1198 1046
Jul 2017 2640 2410 2012 1874 1693 1420 1013
Aug 2017 2154 2045 1842 1674 1425 1204 1055
Sep 2017 1355 984 503 354 104 24 1
Color #c96a18

Gantt

This chart is used to display project timelines over a time span.

Data Format

Labels

Required. In the first row of your data, you must specify what labels you would like to use for your projects.

Data

Required. Starting from the second row, append a list of projects along with their start and end dates in YYYYMMDD format.

Colors

You can use your own color scheme for your projects by appending hexadecimal color codes to the end of your list. The first value in this row must always be "Color".

Example

Event Start Date End Date
Marketing Campaign 20150102 20150108
Sales Webinar 20141228 20150106
Staff Meeting 20141217 20141227
Company Retreat 20141110 20141230

Merge Tags

You can add the {date_start} and {date_end} merge tags directly to your query (e.g. SELECT full_name FROM users WHERE registered>='{date_start}' AND registered<'{date_end}'). The merge tags will be automatically replaced by the respective dates in YYYYMMDD format when your query is executed based on the selected time period in your dashboard. You can also use the {group_by} merge tag which will be replaced by the values "day", "week", or "month". You can use these tags to generate dynamic content that respects the dashboard date range feature.

Stop wasting countless hours tracking your data!