SQL report task
A SQL Report task enables the easy creation of a responsive chart report from a list of SQL queries.
Report Composition
The report consists of an ordered list of juxtaposed charts placed from left to right with automatic line breaks when necessary.
Each chart features a fixed height and a variable width based on its base width and the current width of the report.
Upon task execution, every chart is dynamically populated with data sourced from a SQL query.
Chart Definition
Title
Chart’s title.
SQL Query
The SQL query used to populate the chart.
The list and format of expected columns will depend on the type of chart to be displayed.
Database name
The name of the database (chosen from the databases referenced in CTFreak) to connect to for executing the SQL query.
Base width
The optional base width of the chart (in pixels).
When this field is empty, the width of the chart will always match the width of the report (thus, preceding and following charts will consistently be displayed on a new line).
When this field is filled in, the chart’s width will be at least equal to the base width (unless the latter proves to be greater than that of the report).
Height
The fixed height of the chart (in pixels).
Chart type
Several types of charts can be used to represent the data returned by your query.
Bar chart & line chart
Expected SQL query format:
SELECT
{dimension 1},
{metric 1} ,
{metric ...},
{metric N}
FROM ...
- x-axis: {dimension 1} values.
- y-axis: {metric x} values with one colored line chart/bar chart per metric field.
Column number to start a 2nd axis from
When this optional field is filled in, a second y-axis (on the right) will be displayed and dedicated to the metrics starting from the {column number}-th column.
Example
SELECT
DATE_TRUNC('day', date_order),
SUM(amount_total) as "Sales ($)",
SUM(previous_amount_total) as "Previous week Sales ($)"
FROM (
SELECT
date_order,
0 as previous_amount_total,
amount_total
FROM sale_order
WHERE date_order >= DATE_TRUNC('week', NOW()) - interval '1 week'
AND date_order < DATE_TRUNC('week', NOW())
UNION ALL
SELECT
date_order + interval '1 week',
amount_total as previous_amount_total,
0 as amount_total
FROM sale_order
WHERE date_order >= DATE_TRUNC('week', NOW()) - interval '2 weeks'
AND date_order < DATE_TRUNC('week', NOW()) - interval '1 week'
) a
GROUP BY 1
ORDER BY 1
The data returned by this SQL query can be represented by either this bar chart:
Or this line chart:
Horizontal bar chart & line chart
Same definition as bar chart and line chart, but with the X and Y axes reversed.
Example
If we use the previous query, the result for the horizontal bar chart is as follows:
And for the horizontal line chart:
Doughnut chart
Expected SQL query format:
SELECT
{dimension 1},
{metric 1} ,
{metric ...},
{metric N}
FROM ...
- One color per dimension value.
- One doughnut per metric field.
Example
SELECT
TO_CHAR(DATE_TRUNC('day', date_order),'Day') as "Week Day",
SUM(amount_total) as "Sales ($)",
SUM(previous_amount_total) as "Previous week Sales ($)"
FROM (
SELECT
date_order,
0 as previous_amount_total,
amount_total
FROM sale_order
WHERE date_order >= DATE_TRUNC('week', NOW()) - interval '1 week'
AND date_order < DATE_TRUNC('week', NOW())
UNION ALL
SELECT
date_order + interval '1 week',
amount_total as previous_amount_total,
0 as amount_total
FROM sale_order
WHERE date_order >= DATE_TRUNC('week', NOW()) - interval '2 weeks'
AND date_order < DATE_TRUNC('week', NOW()) - interval '1 week'
) a
GROUP BY DATE_TRUNC('day', date_order)
ORDER BY DATE_TRUNC('day', date_order)
The data returned by this SQL query can be represented by this doughnut chart:
Column format
In SQL queries, it is possible to specify the expected format of dimension-type columns by adding a suffix to the column name:
_c_hour: The values assigned to this column will be considered as hours._c_day: The values assigned to this column will be considered as days._c_iweek: The values assigned to this column will be considered as weeks (starting on mondays)._c_week: The values assigned to this column will be considered as weeks (starting on sundays)._c_month: The values assigned to this column will be considered as months.
Using project constants
Project constants can be used in SQL queries as query parameter prefixed with :CPC_ (for CtFreak Project Constant).
Here’s an example using a project constant named MIN_REVIEWS:
SELECT
"Popularity",
count(*)
FROM (
SELECT
product_id,
CASE
WHEN count(*) >= :CPC_MIN_REVIEWS THEN 'High'
ELSE 'Low'
END as "Popularity"
FROM reviews
GROUP BY product_id
) a
GROUP BY "Popularity"
If you later decide to increase the minimum number of reviews of a product to consider it popular, you’ll only need to modify the constant value to affect all SQL queries referring to it.
Using task parameters
Like project constants, task parameters can be used in SQL queries as query parameter prefixed with :CTP_ (for CtFreak Task Parameter).
Here’s an example using a date task parameter named START_DATE:
SELECT
DATE_TRUNC('day', date_order),
SUM(amount_total) as "Sales ($)",
SUM(previous_amount_total) as "Previous week Sales ($)"
FROM (
SELECT
date_order,
0 as previous_amount_total,
amount_total
FROM sale_order
WHERE date_order >= CAST(:CTP_START_DATE as date)
AND date_order < DATE_TRUNC('week', NOW())
UNION ALL
SELECT
date_order + interval '1 week',
amount_total as previous_amount_total,
0 as amount_total
FROM sale_order
WHERE date_order >= CAST(:CTP_START_DATE as date) - interval '1 week'
AND date_order < DATE_TRUNC('week', NOW()) - interval '1 week'
) a
GROUP BY 1
ORDER BY 1
Task parameters values are converted according to parameter type into the appropriate SQL data type:
- Checkbox → BOOLEAN
- Date → TEXT (Formatting with the
YYYY-MM-DDpattern) - Selector → TEXT
- Integer → BIGINT
- Text → TEXT