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 creating a report, you can add the Advanced Calculation/Formula Column in Select Columns (Step 3 of 8).
When editing a report, click to add a new 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).
When adding calculated fields, type into the box, and click Merge Fields to select a report column to include in your calculation.
Subtraction and addition
Type = into the box, followed by the fields or text you want to add or subtract. For example:
Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.
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.
Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.
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.
- IFS checks whether one or more conditions are met, and outputs a result.
Type =IFS or =IF into the box followed by the merge fields or text you want to add conditional fields to.
Top Tips:
- Wrap any merge fields that output text in quotation marks (put " before and after the field). Number fields do not need the quotation marks, shown below with Points this term.
- Type the text output you want to display in quotation marks, shown below with Needs to improve.
Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.
Conditional 'Ifs'
Type =IFS into the box followed by the fields or text you want to add conditional fields to. Type the text output you want to display in quotation marks.
Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.
Average
Type = and fill in the gaps with the field you want to average and the number of fields.
Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.
Equals
Type = and fill in the gap with the column value you want to show. This will make the two columns display the same thing. If the value in the first column changes, the value in the second column will change.
Rounding
Type = and either ROUNDUP or ROUNDDOWN. Add in the column, and select how many digits after the decimal place to display, for example, 1 will show one decimal place.
Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.
Concatenation
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.
Here I have worked out the difference in attendance to one decimal place, and have concatenated it with the % symbol.
Once you've set the column with the header you want and reordered your columns, 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 this example, I have an extra bracket I need to remove.
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.