Screens: Dashboards

Tile settings

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.

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

What's on the screen

No.NameDescription
1Type fieldChoose the Number (Value), Chart or Table item.
2Chart type fieldShown only for charts. Choose the Bars, Column, Line, Area, Pie, Donut or Scatter item.
3Name fieldThe name shown in the tile header. If left empty, the Untitled tile label is shown.
4Connection field and schema fieldThe 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.
5Query fieldThe tile's SELECT statement. Tables and columns of the chosen schema are autocompleted. A line below states the result shape the chosen type expects.
6Run query buttonActually queries with the current definition. If the SQL breaks a rule, the button is disabled and the reason appears next to it.
7Display options fieldOnly the options the chosen type uses, such as the Unit, Decimals and Rows shown fields ('Display options' below).
8Size fieldWidth (out of 12 columns) and Height (rows) items. The smallest and largest sizes for this type are shown next to it.
9Schedule fieldThe Run automatically switch and the interval ('Run schedule' below).
10Result tableThe first 10 rows of the queried result. Before you query, the Run the query to see results here notice is shown.
11Columns fieldDecides which column plays which role in a chart. Shown only after a query has revealed the columns ('Column roles' below).
12Chart preview buttonFor 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

  1. Click the + Tile button on the dashboard toolbar. The Add tile window opens.
  2. Choose a type in the Type field, and for a chart, its kind.
  3. Choose the connection and schema and write the SELECT statement. Pick the columns to match the result shape hint under the query field.
  4. Click the Run query button to check the result. For a chart, check its look with the Chart preview button.
  5. If the tile should run on a schedule, turn on the Run automatically switch.
  6. 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 SELECT or WITH is accepted. Leading comments are fine. Two or more statements are not accepted.
  • A WITH followed by a write such as INSERT, UPDATE or DELETE is not accepted. Words inside strings can also trip this; rewrite the statement if so.
  • SHOW, DESCRIBE and EXPLAIN are not accepted.
  • The only bind names are :from and :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.

TypeWhat the SQL should returnExample (Demo Shop)
NumberWith one row and one column, that value. A second column becomes the caption under the valueSELECT COUNT(*) FROM orders
Bars, Column, Pie, DonutFirst column is the item name, second is the number. Order follows ORDER BYSELECT status, COUNT(*) FROM orders GROUP BY status
Line, AreaFirst column is time or sequence, second is the number. Use ORDER BY for the axis orderSELECT date(ordered_at) AS day, SUM(total_amount) FROM orders GROUP BY day ORDER BY day
ScatterBoth columns numeric. First is x, second is ySELECT price, stock FROM product
TableAny columns. Draws up to the rows shownSELECT 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

OptionTypesDescription
Unit field, Decimals fieldEvery type except tableThe 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 fieldEvery type except numberThe number of rows drawn. Empty means 20, at most 50. Switching to pie or donut fills in 8 if it is empty
Stack series boxBars, Column, Line, AreaStacks the series when there are several
Trend line boxBars, Column, Line, AreaShows the direction with one straight line through the values. Drawn only with a single series
Show in my timezone boxEvery type except scatterReads time columns without a time zone in the DB time zone and shows them in yours. Hidden when the result has no time column

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.

TypeInitial sizeSmallestLargest
Number3×12×112×2
Bars, Column4×23×212×4
Line, Area8×24×212×4
Pie, Donut4×23×26×4
Scatter6×24×212×4
Table8×24×212×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

  1. Turn on the Run automatically switch. Tiles you make yourself start with it off.
  2. 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. :to is midnight of the day after the last day picked, so write >= :from AND < :to to cover every day picked. For columns stored as dates only or as text, convert them in the query with CAST or 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 :from and :to values 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.

  1. Click the Settings button of a set tile. The Detach from set window appears.
  2. Click the Detach and edit button. The settings window opens with the tile taking over the set's SQL and interval.
  3. 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.