SQL Dashboard

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

How It Works

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

Step 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).

Step 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.

uidfull_namegenderrevenueregistered
1001John DoeMale372.292016-09-17 10:21:47
1002Adrian MillerFemale473.482016-09-19 23:34:53
1003Tim TaylorMale952.782016-09-20 07:56:29
1004Bill SimpsonMale173.402016-09-20 16:42:41
1005Mona JuniorFemale821.642016-09-24 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).

SELECT DATE_FORMAT(`registered`, "%Y%m%d") AS `Date`, COUNT(*) AS `Users` FROM `users` GROUP BY `Date`
DateUsers
201609171
201609191
201609202
201609241




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.

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'
DateUsersRevenue($)
201609171372.29
201609191473.48
2016092021126.18
201609241821.64
Typelinecolumn


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).

SELECT * FROM (SELECT `full_name` AS `Name`, `revenue` AS `Revenue($)` FROM `users` ORDER BY `revenue` DESC) as `tb1` UNION SELECT 'Color', '#009dee'
NameRevenue($)
Tim Taylor 952.78
Mona Junior821.64
Adrian Miller473.48
John Doe372.29
Bill Simpson173.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.

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







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).

SELECT SUM(`revenue`) as `Revenue($)`, '5000.00' as `Target` FROM `users` WHERE `registered`>='{date_start}' AND `registered`<'{date_end}'
Revenue($)Target
2793.595000.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

You can group as many metrics together as you like into a single widget.

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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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: Optional. 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

DateRevenue($)UsersActivity Level(%)
20160921298210
20160922873882
20160923306348
20160924706541
20160925125214
20160926784136
20160927102351
20160928712486
Color#52ff7f#ff7e0e#9d8cf9
Typearealineline

Pie, Donut

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

Data Format

Each row is a comma separated list of values. You can only display 1 metric in a single widget.

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: Optional. 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 1Plan 2Plan 3
135334
Color,#ff7f00#00cb13#ee0000









List

This chart is used to display information in list format.

Data Format

Each row is a comma separated list of values. You can only display 1 metric in a single widget.

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: Optional. 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 RepRevenue($)
Jane Doe100132
Crystal Smith52035
Jack Carter10342
Mona Junior5413
Homer Simpson100
Color#ff7f00




Funnel

This chart is used to display information in a funnel.

Data Format

Each row is a comma separated list of values. You can only display 1 metric in a single widget.

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

TypeCount
Visitors135
Leads110
Customers103
Repeat Customers100







Table

This chart is used to display information in table format.

Data Format

Each row is a comma separated list of values. You can group as many metrics together as you like into a single widget.

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 RepRevenue($)Sales
Jane Doe100132213
Crystal Smith52035102
Jack Carter1034254
Mona Junior541321
Homer Simpson1001






Gauge

This chart is used to display information in a gauge.

Data Format

Each row is a comma separated list of values. You can only display 1 metric in a single widget.

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: Optional. 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: Optional. 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
6000001000000










Number

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

Data Format

Each row is a comma separated list of values. You can group as many metrics together as you like into a single widget.

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: Optional. 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

Each row is a comma separated list of values. You can only display 1 metric in a single widget.

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: Optional. 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

MonthSign Ups123456
May 20161504120398450420114545
Jun 20161640154014231354124511981046
Jul 20162640241020121874169314201013
Aug 20162154204518421674142512041055
Sep 20161355984503354104241
Color#c96a18





Gantt

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

Data Format

Each row is a comma separated list of values. You can display multiple projects in a single widget.

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: Optional. 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

EventStart DateEnd Date
Marketing Campaign2015010220150108
Sales Webinar2014122820150106
Staff Meeting2014121720141227
Company Retreat2014111020141230






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.

Get Started

To get started, simply sign up for a free Cyfe account or log in to your existing account and click the Add Widget button found at the top of your screen.