Omniata allows users to write their own SQL with MySQL syntax, which is useful when creating complex queries that include joins, subselects, unions, or other advanced functions. The custom SQL mode is accessed through the Widget Builder by selecting SQL Mode. A basic example would be:
SELECT * FROM <table_name>
The table name is the SQL table name of the Reporting Table you will be using. To find a table name, navigate to Data Model > Tables and look at the column 'Table Name'. Please note that all Reporting Tables have a machine name as well, which is an internal identifier for Omniata. This can be set to be the same as the SQL table name, but by default it will not be. Writing a query that uses the machine name instead of the SQL table name will result in an error.
The columns in the SELECT statement are the System ID names of the Data Fields you wish to use.
Please note that there may be display issues when using custom SQL that generate null or empty string ('') values in the result. If a SQL query is generating values like this, it's best to use a CASE statement to force the null or blank values to be numeric. An example might look like this:
SUM(CASE WHEN ((result = '') OR (result IS NULL)) THEN 1 ELSE 0 END) AS `Total Result`
You can use dashboard filters to modify the data set returned by a Custom SQL widget by including the following:
WHERE $WHERE WHERE $WHERE(<table_name>,<table alias>) HAVING $HAVING HAVING $HAVING(<table_name>,<table alias>)
If, due to sub-queries or joins, the table name does not match the machine-name of the reporting table, then it can be specifically changed using the alternative syntax (see lines 2 and 4 above). Multiple $WHERE and $HAVING tokens are supported. The columns in the select statement must match the machine names of the fields being used in the filter. For example, Activity Date is om_activity_date and the column cannot be aliased as anything else.
SELECT activity_date, users, count(distinct game) as games FROM ( SELECT activity_date, game, SUM(users) AS users FROM inner_table #assume machine name is "inner_table_mn" GROUP BY 1,2 ) AS outer_table WHERE $WHERE(inner_table_mn,outer_table) GROUP BY 1,2 HAVING $HAVING(inner_table_mn,outer_table)
The creation and use of variables follows the same structure as that of MySQL. A MySQL variable is a column field that is created in a query and does not exist in the table. A variable is denoted using the @ symbol with a name to accompany (i.e. @x or @y)
Variables can be used for setting a constant number for use in formulas. A variable works by using the @ symbol and assigning it a name:
SELECT( @y:=0 ) v
The variable here is named y and referenced as @y. We initially set the variable to 0, so any references to @y at this point would result in 0. If we want to update the variable to add a + 1 for each row, we could use the following formula:
@y:=@y+1 AS 'variable'
This will result in a column where @y increments by 1 with each row:
You can also use variables in mySQL to create a running sum, reference a value on a given date or action, or count rows in the table. For example, to create a running total the following statement can be used.
SELECT b.om_activity_date, @x:= b.om_total_revenue + @x FROM (SELECT @x:=0) a, (SELECT om_activity_date, om_total_revenue FROM table GROUP BY 1 ORDER BY 1 DESC) as b GROUP BY 1 ORDER BY 1 DESC
Functions can be used for building widgets. Supported functions include the type: control, numeric, date, time, text search, and cast. Here are some examples of supported functions:
SELECT IFNULL(<expr>,0) AS field1, COUNT(DISTINCT om_uid) AS field2, SUM(IF(<expr>,<true>,<false>)) AS field3 FROM <table>
Joins can be used to join two reporting tables on a shared column of data, such as om_activity_date. Here is a basic example of a join clause:
SELECT a.date, a.users, b.users FROM (SELECT date, users FROM a ) a JOIN (SELECT date, users FROM b) b WHERE a.date = b.date
When joining two different tables the filters may need to point to different field/table names. To tell the filters to use a specific table name we can employ the $WHERE and $HAVING aliases.
SELECT sm.`om_activity_date`, sm.`om_new_users`, cm.`om_new_users` AS `cm_new_users` FROM (SELECT date(a.activity_date) AS `om_activity_date`, SUM(a.`new_users`) AS `om_new_users` FROM `om_day_gmet_eng` a WHERE (a.`project_name` = 'My Game') AND $WHERE(om_day_gmet_eng, a) GROUP BY date(a.activity_date)) AS `sm` LEFT JOIN (SELECT `om_activity_date` AS `om_activity_date`, SUM(IF(om_retained_days = 0, om_users, 0)) AS `om_new_users` FROM `md_mnt1_bb7e` WHERE $WHERE GROUP BY `om_activity_date`) AS `cm` ON sm.`om_activity_date` = cm.`om_activity_date` ORDER BY sm.`om_activity_date` ASC