The table name is the machine name of the Reporting Table you will be using.
The columns in the SELECT statement are the machine names of the Event and User Fields you will be using. Please note that if your Custom SQL query returns NULL or empty string ('') values, it will not be displayed correctly. For more information, see the article on Custom SQL.
Custom SQL Examples
Using variables and running totals
One of the features of using custom SQL when creating widgets is the ability to use variables. The creation and use of variables follows the same structure as that of mySQL. A mySQL Variable is a virtual field that is created in the 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 is an example of supported functions:
SELECT IFNULL(<expr>,0) AS field1, COUNT(DISTINCT om_uid) AS field2, SUM(IF(<expr>,<true>,<false>)) AS field3 FROM <table>
Joining reporting tables
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 / template 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
You can use the filters on the side bar by passing in the following variables:
WHERE $WHERE WHERE $WHERE(<machine_name>,<table alias>) HAVING $HAVING HAVING $HAVING(<machine_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. Multiple $WHERE and $HAVING are possible.
To find a table's machine name, navigate to Data > Reporting Tables
A select statement with variables might look like this:
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)
Note: 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.
And important distinction is between Widget Conditions and Dashboard Filters. The similarity is that both are effectively modifying either the WHERE or HAVING clause of an underlying query. The main difference is that Conditions are applied within a widget itself, and do not effect any other widgets, while a Filter effects all widgets within a dashboard that have the specific data field in their underlying reporting table.
In general, dashboard filters are easier to maintain as they are generally visible at the dashboard level. Widget Conditions can be useful if a specific widget's scope needs to be narrow (i.e. showing only people who have spent more than $50, only people who have gotten past level 5, etc), but as the Condition is not visible unless a user is editing the Widget, it may not be known that there is a Condition.
By default, Omniata recognises numerical-value Data Fields as Measures in Reporting Tables. If you have a numerical Event Field, Custom User Field or Table Field and you want to use it as a Dimension, follow these instructions. The basic assumption is that the Data Field is question is created and ready to be used.
Open the setup for the Reporting Table in question. It can be a new Reporting Table or an old one which you are editing.
Search the Data Field and drag-and-drop it as a Dimension. In this example the Field is called "Tutorial Step Id". Save the Reporting Table. Remember to run a job so that the Reporting Table contains data for the new Data Field.
The Data Field is listed as Measure in the Design Mode. Click the options of the Data Field and select Make Dimension.
Sometimes when using Custom SQL mode, it's desireable to have a numeric value for the x-axis. Examples include levels, tiers, the count of an event occurring, etc. In some cases the widget will not display the information in the desired order, such as displaying numeric levels zero through ten as 0, 1, 10, 2, 3, 4, 5, 6, 7, 8, 9. This is because the system is recognizing the level field as a string, and sorting the field in lexicographic order. To have the numeric field be sorted numerically, simply alter the query from this:
SELECT level, SUM(revenue) AS revenue FROM revenue_table GROUP BY 1 ORDER BY 1 ASC
SELECT CONCAT(level, '') AS level, SUM(revenue) AS revenue FROM revenue_table GROUP BY 1 ORDER BY CAST(level AS UNSIGNED) ASC
Concatenating the level field with an empty string will force it to be a string data type, which will ensure the level field is sorted numerically.
To automate data pull for a specific widget you need to generate a user token and request the CSV and JSON
Generating a User Token
Go to Configurations > Account > Account Settings
Click Generate New Token
Requesting the CSV Data of a Widget
Requesting the JSON Data of a Widget