Skip to main content

Prepare the data

A good dashboard starts with the form. Question names, the type of each answer and the calculated fields define what the dashboard can show and how accurately. A well-prepared form produces correct indicators from the very first collection, allows several forms to be combined in the same dashboard and avoids rework as the dashboard grows. A form put together in a hurry usually produces charts with confusing names, numbers summed as text and fields that change names along the way.

This page covers everything that should be done before creating the first chart: enabling the integration, naming the questions, checking the type of each answer and keeping in the form the calculations the dashboard will use.


Enable the form integration​

Only forms enabled for integration appear in Advanced Reports.

  1. In Hashdata's main menu, click the Integrations icon.
  2. Select the form and enable the Hashdata Advanced Reports integration. The same form can also have the REST API integration.
  3. Click Settings.
  4. For each question, define the question name. The Auto-fill button suggests names based on the question text. Review the suggestions using the standard in Question name.
  5. Check that every question the dashboard will use is enabled for export to BI. A question that is not exported does not appear in the reports.
  6. Check the Index name in Hashdata Advanced Reports. See Index name.
  7. Save.

From then on, every submitted response is automatically copied to the form's index.

Drafts can also be integrated

A draft form can be integrated, but its structure may change at any time. Renamed or removed questions, or questions whose type changed, change the fields that reach the dashboard. For dashboards in production, integrate published forms.

Integration screen details

The integration screen is also used by the REST API. See the Integrations page for details on this setup.


Index name​

The index is the form's collection base in Advanced Reports. Its name has two parts:

  • Numeric prefix — generated by the system. Do not change it.
  • Description — letters, numbers, underscores (_) and hyphens (-) only.

Example: 21261-personal-data-form.

Choose a short description that identifies the form unambiguously. When several forms will make up the same dashboard, use descriptions with a common part (for example, best-practices-site-a and best-practices-site-b). This lets you combine them in a data source with a wildcard.


Question name​

The question name is the short name that identifies the question in the system. It is the same Field name found under Element attributes in the form editor. In Advanced Reports, it is the field name: it appears in the field list, in filters, in formulas and in automatic chart titles.

RuleRecommendedAvoid
Lowercase letterssite, shiftSite, SHIFT
No accents or special lettersevaluation, regionévaluation, región
Hyphen instead of spacearea-managerarea manager
Prefixes that group fields of the same kindperc-shine, perc-sort, score-shineshine-pct, points2
Fixed number of digits in item listsitem-01 … item-25item1 … item25
Same name for the same question in different formssite in all of themsite in one, business in another
A name that describes the contentchamber-temperatureq17, new-field
Upper and lower case matter

Group and group are different fields in the reports. If a name was created with a capital letter, it must be typed exactly that way in filters and formulas. Standardizing everything in lowercase avoids this confusion.

Rename before you start collecting

Collections already submitted keep the names the questions had when they were sent. If a question is renamed later, older collections keep the old name, and the same question appears as two fields in the dashboard. Define the names before putting the form into use. If you need to rename a form that already has collections, contact Hashdata support about reprocessing the history.

Check that names match the questions​

In long forms with repeated sections (for example, one section per pillar in a 5S audit), a name can easily get out of place: the result of the "Shine" section ends up named perc-set-in-order, and all the following ones shift with it. The dashboard shows numbers, but under the wrong name.

Before integrating, go through the form in order and check, question by question, that the name describes that question. For forms with many sections, build a simple table with three columns: section, question and name.


How each question type reaches the reports​

The question type defines what can be calculated with it. A number can be summed and averaged; text can be grouped, counted and filtered.

Question typeHow it usually arrivesTypical use in the dashboard
Number and calculated fieldNumberSum, average, median, minimum, maximum, last value, ranges (intervals).
Single choiceText, with the selected optionGroup (for example, by site), count, filter, split bars by category.
Choice with assigned valuesMay arrive as the number assigned to the option. Check in DiscoverSum occurrences (for example, option "Yes" = 1) or average scores.
Multiple choiceA list of all selected optionsCount how many times each option was selected. See Multiple choice.
Free textTextWord search, tag cloud, notes column in tables.
Date and timeDateFilter by period or use as the time field of a data source.
Check the type before building the dashboard

In Discover, the field list shows an icon next to each name: # for numbers, t or k for text, a calendar for dates. A score, points or percentage field that appears as text cannot be averaged or summed. In that case, review the question type in the form. See Explore the data.

Multiple choice​

In a multiple-choice question, one collection can have several options selected. When grouping by that field, each collection is counted once for each selected option. Therefore:

  • The sum of the bars can be greater than the total number of collections.
  • Percentages by option can add up to more than 100%.

This is correct: the chart shows how many collections selected each option. Make it clear in the title, for example: "Risk factors reported (multiple-choice question)".

Options with exact spelling​

Filters and formulas compare the exact option text. "Positive" and "positive" are different values. Before writing filters, check in Discover how each option was recorded.


Calculated fields and tags​

Rules with several conditions are simpler and more reliable when calculated in the form, with a calculated field. The result reaches the index ready, and the chart only needs to sum, count or average it.

NeedIn the form (calculated field)In the dashboard
Compliance percentage of an auditavgByTag('r')*100, or the sum by tag divided by the maximum possibleAverage of the field, by month or by site.
Score of each sectionSum by tag for each section (for example, sumByTag('shine'))One series per section, in the same chart.
Classification with several conditions (for example, "positive" when a test is positive or at least two symptoms are present)Calculated field returning "Yes" or "No"Count or percentage of "Yes".
Age band, temperature band, weight bandCalculated field with the band (optional)Group directly by the band, or use Intervals on the number.
Tags and names are independent

Formulas that use tags (such as sumByTag('r') and avgByTag('r')) do not depend on question names. That is why standardizing names does not break these calculations. Formulas that reference a question name must be reviewed when the name changes.

Percentage scale: 0–100 or 0–1

A calculated field such as avgByTag('r')*100 stores, for example, 85 for 85%. In the chart, display this value with the Number format and the % suffix. The Percent format multiplies the value by 100 and would show 8,500%. See Number formats.


Combine several forms in one dashboard​

When each site, division or region has its own form, each one generates a separate index. To show all of them in the same chart, the indices are combined in a single data source. For this to work:

  1. Use identical question names in all forms. If one form uses site and another uses business, the dashboard treats them as different fields, and the chart doesn't combine the data.
  2. Use the same question types. A field that is a number in one form and text in another cannot be summed across them.
  3. Use the same answer options, with the same spelling.
  4. Identify the source. Add to each form a question with a fixed value (for example, division, with the value "Division A"). The dashboard then shows readable names. Without this question, the source can be identified by the _index field, which holds the technical name of the index. See The _index field.
  5. Give the indices descriptions with a common part, to use a wildcard in the data source.

Transposed index​

Some forms record the same measure in several questions, one per position, item or type. For example, a defect control with one question for each type: defect-01, defect-02 … defect-24. To analyze "how many defects of each type", it is easier to turn these columns into rows. That is what the transposed index, configured in the integration, does.

In a transposed index, each mapped question becomes a record with two fields:

  • a label field, with the item name (for example, defect-type);
  • a value field, with the answer for that item (for example, defect-value).
Collectiondefect-type (label)defect-value (value)
Collection 1Stain2
Collection 1Crack0
Collection 1Foreign body1
Collection 2Stain0
Collection 2Crack3
Collection 2Foreign body0

For a defects-by-type chart:

  • Horizontal axis: the label field, with Top values.
  • Vertical axis: Sum of the value field.
In a transposed index, the count of records is not the count of collections

Each collection becomes one record per mapped item. In a 24-item transposition, one collection generates 24 records. That is why Count of records with the label on the axis shows the same number for every type: it counts rows, not occurrences. Always use Sum of the value field. To count collections, use the form's original index.


Validate before building the dashboard​

  1. Integration active, with the necessary questions enabled for export.
  2. Question names reviewed, in form order, following the standard.
  3. Calculated fields pointing to the correct names and tags.
  4. Complete test collection, submitted and synced.
  5. Discover: the test collection appears in the index, with all fields filled in and the correct type. See Explore the data.
  6. Answer options written down with the exact spelling, for filters.