Appearance
Data tables: your own report
/admin/manage/data-tables: System → Data tables in the menu
Every site has a question the fixed reports were not written for. A corridor with one door counter and two toilets behind it; a count that only means something once two sensors are put together; a log of every time a reading crossed a line, with the value it crossed at. Data tables let you build that report yourself, out of three things the platform already has:
- A data table: a table you define, with a name, typed columns, and how long rows are kept.
- A rule: the trigger. When its condition is met (a PIR trips, a counter passes a line, a temperature crosses a threshold, a button is pressed), its action Add a row to a data table writes a row with the reading and the place.
- A custom report: Reports → Custom Reports lists your tables beside the built-in ones. Sum, average, count and group them like any other.
Rules are the only writer, apart from rows you add by hand to try a report out. A row is one firing of a rule, so "how many times did X happen" is the count of rows, and the reading that made the rule fire is whatever you chose to store.
Who can do it. The page needs the manage-data-tables permission (Admin and Manager as shipped). Pointing a rule at a table needs the rule permissions; reading the result needs Custom Reports.
The page
The page is headed Data tables, Your own tables. A rule adds a row when something happens; Custom Reports count and chart the rows. New table is at the top right, beside How it works, which opens a short explanation with the toilet example.
Four tiles sit under it: Tables, Rows stored, Rows, last 24 h (zero after setting up a rule means it has not fired yet) and Tables with no rule. Click Tables or Tables with no rule to open the matching tab.
Over the list are two tabs, All and No rule yet, and Find a table, which matches the name. The list shows each table's Table name (with its description under it), Columns, Rows, Last row, Filled by (how many rules write to it, or No rule yet) and how long it Keeps rows. Each row has three buttons: Open rows, Edit and Delete.
Click a table to open View: its rows, last row and retention, the rules that fill it, its columns and its Report key (with a copy button), and Open rows, Edit, CSV and a delete button.
1. Make the table
New table opens the New data table panel.
| Field | What to put |
|---|---|
| Name | What one row is a list of: Toilet visits, Cold-room excursions. Shown in Custom Reports |
| Description | What one row means, for whoever reads the report later |
| Keep rows for (days) | The retention, 1 to 1,095 days (three years). 90 by default. Older rows are deleted every night |
| At most (rows) | Optional cap, 100 to 5,000,000. Beyond it the oldest go, whatever their age |
| Columns | Up to 20. Each has a Label, a Key, and a Type. Add column for another |
The Key is what the rows store values under: letters, digits and underscores, starting with a letter, 32 characters at most. It follows the label as you type until you type a key of your own.
| Type | Holds | In a report |
|---|---|---|
number | a figure | a metric: sum, avg, min, max |
text | a label (male / female, open / closed), up to 255 characters | a grouping |
boolean | true or false | a grouping |
datetime | a date and time | a grouping |
zone | a zone, by uid | a grouping that shows the zone's name |
device | a sensor, by MAC | a grouping that shows the sensor's name |
entity | a person or asset, by uid | a grouping that shows the name |
Keep it narrow. One row per thing that happened, with the figure to add up as a number, and what you will want to split it by as text, zone or device. A report groups by at most five things.
Every row also carries, without a column for it: when it was written, the rule that wrote it, and the zone and device the firing was about (for a Label or Person / asset rule, the device that fired it and its zone).
Press Create table. The panel closes and the new table opens in View, which says what to do next. To change it later, press Edit on its row or in View (the panel then says Edit table and the button Save table).
Changing a table later. Add a column any time. A key cannot be renamed, because the rows store their values under it. Remove a column (the × on its row) and the old rows keep the value, but reports stop offering it; the message after saving names the dropped columns.
2. Point a rule at it
Rules → Add Rule. Choose what the rule watches and the condition as usual, then add an action with the channel Add a row to a data table, pick the Table, and fill in one box per column under Columns. Each takes a fixed value (1, male) or a placeholder. A column left blank is stored empty. (No table yet? The action says so and links to this page.)
| Placeholder | Gives |
|---|---|
{{value}} | the reading the rule's first condition looked at |
{{people_count}}, {{temperature}}, {{enter_count}} … | any metric value, by name |
{{occupied}}, {{illuminance}}, any decoded field by name | the sensor's own fields, for a Sensor reading (any field) rule |
{{rule_name}} | the rule's name |
{{zone.name}} · {{zone_uid}} | the zone the rule watches (or the device's zone) |
{{device.name}} · {{device_mac}} | the device the rule watches; for a Label or Person / asset rule, the device that fired it |
{{venue.name}} · {{venue_uid}} | the venue |
{{now}} | when it fired, as a UTC timestamp |
{{today}} · {{time}} | the date, and the clock time (HH:MM), on your organisation's clock |
Values are converted to the column's type on the way in: {{temperature}} into a number column is a number a report can average; male into a number column is empty. A zone column holds the zone's uid, which the report shows by name: write {{zone_uid}}. A zone's exact name ({{zone.name}}) is turned into its uid as the row is added, so rules written that way still group correctly; a name two zones share, or a typo, is kept as typed and shows as it is.
Set repeat at most every to match the thing. A rule fires at most once per that interval, and a condition that stays true fires again as soon as it allows. For "one row per visit" use an edge (Becomes) with the shortest interval, 30 seconds; for "one row per hour it was over the line", a level with 1 hour. See Rules on any sensor reading. (The table form's help still calls this setting Cooldown.)
3. Check it is filling
Open a table's rows: the Open rows button on its row in the list, or Open rows in View. The rows page shows, newest first, Recorded (on your organisation's clock), each of your columns, Zone, Device and Source (rule with the rule's name, or manual). The newest rows are shown; the CSV has all of them.
- Add a row by hand, at the top, writes one row marked manual: the quickest way to see a report work before the rule has fired. Recorded at is optional (blank = now). Press Add row.
- Prune now applies the retention immediately instead of waiting for the night.
- The × on a row deletes it.
- CSV downloads the rows. Rules and Custom Reports jump to those pages; Edit opens the table's form; All tables goes back.
4. Report on it
Reports → Custom Reports → New Report. Under Source Table, the group Your data tables lists yours. Number columns are the metrics, with Count (Records) for the number of rows; the other columns are groupings, alongside Date and Hour (on your clock), Zone, Device MAC, Person / asset and Rule. Filter, chart, save and export as for a built-in table (the Location and Venue boxes on the run bar do not apply to a data table; use a filter): Building a custom report covers the builder.
The toilet example
The Level 2 toilets at Northwind Tower have a door counter on the corridor and two toilets behind it, male and female. The counter says how many went in; a PIR in the male toilet says how many of those went left. Aisha Rahman wants to know which side to send Siti Nur to first in the morning rush.
- Table
Toilet visits:visitsnumber ·sidetext ·toiletzone. - Rule Male toilet visit: watch the male toilet's PIR (or the male toilet zone); condition Sensor reading (any field) → field
occupiedBecomes (changes to)1; repeat at most every 30 sec; action Add a row to a data table →visits = 1,side = male,toilet = {{zone_uid}}. An edge fires once per change, so one visit is one row however many packets the PIR sends while someone is inside. - Rule Toilet corridor entry: watch the door counter; condition Sensor reading (any field) →
entryChanges (any change); repeat at most every 30 sec (an edge is checked on every packet; Greater than 0 is a level, checked at most once a minute per sensor, and would miss entries); action →visits = {{value}},side = all,toilet = {{zone_uid}}. - Report:
sum visitsbysideand Hour, bar chart. Female is all minus male, read off the chart or worked out in the exported sheet.
What the numbers count: a PIR trigger is "someone went in", not a head count. Two people entering together are one trigger, and the sensor's own hold-off decides how soon it sees "empty" again and can trigger anew. With a 30-second interval, two visits less than 30 seconds apart are one row. The corridor counter counts crossings, so someone who goes in, comes out for a towel and goes back in is two.
A second, shorter one: cold-room excursions. Table temperature number · room zone · sensor device. A rule watching the cold room, Temperature greater than 8, repeat at most every 1 hour, writes the reading each hour it is over. The report shows max temperature by room and Date.
Or ask OAIA
With OAIA on, describe the report you want in the chat, with the sensors you have and what you want counted. It lays out the table, the rule and the report for your case, then proposes to build all three in one go. Click Apply; with Autopilot in Act and the Configure tier on, it is built as you ask. See OAIA.
Questions and problems
No rows appear. The rule has not fired: check Fired and Last Fired on the Rules list, or its rows on Monitor → Live Monitor → Rules, where the Add a row action shows sent or failed. A rule whose check has no value for what it watches never fires. Or its action points at a table that has since been deleted; deleting a table deletes its rows (the Delete table dialog warns which rules still write to it), and a rule still pointing at it adds nothing until it is edited.
Rows, but the number is empty. The template put text into a number column ({{zone.name}} where {{value}} was meant), or the placeholder names a metric the rule has no value for.
Far more rows than events. Repeat at most every is shorter than the thing lasts, so one long presence became a row a minute. Lengthen it, or use Becomes.
Fewer rows than events. Two events inside one repeat at most every are one row; 30 seconds is the shortest.
The report shows uids, not names. The column is text; make it zone, device or entity and the report names it. Existing rows keep their values.
A key turns red: "is used by the built-in reports". Keys such as male, female, count, total, type, value or zone_uid are taken by the platform's own reports; the form suggests another, such as male_visits.
The table is not offered in Custom Reports. The Custom Reports page was open before the table was made: reload it.
The table stopped growing. Check At most (rows): beyond the cap the oldest rows go every night, so the count stays flat by design.