SQL
Use the SQL widget to query and visualize data.
- We support the following Database Types: MySQL, MicrosoftSQL, and PostgreSQL
- We currently do not support MicrosoftSQL database hosted on Azure
SQL: This widget requires end-user to be proficient querying databases with sql syntax.
In This Article:
Overview
Step 1: Configure Database for Cyfe Access
- Create user, and enable Cyfe IP access to your DB
Step 2: Format Query
- Learn how to format query to visualize data from your db
Step 3: Configure Cyfe SQL Widget
- Troubleshooting
Step 1: Configure Database for Cyfe Access
Supported Database Types: MySQL, MicrosoftSQL, and PostgreSQL.
☑️ Create a username with read-only access to your database
☑️ We recommend password is not your production password
☑️ Make sure your Database can be accessed from our IP: 107.20.231.49
🚥Before proceeding, make sure to test by querying directly against your database with new username
Step 2: Query Formatting
☑️ Determine the chart type for your data
☑️ Write query where resulting data format matches Cyfe requirements
☑️ Test the query directly against your database via your new user to confirm formatting
Query Formatting
SQL query language formatting is specific to your database.
First, determine the chart type you want to see in Cyfe.
The query's resulting data format must match the format of the chart type required by Cyfe SQL widget:
- Below is an example Database with example queries
Example: Database Table "Users"
Let’s walk through a few examples to get you started. We’ll use this database table named “Users” as a basis for our example SQL queries and the MySQL syntax. Adapt these examples to fit your specific use cases as needed.
Line Chart
This example shows you how to write a SQL query that displays the number of registered users in our example table ("users") over time in a line chart. We’re going to SELECT “Date” as the first header label to take advantage of the historical data feature in your dashboard (i.e. using your dashboard date picker):
SELECT DATE_FORMAT("registered","%Y%m%d") AS 'Date',
COUNT (*) as 'Users'
FROM 'users'
GROUP BY 'Date'
Result table from query + example screenshot from Cyfe SQL Widget:
Date | Users | Example Screenshot |
20160628 20160630 20160701 20160705 |
1 1 2 1 |
![]() |
Line + Column Chart
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'
Result table from query + example screenshot from Cyfe SQL Widget:
Date | Users | Reveneue ($) | Example Screenshot |
20160628 20160630 20160701 20160705 Type |
1 1 2 1 line |
372.29 473.48 1126.18 821.64 column |
![]() |
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'
Result table from query + example screenshot from Cyfe SQL Widget:
Date | Reveneue ($) | Example Screenshot |
Tim Taylor Mona Junior Adrian Miller John Doe Bill Simpson Color |
952.78 821.64 473.48 372.29 173.40 #009dee |
![]() |
Pie Chart
SELECT (SELECT COUNT(*) FROM 'users' WHERE 'gender'='Male') as 'Male',
(SELECT COUNT(*) FROM 'users' WHERE 'gender'='Female') as 'Female'
Result table from query + example screenshot from Cyfe SQL Widget:
Male | Female | Example Screenshot |
3 | 2 | ![]() |
Gauge Chart
SELECT SUM('revenue') as 'Revenue($)', '5000.00' as 'Target'
FROM 'users'
WHERE 'registered'>='{date_start}' and 'registered'<'{date_end}'
Result table from query + example screenshot from Cyfe SQL Widget:
Revenue | Target | Example Screenshot |
3 | 2 | ![]() |
Important Query Formatting Rules
1. Custom Date Range ("Date")
You'll notice that most of these examples use "Date" as the first header label in the query:
SELECT DATE_FORMAT("registered", "%Y%m%d") AS 'Date' ...
- SELECTing "Date" as the first header label allows you to take advantage of the historical data feature in Cyfe; you can use the date picker in your Cyfe dashboard (i.e. set custom date range):
2. Specify the type of values your labels (table headers) will represent
Appending "($)", "(%)", or "(:)" to the end of each label specifies the type of values your labels represent:
SELECT Sum('reveue') AS 'Revenue($)' FROM ...
3. Merge Tags (optional)
You can add the {date_start} and {date_end} merge tags directly to your query. 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
SELECT full_name
FROM users
WHERE registered>='{date_start}’ AND registered<‘{date_end}’)
Step 3: Configure Cyfe Widget
Add the SQL widget to your dashboard, then click Configure
- Click Add Account
Input the following information:
- Account Name
- Database Type
- Database Host
- Database Port
- Database Username
- Database Password
- Database Name
Troubleshooting
We recommend
✓ Testing the query directly against your database to confirm it works and data is formatted properly
✓ Using the "Table" chart type to troubleshoot. This allows you to easily see resulting data format for your query before applying it to a graph (e.g. line, area, gauge).
Can't Connect or Unable to select database?
Make sure to check your inbound rules; is a firewall setup that would deny access? is the database publicly available? permissions for user setup correctly? As a best practice, we suggest creating a read-only user to be utilized as the login/pass for SQL widgetSQL Query not saving?
There is a limit of 4K characters that you can input. We recommend creating stored procedures to query against. Using stored procedures makes maintaining widgets easier, and decreases query lengthOptional Data Formatting Parameters:
In the accordion below are a list of optional parameters you can append to the end of your query using the UNION syntax
Data Format Parameters:
Optional | Description | |
Labels | No | Required. Specify what labels you would like to use. |
Data | No | Required. Starting from the 2nd row, append list of values for your metrics starting with the date (YYYYMMDD) as the first value in each row |
Colors | Yes |
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” Colors,#ffc15a, #000000
|
color | Yes | ^ if you want to use just one color, |
Cumulative | Yes |
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: Cumulative,1,0,0,0
|
Average | Yes |
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 Average,1,0,0,0
|
Total | Yes |
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” Total,542,23,42,49
|
Comparison | Yes |
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”: Comparison,-10,50,42,0
|
Reverse | Yes |
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 calculate 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 Reverse,1,0,0,0
|
ReverseGraph | Yes |
|
YAxis | Yes |
|
YAxisMin | Yes |
|
YAxisMax | Yes |
|
YAxisShow | Yes |
|
LabelShow | Yes |
|
Additional Chart Type Examples
To utilize other chart types, reference the resulting data format of query's:
Pie/Donut:
Result of Query: | Screenshot |
plan 1, plan 2, plan 3 |
![]() |
List:
Result of Query: | Screenshot |
Sales Rep, Revenue($) |
![]() |
Funnel:
Result of Query: | Screenshot |
Type, Count |
![]() |
Table:
Result of Query: | Screenshot |
Sales Rep,Revenue($),Sales |
![]() |
Gauge:
Result of Query: | Screenshot |
Revenue($),Target |
![]() |
Number:
Result of Query | Screenshot |
Revenue($) |
![]() |
Cohort:
Result of Query | Screenshot |
Month,Sign Ups,1,2,3,4,5,6 |
![]() |
Gantt:
Result of Query: | Screenshot |
Event,Start Date,End Date |
![]() |
SSH Tunnel?
Currently we do not support any kind of SSH access.
The workaround would be to create a read-only user on your database, and login as read-only user when connecting to the database account.
Comments
0 comments
Please sign in to leave a comment.