One of the great functions of Method Report Designer is the ability to use specialized fields to achieve certain goals. Considering many reports make use of sums and figures, it stands to reason that a specialized field used to do mathematical equations within your report would be useful.

That’s where calculated fields come in. These fields are used in conjunction with other fields to manipulate data, giving you totals, percentages, and other arithmetic-based information in much the same way as equations are used in programs like Microsoft Excel.

A few things to note about calculated fields:

To illustrate how calculated fields are used in Report Designer, we will build on the example report we used in our Detail reports documentation. Let’s presume you would like to calculate a commission of 12 percent based on the sales represented by a series of invoice amounts. Here’s where calculated fields come in.  

1. To begin, follow the steps laid out in our Detail Reports section to build a basic report with invoice amounts included for each customer.  

2. Choose the field you wish to calculate from the Field List on the right-hand side of your screen. For this example, we'll be looking for Invoice.Customer. Right-click on this field and click Add calculated field.

This will create a field within the Field List called Calculated Field 1.  You’ll be able to recognize it because it has a different icon than all the other fields in the list!

Note: It's recommended to rename the Calculated Field so you can locate it easily. To do so, select the field and scroll down to Property Grid, where you can rename the field under Design, (Name).

3. Now you’ll need to tell the field what you want it to calculate. Click on the ellipses button [...] next to Expression in the field properties to open the Expression Editor. 

4. Within the Expression Editor, click on Fields and double click on [Amount]. This tells the editor you are pulling numbers from the Invoice Amount, and it will appear in the expression editor field.

5. To express 12% of the amount, click on the multiply button (X) and then type in "0.12". You're done with defining the calculated. Click Okay.

Note: It’s worth noting that the percentage button (%) in the Expression Editor does not actually refer to percentages - it's a programming operator that refers to remainders in division equations. Don’t use it to try and calculate percentages.

6. Once you have defined the field, locate it in the Field List and drag it next to your other fields in the Detail Report section. Now check the Preview screen, and you’ll see you now have a new column that shows you a 12% commission amount next to each invoice entry!

Note: Remember to create headers for new columns so your report has context and those reading it know what numbers mean.

And you're done! You've created a calculated field. 

If you make a mistake when creating your calculated field, you have the option to edit them.

1. Right-click the calculated field in the Field List and selecting Edit Calculated Field. You don't have to right-click on the field itself.

This will bring up a list of all your saved calculated fields. You can select any one of them and make edits on the right hand side of the list. 

You are able to change the properties of the field, including (most importantly) the name and field expression. 

Note: When you edit a calculated field and then return to the detail section, you will note the name displayed in the field itself will not automatically update. You can delete the field and re-drag it from the Field List to update it.

2. Click on the ellipses button [...] next to Expression to bring up your Expression Editor again, where you can change the equation as you did in steps 4 and 5.

Note: When you return to the Expression Editor, you will encounter a limitation in Report Designer that artificially expands the expressions in the editor to include information you don’t need, so you will need to manually remove that extraneous information. 

In the above example, the [Amount] has been expanded to say [Customer.Invoice.Customer.Amount]. You’ll have to delete the extra information in order for the field to work. Unfortunately this is the only way around this limitation.

3. Once you’ve finished these steps, click Preview. You will be able to see (in this example, at least), a column of 12% commission totals next to each invoice associated with a customer.  

And that’s it! 

You can use calculated fields in this way to express all sorts of sums in your Report. However, if you’re interested in tallying totals, you might want to check out our documentation on Summaries.