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

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

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

Optional 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

Colors,#ffc15a, #000000

Cumulative,1,0,0,0

Average,1,0,0,0

Total,542,23,42,49

Comparison,-10,50,42,0

Reverse,1,0,0,0

ReverseGraph,1,0,0,0

YAxis,0,0,0,0

YAxisMin,0,20,100,0

YAxisMax,1000,2000,500,600

YAxisShow,1,0,0,0

LabelShow,1,0,0,0

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 13, 53, 34 color,#ff7t00,#00cb13,#ee0000

List:

Result of Query:	Screenshot
Sales Rep, Revenue($) Jane Doe, 100132 Crystal Smith, 52035 Jack Carter, 10342 Mona Junior, 5413 Home Simpson, 100 color,#ff7t00

Funnel:

Result of Query:	Screenshot
Type, Count Visitors, 135 Leads, 110 Customers, 103 Repeat Customers, 100

Table:

Result of Query:	Screenshot
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:

Result of Query:	Screenshot
Revenue($),Target 600000,1000000

Number:

Result of Query	Screenshot
Revenue($) 700000

Cohort:

Result of Query	Screenshot
Month,Sign Ups,1,2,3,4,5,6 May 2016,1504,1203,984,504,201,145,45 Jun 2016,1640,1540,1423,1354,1245,1198,1046 Jul 2016,2640,2410,2012,1874,1693,1420,1013

Gantt:

Result of Query:	Screenshot
Event,Start Date,End Date Marketing Campaign, 20200102, 20200108 Sales Webinar, 20191228, 20200106 Staff Meeting, 20191217, 20191227 Company Retreat, 20191110, 20191230

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.