This series covers the basics of creating a report in the Web Report Designer. Previousy, you learned about filtering your report.
In this article, you will learn about calculated fields.
What are Calculated Fields?
One of the great functions of the Web 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 calculate 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:
Once created for a report, it can be used again within that report.
Calculated fields are calculated and displayed when the report is generated. You will not find these fields in any of Method's tables.
In the next example, we'll presume you would like to calculate a commission of 10 percent based on the sales represented by a series of invoice amounts.
Create the Calculated Field
If not already open, make sure you have your Customer Invoices' report open. The 10% commission amount is chosen only for this example and is a simplification of how commissions would work in practice. It's only used here to demonstrate how calculated fields work.
Click the Field List icon, and expand your Method Account, and then expand the Customer table. Select the Invoice.Customer line. You will see a small icon appear beside it.
That icon, which is too small to see, let's you add a calculated field. You can add a calculated field to any of the shown tables. For now, we will add it to Invoice.Customer table so click it to add a calculated field.
The Invoice.Customer line will expand, and you will see an added field called calculatedField1. (If not, scroll down to find it for the fields are listed in alphabetical order.) Beside it are two icons: edit (pencil) and delete (trashcan). Click the edit (pencil) icon. This expands the properties for the calculated field.
Rename the Name and the Display Name.
Name: "
commissionField"Display Name: "
10% Commission"
Great! you created the calculated field, now to build the expression to do the calculations.
Build the Expression
Click the ellipsis (...) beside Expression. This will bring up the Expression Editor.
Notice that Fields is selected in the left lower box. This displays the fields within Invoice.Customer. Double-click the Amount field to add it to the editor.
The box with the "1" is a text editor. You build the calculation in this box. Right now, it says "
[Amount]", so that means it will display the amount. What we want to do is calculate a percentage! So click on Operators to see all the math operations we can use.
Of course, you'll find that it's just math symbols. You could click on them, or type in "
* 0.1" beside "[Amount]".
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.
That's it! You created a calculated field. Click OK to save your changes. While you're at it, click Save to save your report.
Within the DetailReport1 band, move other controls so you can make room for the new field. Drag your new commissionField into this band beside the Amount field.
Style this new field. Select the Properties icon, and then change its Text Format String to currency. Also, change its Text Alignment to Top Right, to right align it.
You'll need a header for it as well. Copy one of the other headers and paste it in. Change it to your abbreviation for Commission.
Save the report again, and click Preview.
Great! You can use calculated fields in this way to express all sorts of calculations in your Report.
You may think you would use calculated fields to calculate the total for the commissions, however, if you need to tally totals, join us in the next lesson where we talk about Summaries.