Skip to main content

Report Editor

Overview​

The report editor is where analysts write the SQL & R to query the data, as well as configure the visualization of the report via a chart type. You can access the report editor by selecting the New Report button on a dashboard or the dashboard overview page, or by choosing Edit from the drop-down menu of a particular report.

Overview Overview

Datasource Selection​

A report is written to run against a particular datasource. To select a datasource for the report, use the drop-down menu above the text input where the SQL queries are written.

image-300 image-300

Almanac​

On the left-hand side of the report editor, the datasource almanac gives an overview of the relations (i.e. tables) available for querying with the selected datasource.

image-300 image-300

By selecting a table in the almanac you can inspect the columns and run some basic, predefined queries.

image-300 image-300

The datasource metadata shown in the almanac is refreshed nightly, so that up-to-date schema information is shown when you make changes to the remote database tables or columns outside of Cluvio.

SQL Editor​

The SQL editor is a text area located right below the datasource selector. It is here that you write the SQL to run against the datasource. In the top-right corner, the SQL editor allows formatting the SQL and enabling R.

SQL Editor SQL Editor

Enabling R will result in a split of the editor window in two parts, one for SQL and one for R.

SQL and R Editor SQL and R Editor

A central concept when authoring SQL for reports in Cluvio is the use of filters via Cluvio filters syntax, a simple to use extension of the SQL syntax and a key to making your reports interactive.

Query Size​

A report's SQL is limited to 1,048,576 UTF-8 characters (1 MB of ASCII text) — it is a count of characters, not of bytes, so text outside ASCII is not penalised for taking more than one byte each. The same limit applies to the R script and to the SQL of filters and SQL alerts. Roughly twenty thousand lines of SQL fit within it. Saving a query that exceeds the limit is rejected, and the editor shows the error on the query itself.

SQL snippets are substituted into the query before it runs, so what reaches your database can be larger than what you wrote. The same limit applies to the query after that substitution, so a query that fits only because its snippets are short can still be refused once they are resolved — see Limits.

Filter values do not count towards it. The values selected for filters are substituted when the query runs, after the limit has been checked, so a multi-select filter carrying thousands of values can take the statement your database receives well past the limit. It is on the SQL you write and the snippets it pulls in, not on the data a viewer selects.

Query Results​

When a SQL query is run, the results are shown right below the SQL editor. How the results are displayed depends on the selected chart type. To select a chart type, click on one of the chart names in the horizontal menu.

Query Results Query Results

The result metadata (i.e. the number and type of the result columns) decides which charts are offered: only those likely to produce a useful visualization are enabled (and are available according to your Cluvio plan). For more information on the available chart types and their configuration options, see Chart Types.

The Results tab is always available and shows the result data in a simple tabular form, together with chart recommendations on the right.

It offers two views of those rows. Table view lists them as a table, paged, with Show all to drop the paging; Record view steps through them one at a time as Record N of M, listing every column of that row down the panel — the readable way to check a result that is thirty columns wide. The Filter rows box narrows what either view shows, without re-running the query.

Query Results Query Results

From the Results tab, you can also inspect the executed SQL, as well as get a query performance analysis via the EXPLAIN Query option, and even download the result data as CSV.

Query Info Query Info

Report History​

Report history in the editor's left icon bar lists every saved revision of the report, what changed in each, and who changed it — with Restore to put one back and Diff to current to compare it against the report as it stands. See Change History for the detail, and for the dashboard-wide view of the same record.

Using R​

Available on
All plans

On top of running SQL, Cluvio also allows you to post-process the SQL results via a custom R script, which makes it possible to do process data in ways which are impractical or even impossible to do with only SQL.

The following example shows how easy it is to add a running total column to the SQL results:

SQL:

SELECT {saletime:week}, SUM(pricepaid) as "revenue"
  FROM sales
  GROUP BY 1
  ORDER BY 1

R:

sql.data %>%
  mutate(running_total=cumsum(revenue))

As seen in the example, the SQL results are made available to the R script in the sql.data variable as a data.frame. The final result of the R script is the result of the last command and is passed to the Cluvio visualization as a data table. Supported R types are:

  • primitive types, date, timestamp
  • vectors of these (will appear as multiple rows of a single column)
  • data.frame

You can optionally name columns in the output as usual in R via the 'names' attribute, e.g. the following will produce a column named "Metric" with 2 rows of data:

res <- c(42, 84)
names(res) <- "Metric"
res

The following libraries are directly available to use in R scripts. Please contact hi@cluvio.com in case you would like to use a package that is not currently supported.

System and I/O functions are not available.

info

Important Restrictions: The R script runs within a constrained environment:

  1. The script has to finish within 1 minute (please contact support with your use-case if you need this limit raised).
  2. The script can use at most ~100MB of computer memory.
  3. Most system functions and functions for accessing the local filesystem are not available, i.e. the script is meant to process the SQL output in a referentially transparent manner.
  4. The output of the R script has to be data (as stated above); there is not much use for generating visuals using R (e.g. using ggplot or shiny) in Cluvio, as the visualization step we apply is the same as with plain SQL results.

Settings​

To fine-tune a report's appearance and behavior, like the data refresh rate, you can access the report settings from the top-right corner of the editor UI.

image-500 image-500

For more details, see Report Settings.