OVERVIEW
How to use calculated fields

Overview

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 awfully 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 - just better.

Some of you might be asking, however, why use calculated fields at all?  Why not just add a field with the data calculated directly in Method?  Well, simply put, this just makes more sense in specific scenarios.  Think about it this way - there might be a lot more overhead if we were to update a particular field (calculations) for every record before we print a report, so why not have a modular field you can customize on the fly to do the calculation you need at that exact moment?  That's what calculated fields are designed to do, and best of all, once you create one for a particular report, you can use it over and over again within that unique report. One thing to keep in mind, you won't find calculated fields added to the Report Designer in Method's tables as these fields are calculated and displayed or printed only when the report is generated.

back to top

How to use 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 percentage 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 will be looking for Invoice.Customer.  Right-click on the selected 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!
   
   

   TIP:	While it’s not required, you might find it helpful to rename the Calculated Field so you can locate it easily.  To do so, select the field and scroll down to field properties, where you can rename the field.

    
3. Now you’ll need to tell the field what you want it to calculate, or “express”.  Click on the ellipses button [...] next to Expression in the field properties.  This will open the Expression Editor.  
4. Click on Field and double click on Amount.  This tells the field you are pulling numbers from the Invoice Amount, and it will appear in the editor field above. 
   
   

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

    
5. To express 12 percent of the given amount, click on the Multiply button (X) and then type in 0.12 (or type in 12 and then click on the Divide button and type in 100).  Then click Okay.  You have now defined the number you want the calculated field to express!
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 percent commission amount next to each invoice entry!
   
   

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

    
7. You also have the option to edit calculation fields, simply by right clicking in the Field List and selecting Edit Calculated Field.  This will bring up a list of all your saved calculated fields.  On the right hand side of this list you are able to change the properties of the field, including (most importantly) the name and field expression. 
   
   

   TIP:

   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’ll need to change it manually on this screen by clicking and retyping in a name.  Contrarily, you can delete the field and re-drag it from the Field List.

    
8. 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 5 and 6.
   
   

   TIP:

   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.  For example: if you edit the calculated field you created in step 5, you’ll see 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.

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

p style="text-align: right;">back to top