SQL Dashboard
SQL Dashboard
The Cyfe SQL dashboard widget lets you create custom dashboards using data from your MySQL 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.
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 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 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 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 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).
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”).
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”.
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”.
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.
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”).
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”.
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”.
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”.
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.