SQL

Use the SQL widget to pull in data unavailable via our other widgets. You can query and visualize the data from your MySQL and Microsoft SQL databases.

Overview / How it works

The data you want to visualize must match data formatting rules. Put another way, your query's resulting data format must match the format of the chart type selected. 

Step 1: Configure account / database Cyfe will access

• Correctly configure your database
• Configure Cyfe widget

Step 2: Query database

• Data format + examples to utilizing SQL widget

Step 1: Configure account / database

In configuring the SQL Widget, you will need to input the following information:

• Account Name
• Database Type
• Database Host
• Database Port
• Database Username
• Database Password
• Database Name

Once you created and configured your database, you can perform the following steps to configure your Cyfe widget:

1. Follow the steps for "Adding a Widget."
2. When the widget window appears, select "Configure Widget."
3. Enter the following information:
   • Widget Title: Title you would like displayed for the widget.
   • Database Account: Provides access to the data you want to display.
   • Chart Type: Select how you want the data to display.
   • Refresh Rate: Select how often you want the link to refresh.
   • SQL Query: Make sure your query's resulting data format matches the format of the chart type you selected.
4. Select "Save Widget."

Step 2: Query database

The query's resulting data format must match the format of the chart type selected.  Before walking through a few examples, there are three rules to call-out (additional customization options provided near end of article):

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}’)

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

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

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.