Using calculated 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.

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!

Please note that this feature is only available on School MIS sites.

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

You can only add a calculated field to the right of the contributing columns. 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_2021-08-23_at_12.53.17.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_2021-08-26_at_11.51.44.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_2021-08-26_at_12.11.17.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_2021-08-26_at_12.09.27.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_2021-08-26_at_12.25.54.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_2021-08-26_at_12.36.56.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_2021-08-26_at_13.04.42.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 0 found this helpful
I'm still stuck!

Comments

0 comments

Please sign in to leave a comment.