Calculated fields and calculated columns
Summary A Calculated Field works out a number from other answers on a form, such as a total cost from a quantity and a unit price, while the form is filled in. A calculated column does the same in a…
Check yourself
Vérification de votre connexion à PaT…
How To
Sur cette page
- Adding a calculated field to a form
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5
- Building the formula in the Calculation Builder
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5
- Step 6
- Step 7
- Step 8
- Step 9
- Step 10
- Step 11
- What a formula can use
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5
- Step 6
- Step 7
- Step 8
- Step 9
- What people filling in the form see
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5
- Step 6
- Step 7
- Step 8
- Calculated columns in a UDF
- Step 1
- Step 2
- Step 3
- Step 4
- Calculated columns on a grouped report
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5
- Step 6
- Step 7
- Step 8
- Step 9
- Step 10
- Step 11
- Step 12
- Step 13
- Choosing a calculated column's decimal places
- Step 1
- Step 2
- Step 3
- Step 4
- Step 5
- Step 6
- Step 7
- Purpose / Intent of the Module
- Header One
Summary A Calculated Field works out a number from other answers on a form, such as a total cost from a quantity and a unit price, while the form is filled in. A calculated column does the same in a UDF, the data behind your grids and dashboards: on a grouped report it works from the figures the grid shows for each group, as a spreadsheet would, and you choose how many decimal places it is rounded to. This lesson is for the people who build forms in the SD Form Builder and set up UDFs in the SD Endpoint Manager.
Adding a calculated field to a form
Step 1
A calculated field works out a number from other answers on the same form, such as a total cost from a quantity and a unit price. The person who builds the form adds it in the SD Form Builder: open the SD, click Advanced, then click SD Form Builder in the menu on the left.
Step 2
First add the fields the formula will use, such as two Text (Single line) fields called Quantity and Unit Price. Give each field its own Title.
Step 3
On the left of the form builder, open the drop-down list under Controls and choose Common.
Step 4
Calculated Field is the last element in the Common list. If you cannot see it, click Next under the list.
Step 5
Drag Calculated Field onto the Canvas. Until it has a formula, it reads Expression: No expression set. Its result box stays empty in the form builder: the result is only worked out when the form is filled in.
Building the formula in the Calculation Builder
Step 1
Click the calculated field's edit button, the pencil at its top right. The Edit form field Calculated Field window opens.
Step 2
On the General tab, type the name people will see in Title, such as Total cost.
Step 3
Click the abacus button at the right end of the Expression Value box: pointing at it shows Open Calculation Builder. The Calculation Builder opens.
Step 4
Under Available Fields, click the first field the formula uses, such as Quantity. PaT adds it to the Expression box by its reference, between curly brackets.
Step 5
To find a field, type part of its name in Search fields. Each field in the list shows its title and its reference, and a field inside a Field Group also shows the group's title.
Step 6
Under Operators, click the operator you need, such as *. The buttons are + to add, - to subtract, * to multiply and / to divide. The ( and ) buttons put part of the formula in brackets, so it is worked out first.
Step 7
Click the next field, such as Unit Price. The Expression box now holds both references with * between them, and Preview, below the list, shows the formula as it stands.
Step 8
You can also type in the Expression box, for example a number such as 100. Clear, beside the operators, empties the box, and Cancel closes the builder without keeping your changes.
Step 9
Click Save Expression, at the bottom right of the Calculation Builder. The builder closes, and the formula appears in Expression Value.
Step 10
Click Save and Close Calculated Field, at the bottom right of the window. On the canvas, Expression: now shows your formula.
Step 11
If the Canvas heading says (Unsaved changes), click Save Form Now at the top of the canvas to keep the new field.
What a formula can use
Step 1
Available Fields lists only the fields a formula can use: Text (Single line), Dropdown (Single selection), Slide Bar, Star Rating and other calculated fields.
Step 2
A formula can use another calculated field, so one result can build on another, such as a subtotal plus freight. Available Fields leaves out the calculated field you are editing, so it cannot be added to its own formula.
Step 3
Use only field references, numbers, the four operators and brackets. Anything else, such as a word or a percent sign, makes the result read Invalid expression.
Step 4
For a percentage, divide and then multiply by 100: put the division in brackets, click *, then type 100.
Step 5
In the fields a formula uses, people should type plain numbers, such as 1200 or 12.5. PaT also reads 1,200 as 1200 and $12 as 12. An answer such as abc or 12% does not count as 0: the result shows a message instead.
Step 6
A Dropdown (Single selection) counts the option chosen as a number, so give its options labels that are numbers, such as 1, 2.5 and 3. An option that is not a number counts as 0, and an option such as 2.5 counts as 2.5, also on older forms.
Step 7
Slide Bar and Star Rating count the number chosen.
Step 8
A field's reference is made from its Title, in lower case with hyphens for spaces. Under Available Fields, each field shows its Reference. If you change the Title of a field a formula uses, open the Calculation Builder and check that the formula still uses that field's reference: PaT counts a reference it cannot find as 0.
Step 9
Give each field a formula uses its own Title. When the same Title is in two Field Groups, such as after you duplicate a group, each group's calculated field uses the field in its own group.
What people filling in the form see
Step 1
The person filling in the form cannot type in a calculated field: PaT fills it in from their other answers. A calculated field is always read only, so its window has no Read only switch.
Step 2
The result updates about half a second after they stop typing or choosing in a field the formula uses.
Step 3
The result always shows 2 decimal places, such as 12.50. A calculated field on a form has no setting to change this.
Step 4
An empty field counts as 0, so the result can read 0.00 before anything is filled in.
Step 5
If the formula divides by a field that is empty or 0, the result reads Invalid calculation until that field has a number. If an answer the formula uses is not a plain number, the result shows a message instead, such as "abc" is not a plain number.
Step 6
The result is saved with the other answers. Form Response Column Header Text, on the Advanced tab of the field's edit window, sets its column heading in the form responses.
Step 7
To test the formula before you share the form, click Preview at the top left of the form builder. In Preview (Emulated Size), fill in the fields the formula uses and check the result.
Step 8
To show the calculated field only when an earlier answer calls for it, use its Conditional Logic tab, as the lesson Conditional Formatting shows.
Calculated columns in a UDF
Step 1
A calculated column does the same job in a UDF, the data behind your grids and dashboards. The person who sets up the UDF adds it in the SD Endpoint Manager, on an SD's Advanced tab under PaT BI.
Step 2
Find your UDF and click Edit Endpoint on its card. Configure Data Source opens.
Step 3
Click the Columns tab, then click Add New Calculation Column. When a column is already selected, click Add Column at the top of its settings instead.
Step 4
The lesson Configuring a UDF: columns and calculated columns shows how to name the column and build its formula with Edit Expression. The steps below add grouped reports and decimal places.
Calculated columns on a grouped report
Step 1
A grouped report shows one row per group, such as one row per region. To group a UDF, click the Query Builder tab, expand GROUP BY, switch on Enable GROUP BY and choose the column in Group by (Level 1).
Step 2
You can add a second level in Then group by (Level 2). The lesson Configuring a UDF: filters, grouping and endpoint details covers grouping.
Step 3
On the Columns tab, click each number column the report totals. Under Aggregation Type, set Aggregation Method to SUM - Total of all values, so each row shows that group's total.
Step 4
Every other column needs an Aggregation Method too, except the grouped column and calculated columns. Until it has one, its Aggregation Type says Aggregation required.
Step 5
In the preview grid, the grouped column's header is marked L1, and a totalled column's header SUM.
Step 6
A calculated column on a grouped report reads its formula the way a spreadsheet user means it: each column in the formula is the figure the grid shows for that group, not the separate records behind it. That is the group's total for a column on SUM, its average for AVG, its count for COUNT, and its lowest or highest value for MIN or MAX. Columns multiplied together are worked out for each record first, then added up.
Step 7
For example, group by Region and set Sales and Target to SUM. In the calculated column's Calculation Builder, click Sales, click /, click Target, click *, then type 100. Each region's row shows its total sales as a percentage of its total target.
Step 8
Divisions and percentages work as written, with no special functions such as SUM in the formula.
Step 9
A calculated column cannot be grouped and has no Aggregation Type of its own: its Group By setting says GROUP BY is disabled for calculated columns. PaT works it out from the figures the grid shows for each group.
Step 10
The Query Builder's Group by (Level 1) and Then group by (Level 2) lists leave out calculated columns, so you group by an ordinary column.
Step 11
If a column the formula uses is not typed as Number, Expression Builder warns you. Click that column and set its Data Type to Number for clean results: a value that is not a number comes through blank.
Step 12
Click Apply to Data to save your changes and see the grouped rows with the calculated column.
Step 13
If you switch Enable GROUP BY off again, every column stays in the grid, including the totalled columns and the calculated columns.
Choosing a calculated column's decimal places
Step 1
On the Columns tab, expand Column Order and Visibility and click the calculated column, or click its header in the preview.
Step 2
Scroll down to Decimal Places, below Group By. It shows for calculated columns and for Number columns.
Step 3
Type how many decimal places to show, from 0 to 10. A new column starts at 2, and a number above 10 is set to 10.
Step 4
For example, in the preview a region at 87.46 percent of target shows 87.5 with 1 decimal place, and 87 with 0.
Step 5
The setting applies when the formula's result is a number. A text result, such as one made with SPLIT, shows as it is.
Step 6
Click Apply to Data to save the setting. The preview in the window shows the column with that many decimal places.
Step 7
Grids in forms and dashboards built on the UDF show the column with that many decimal places too, so with 0, 4 shows as 4, not 4.00.
Purpose / Intent of the Module
A brief description of what this module is about
Header One
Rate this page
Checking your PaT sign-in…