Using advanced calculated formula fields in the Custom Report Writer

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).

Screenshot_2021-08-17_at_10.24.57.png

 

When editing a report, click to add a new column.

Screenshot_2021-08-17_at_10.22.37.png

Screenshot_2021-08-17_at_10.24.03.png

 

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.

Screenshot_2021-08-17_at_10.23.41.png

 

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, AVERAGE, CONCAT, ROUNDDOWN, ROUNDUP

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.

Screenshot_2021-08-17_at_10.48.22.png

Subtraction and addition

Type = into the box, followed by the fields or text you want to add or subtract. For example:

Screenshot_2021-08-23_at_12.44.45.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_14.55.08.png

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. 

Screenshot_2021-08-26_at_11.50.10.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_14.56.17.png

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.

Screenshot_2022-08-24_at_14.58.13.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_14.58.42.png

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.

Screenshot_2022-08-24_at_14.58.13.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_14.58.42.png

Average 

Type = and fill in the gaps with the field you want to average and the number of fields.

Screenshot_2021-08-26_at_12.25.19.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_14.59.42.png

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.

Screenshot_2021-08-26_at_12.28.03.png

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.

Screenshot_2021-08-26_at_12.37.27.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_15.01.30.png

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.

Screenshot_2021-08-26_at_13.03.21.png

 

Once you've set the column with the header you want and reordered your columns, this will show something like this in your report.

Screenshot_2022-08-24_at_15.06.07.png

 

 

Getting an error message when saving a calculated field?

Screenshot_2021-08-26_at_12.38.20.png

Check your formula - you may not have it written correctly. In this example, I have an extra bracket I need to remove.

Screenshot_2021-08-26_at_12.39.29.png

Was this article helpful?
0 out of 3 found this helpful
I'm still stuck!

Comments

3 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.

    0
  • 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!

    0
  • 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.

    0

Article is closed for comments.