Adding a calculated field to a custom report allows you to feed other columns into a custom calculation, saving you time working out each one. You can use this feature in the Custom Report Writer on the School MIS or MAT MIS.
The example below will walk through adding calculations to reports on attendance and behaviour data, but you can use it on any field you can base filters on such as assessment attainment and progress!
Permissions
To create your own custom reports or edit reports shared with you, you will need the School: General Admin: Export Data permission.
The amount of data you are able to view will also depend on your permissions - see more information on how this works here: Who can access Custom Report Writer reports and fields?
If you need to give someone permission to create reports or report on certain items, you can follow these instructions.
When can I add calculated fields?
You can add calculated fields when creating a report, or when editing a report!
When initially creating a report, you can add the Advanced Calculation/Formula column in Select Columns (Step 3 of 8).
When you're editing a report, you'd just click to add a new column, and then add Advanced Calculation/Formula and click Add Column.
Be sure to select your Data type as either Text, Integer or Decimal (2dp, 1 or rounded number). You may need to click back into the column to select the number of decimal places if you change the data type to decimal.
How to use calculated fields
In the sections below, we'll go through some common calculations you might want to include in a calculated field, and how to add them. Only the following functions are allowed: SUM, IF, IFS, AND, OR, AVERAGE, CONCAT, ROUNDDOWN, ROUNDUP, ISNUMBER, SEARCH, ISTEXT, YEAR, and MONTH.
Calculated fields can use values from any other non-calculated fields, but if based on another calculated field, then this field must be to the left (we calculate them in order from left to right). You can drag and drop the fields in the Report Columns section.
All formulas must start with an equals (=) symbol.
When using brackets, such as when using a more complex formula, always make sure your formula has the same number of open brackets and close brackets in total.
When adding calculated fields, type into the box, click on the three dots and click Merge Fields to select a report column to include in your calculation.
Once you've created your formula and added the Data type, click Save and it'll appear in your report. You'll also most likely want to add a Column label to re-name the formula column heading.
Subtraction and addition
Type = into the box, followed by the fields or text you want to add, with a + symbol, or subtract, with a - symbol.
For example:
Division and multiplication
Type = into the box followed by the fields or text you want to divide or multiply. Use / for divide and * for multiply.
For example, I want to estimate how many points a child might collect through the year, which I'll compare to the total.
Conditional 'Ifs': IF and IFS
Like in Excel or Google Sheets:
- IF can be used to set an output depending on the value of a field. If you use IF, you need to use IF before every new condition.
- IFS checks whether one or more conditions are met, and outputs a result. If you use IFS, you only need to specify this at the beginning of the formula.
Type =IFS or =IF into the box followed by the merge fields or text you want to add conditional IF/IFS fields to. Type the text output you want to display in quotation marks, shown below with "Great" and "Needs to improve".
After each conditional, add a comma, a space, and then the new merge field as you continue.
Merge fields that output numbers
The < and > symbols represent 'more than' and 'less than', respectively. You can also use = for 'equal to'.
In the above example, the formula is saying "if the total achievement points is more than 5, output "Great", and if the total achievement points is less than 5, output "Needs to improve".
In the report, this would look like:
Merge fields that output text
You'll need to wrap any merge fields that output text in quotation marks (put " before and after the merge field). Number fields do not need the quotation marks.
In the above example, the formula is saying "if the SEN status is SEN Support, return "K" and if the SEN status is Monitoring, return "N".
In the report, this would look like:
However, you'll also ideally want to add something to the formula that specifies what should happens if neither/none of the conditions are met. Otherwise, you'll get a #N/A error in these fields.
To do this, add a comma, a space, and then two quotation marks (, "") which tells the formula: if the conditions aren't met, return nothing. See this addition below:
Average
Type =AVERAGE(column1+column2)/2
This will show something like this in your report:
Equals
Type = and insert the merge field you want to show. This will make the advanced formula display the same thing as the merge field. If the value in the merge field changes, the value in the advanced formula will change.
Rounding
Type = and either ROUNDUP( or ROUNDDOWN(
Add in the merge field, add a comma and how many digits after the decimal place to display. For example: ,1 will show one decimal place and ,0 will show a rounded number. Then add close brackets )
This will show something like this in your report:
Concatenation
This is usually when you want to show a percentage.
Type =CONCAT( then add in the data you want to concatenate to place the values end to end in the same column with no spaces.
In this example, there is an embedded ROUNDUP formula to work out the difference in attendance to one decimal place, concatenated with the % symbol.
This will show something like this in your report:
Getting an error message when saving a calculated field?
Check your formula - you may not have it written correctly. In the below example, there is no closing bracket:
Comments
This is brilliant - at least if they aren't available on marksheets they can be used in custom report writer. Is it possible to have a MAX function added. Then it would be perfect.
Hi there! We don't have plans to add a MAX calculator, but if you click the column header you can sort the values to bring the highest to the top!
That's unfortunate - it's more where PE are calculating the max of marks for different PE options for their GCSE. It would be very useful if they could add it to report writer at some point.
Article is closed for comments.