In all other reports you show the data the way it was entered: each questionnaire result is shown as it was filled in. Sometimes that is not enough. You may want to see a total, a count, the latest date, or a value that combines answers from different questionnaires — for every record, calculated automatically. For that you use the Analytics designer.
Example: field staff fill in a Deliveries questionnaire when stock is delivered and a Sales questionnaire when stock is sold. You want to see the current stock per shop, that is the total delivered minus the total sold. The standard reports cannot do this: they show the individual deliveries and sales as they were entered, but they cannot combine two questionnaires or calculate the difference per shop.
In the Analytics designer you build this in three steps: a step that sums all deliveries per shop, a step that sums all sales per shop, and an expression Deliveries minus Sales that calculates the current stock. When data is added or changed, the report always shows the up-to-date result.
A more advanced example: mobile users fill in a Training questionnaire each time a course is given, and an Assessment questionnaire each time a participant is assessed afterwards. Management wants to know the percentage of assessments done after the latest training for every location, and how that compares to all assessments. This cannot be done in the standard reports: you need a date from one questionnaire to filter results from another, and then combine three values into one calculated percentage.
In the Analytics designer you build this in five parts:
| Step 1: lasttraining | scalar step, survey Training, value question Training date, business date Training date, agg max — gives the date of the latest training per location. |
| Step 2: aftertraining | scalar step, survey Assessment, value question Score, business date Date, agg count, filter: Date > step lasttraining — counts only the assessments done after the latest training. |
| Step 3: allassessments | scalar step, survey Assessment, value question Score, business date Date, agg count — counts all assessments, without a filter. |
| Expression: pct | formula {step:aftertraining} / {step:allassessments} * 100 — the percentage of assessments done after the latest training. |
| Display columns | Location and the other registration questions, so each row shows one location with its latest training date, the two counts and the percentage. |
The designer page
| 1 Display columns | Registration questions shown as columns in the report, for example Company, Location or Installation date. Dropdowns connected to a registration table automatically show the text instead of the ID. |
| 2 Table-level filter | Filters on the registration, for example Location = Chipata. Only registrations matching the filter appear in the report. |
| 3 Steps | The data to compute. Each step picks a questionnaire, a question to aggregate and an aggregation. Every step becomes one column in the report. |
| 4 Expressions | Calculated columns based on step results, for example the sum or the maximum of two steps. |
| Run | Executes the report and shows the result in the grid at the bottom of the page. |
| Save design | Saves the report under a name, with a role and an optional management flag. The saved report can be opened under Reports > Analytics by users matching the role. |
| Open | Loads a previously saved design into the designer. |
Steps
A step computes one value per registration. Add a step with the + button on the Steps card. Every step has a unique Step id which is used as the column name and can be referenced by other steps and by expressions.
| Kind | scalar: aggregates a single question over all results. paneldynamic: aggregates a question inside a panel dynamic, grouped by another question in the same panel (for example total time per project). |
| Step id | Unique name for the step, used as the column name. Example: useafter |
| Survey | The questionnaire the data comes from. |
| Value question | The question whose values are aggregated. For paneldynamic this is the Panel value question. |
| Business date question | The date question used to group results by date. For a scalar step on a date question, use the same question for both value and business date. |
| Agg | The aggregation applied to the value question: sum, count, avg, max, min or cumulative_delta (scalar steps only). |
| Filter | Optional conditions on the questionnaire data. The value can be a literal or a reference to an earlier step, see below. |
Referencing an earlier step
In a step filter the value can reference the result of an earlier step instead of a fixed value. This is used to compute values relative to another step, for example the usage after the last maintenance date. Only steps listed before the current step can be referenced.
Example: report the total usage after the last maintenance
| Step 1: lastmaint | kind scalar, survey Maintenance, value question Maintenance date, business date Maintenance date, agg max. This computes the most recent maintenance date per registration. |
| Step 2: useafter | kind scalar, survey Use, value question QTY, business date Date, agg sum, filter: Date > step lastmaint. This sums all usage recorded after the last maintenance date. |
Expressions
Expressions add a calculated column using the results of the steps and the registration questions. Each expression has an id, a title shown as the column header, a result type (number or date) and the formula.
Inside a formula you can use:
| {step:stepid} | Access the result of a step, for example {step:useafter} |
| {registration:question} | Access a registration question, for example {registration:Maintenance interval} |
| today | The current date |
| + - * / ( ) | Arithmetic operators |
| min max coalesce | Functions taking a comma separated list of arguments, for example max({step:a}, {step:b}). coalesce returns the first non-empty argument. |
Use the Insert step and Insert registration question dropdowns above the formula field to insert tokens without typing them. The Insert today button adds the today token.
Examples:
| {step:usage} + {step:install} | Total of two steps |
| max({step:usage}, {step:install}, {step:maint}) | The largest of three step results |
| coalesce({step:useafter}, 0) | Returns 0 when the step has no result |
Saving and sharing the report
| Role | The report is visible under Reports > Analytics for users with this role. |
| Management report | When checked the report is only visible to administrators, data managers and survey designers, regardless of the role. |
Saving a design with an existing name updates that design. Use the Open menu to load, modify and delete saved designs.