The Tile settings window defines one dashboard tile: which connection receives which SELECT statement, whether the result is drawn as a number, a chart or a table, how large the tile is and how often it runs. The left side is the tile's definition and the right side is a preview that actually queries with that definition. The dashboard screen the tile sits on is described in Dashboards.
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Type field | Choose the Number (Value), Chart or Table item. |
| 2 | Chart type field | Shown only for charts. Choose the Bars, Column, Line, Area, Pie, Donut or Scatter item. |
| 3 | Name field | The name shown in the tile header. If left empty, the Untitled tile label is shown. |
| 4 | Connection field and schema field | The connection and database/schema this tile queries. A tile with no schema chosen (a new tile, or one made from a connection suggestion) shows Connection default schema in the field and queries the connection's default schema. Changing the connection selects its first schema. |
| 5 | Query field | The tile's SELECT statement. Tables and columns of the chosen schema are autocompleted. A line below states the result shape the chosen type expects. |
| 6 | Run query button | Actually queries with the current definition. If the SQL breaks a rule, the button is disabled and the reason appears next to it. |
| 7 | Display options field | Only the options the chosen type uses, such as the Unit, Decimals and Rows shown fields ('Display options' below). |
| 8 | Size field | Width (out of 12 columns) and Height (rows) items. The smallest and largest sizes for this type are shown next to it. |
| 9 | Schedule field | The Run automatically switch and the interval ('Run schedule' below). |
| 10 | Result table | The first 10 rows of the queried result. Before you query, the Run the query to see results here notice is shown. |
| 11 | Columns field | Decides which column plays which role in a chart. Shown only after a query has revealed the columns ('Column roles' below). |
| 12 | Chart preview button | For chart types, draws the preview result with the same chart as the tile. Once opened, it redraws whenever you change an option. |
- The window's OK button is enabled when the SQL follows the rules and a connection is chosen. You can save without a preview.
- Changing the Chart type field, the Columns field or the Display options field does not query again; the result already fetched is redrawn. A query goes out only when you click the button.
Creating a tile
- Click the + Tile button on the dashboard toolbar. The Add tile window opens.
- Choose a type in the Type field, and for a chart, its kind.
- Choose the connection and schema and write the SELECT statement. Pick the columns to match the result shape hint under the query field.
- Click the Run query button to check the result. For a chart, check its look with the Chart preview button.
- If the tile should run on a schedule, turn on the Run automatically switch.
- Click the OK button. The tile takes the first free slot and runs once.
- The Dashboard button of the SQL editor also opens this window, with the SQL and execution target filled in (Dashboards).
- For an existing tile, the Settings button in its header opens the Tile settings window. If changing the type makes the tile larger, neighboring tiles are pushed and the layout is rearranged.
Rules for tile SQL
A tile runs again and again on a schedule, even while you are not looking. So it accepts less than the SQL editor does. The rules are checked when you save and before every run.
- Only one statement starting with
SELECTorWITHis accepted. Leading comments are fine. Two or more statements are not accepted. - A
WITHfollowed by a write such asINSERT,UPDATEorDELETEis not accepted. Words inside strings can also trip this; rewrite the statement if so. SHOW,DESCRIBEandEXPLAINare not accepted.- The only bind names are
:fromand:to. Other names are not accepted, because there is no value to supply for them.
Runs work like this:
- They run without a session, in auto-commit. A tile never leaves a transaction open.
- Results are received up to 200 rows. Table tiles draw only the rows shown out of those.
- They are not recorded in SQL execution history. In account projects, masked columns are hidden the same way as in the SQL editor.
- A run you clicked that does not finish within 60 seconds, or an automatic run that does not finish within 80% of its interval (no more than 60 seconds), ends with a timeout error.
Result shape
Each type reads the result columns in a fixed way. The rule for the chosen type is also shown in one line under the query field.
| Type | What the SQL should return | Example (Demo Shop) |
|---|---|---|
| Number | With one row and one column, that value. A second column becomes the caption under the value | SELECT COUNT(*) FROM orders |
| Bars, Column, Pie, Donut | First column is the item name, second is the number. Order follows ORDER BY | SELECT status, COUNT(*) FROM orders GROUP BY status |
| Line, Area | First column is time or sequence, second is the number. Use ORDER BY for the axis order | SELECT date(ordered_at) AS day, SUM(total_amount) FROM orders GROUP BY day ORDER BY day |
| Scatter | Both columns numeric. First is x, second is y | SELECT price, stock FROM product |
| Table | Any columns. Draws up to the rows shown | SELECT order_id, status, total_amount FROM orders ORDER BY ordered_at DESC |
- To draw several series, return three columns (item, series, value) or several numeric columns. Only the Bars, Column, Line and Area types can stack series.
- When a chart cannot be drawn, the reason is written instead of the chart: when there is no numeric column, when there are more than 50 series, when a Pie or Donut gets several series, and when there are several numeric columns but repeated item values make the roles ambiguous. The last case is solved with the Columns field.
- When the result has 0 rows, the No rows label is shown.
Column roles
Left empty, the Columns field is decided from the result shape. Choose only when repeated item values make it impossible to tell automatically.
- Charts such as bars and lines have the Label axis field and the Value field, and charts that can stack series also have the Series (legend) field. Pick a category column for the series; picking a numeric column turns its values into series names.
- Scatter has the X axis field and the Y axis field. Left empty, the first two columns are used.
Display options
| Option | Types | Description |
|---|---|---|
| Unit field, Decimals field | Every type except table | The unit after the value and the number of decimals (0–6). A leading space in the unit keeps it apart. Charts use it for axis ticks, tooltips and value labels |
| Rows shown field | Every type except number | The number of rows drawn. Empty means 20, at most 50. Switching to pie or donut fills in 8 if it is empty |
| Stack series box | Bars, Column, Line, Area | Stacks the series when there are several |
| Trend line box | Bars, Column, Line, Area | Shows the direction with one straight line through the values. Drawn only with a single series |
| Show in my timezone box | Every type except scatter | Reads time columns without a time zone in the DB time zone and shows them in yours. Hidden when the result has no time column |
- The display format of times and your time zone are the same settings the SQL editor uses (SQL execution and transactions).
Size
Width is how many of the 12 columns, and height is how many rows (one row is 118px). Each type allows only sizes that can be read.
| Type | Initial size | Smallest | Largest |
|---|---|---|---|
| Number | 3×1 | 2×1 | 12×2 |
| Bars, Column | 4×2 | 3×2 | 12×4 |
| Line, Area | 8×2 | 4×2 | 12×4 |
| Pie, Donut | 4×2 | 3×2 | 6×4 |
| Scatter | 6×2 | 4×2 | 12×4 |
| Table | 8×2 | 4×2 | 12×4 |
- When you change the type and had not touched the size, it moves to the new type's initial size; if you had, it is only fitted between the new type's smallest and largest sizes.
- You can also resize by dragging on the dashboard (Dashboards).
Run schedule
- Turn on the Run automatically switch. Tiles you make yourself start with it off.
- Choose the Dashboard default button to follow the default interval on the dashboard toolbar. Choose the This tile only button to enter an interval in seconds for this tile only.
- Minimum 30s. Runs only while the app is open and this tab is visible.
- If the dashboard's Auto run switch is off, the tile does not run even with its own switch on.
- A tile with the switch off runs only when you click the Run now button or the Refresh all button.
Query period and time zone
When the SQL uses :from or :to, the value of the Period field on the dashboard toolbar is bound. Before you use them, the Use :from and :to to read the dashboard period — the toolbar [Period] value is bound. No other names are accepted. hint is shown under the query field; once you use them, the lines in the picture below appear.
- Hover over the exclamation mark (1) to see the rules.
:tois midnight of the day after the last day picked, so write>= :from AND < :toto cover every day picked. For columns stored as dates only or as text, convert them in the query withCASTor similar. - Turning on the Query in my timezone box (2) sends wall-clock times in your time zone instead of the DB time zone. The tile's footer gets a clock icon. Teammates in another time zone will then query a different window.
- The Period field on the right (3) is used only for this window's preview. The dashboard's period does not change. After the first query, changing the period queries again right away.
- The Queried period (…) label (4) shows the
:fromand:tovalues that will actually be sent and their time zone. When the DB time zone is unknown, the UTC · DB timezone unknown label is shown.
Detaching a tile from a set
Tiles added as a set from the Preset gallery share one query with other tiles. Editing that query in place would change the values of the neighboring tiles too, so the tile is detached from the set before the settings window opens.
- Click the Settings button of a set tile. The Detach from set window appears.
- Click the Detach and edit button. The settings window opens with the tile taking over the set's SQL and interval.
- Clicking the OK button in the settings window saves the detachment. If you close the settings window without saving, the tile stays in the set.
- A detached tile queries on its own, so each cycle has one more query.
- A tile that computed its value from the set result (for example, row count or sum) carries that computation in the Computed value line. It survives query edits, and the Clear button makes the tile use the query result as it is. While a computation is set, the Columns field is hidden.
Things to keep in mind
- The Run query button sends a query to the real database. Do not try heavy queries on a production database.
- Write statements are blocked, but a function called inside a SELECT that changes data cannot be blocked by looking at the statement. Use a read-only database account for production (Safeguards for production databases).
- Results are received only up to 200 rows. Aggregate in SQL; fetching a large table and trimming it in the tile burdens both the database and the app.
- The Cancel button discards what you changed in this window. To delete a saved tile, click the Delete button in its header on the dashboard.
Related screens
- Dashboards: the screen tiles sit on, the period and automatic runs.
- Preset gallery: add ready-made tiles and sets.
- SQL editor: refine the SELECT to send as a tile.
- SQL execution and transactions: how times and time zones are shown.
- Safeguards for production databases: why a read-only account is recommended.