Calculated Fields

Creating a new Calculated Field

  1. Right-click in the section of the Field List in which the field needs to be located. Any field that will be used inside a band (Classes band, Contacts band, etc) needs to reside inside that area of the Field List.
  2. Select Add Calculated Field.
  3. In the Property Grid, change the name of your calculated field to something that makes sense to you. The field will move to its new alphabetical location in the Field List.
  4. Change the Field Type if necessary. Leaving it at “None” is acceptable.

 

Notes: Always change the name of the calculated field before binding it to anything on your report. Binding will be lost if the name is changed afterwards.  The field may be bound to the report before or after giving the calculated field a value.

 

Giving the Calculated Field a Value – Expression or Script

 

Two methods are available for giving your calculated field a value. Using the Expression is more straight-forward, but Scripts are sometimes necessary for things that are not available in the Expression Editor. Anything that is school-defined (see examples below) cannot be accessed through a simple expression and must be defined using a script.


The main advantage to having a calculated field use a script as opposed to attaching a script directly to an element (cell or label) is that the calculated field can be easily re-used throughout the report. It can even be used in other calculated fields.

Expression:

 

  1. Right-click on your calculated field and choose Edit Expression.
  2. Use the Expression Editor to define your field as desired. The Expression Editor provides functionality to build logical expressions using the built in industry-standard logical operators, functions and data types.
  3. Examples are listed below.
Expression Result Usage
[Personal Details.Gender] == 'F' Will print a checkmark in the box when student’s gender is female. You will need two checkboxes, one for male and one for female. Create a blank checkbox on the report first, and change its text the format you want (“F”, “Female”, etc). Then, bind this field to the checkbox.
iif ([Personal Details.Gender] = 'F', 'her', 'his') Field can be used within a sentence to print a gender-specific word. Use this field in the middle of a sentence: “We are concerned about [GenderWord] attendance in this class.”

 

Scripts:

 

  1. Click on the Scripts pane at the bottom of the report designer interface.
  2. Write the script (please contact Maplewood support for help on getting started) at the top of the scripts pane. Sometimes it is easier to write the script in a text editor then copy & paste into the Scripts pane.
  3. Click Validate at the top of the Scripts pane to validate your script.
     a. If you have a complex report with several scripts, it may be faster to go back to the Designer pane, click Save, exit and go back in to the report – any script errors will show up in the Script Errors window at the bottom.
  4. Once the script is written and validated, click on the Designer pane and click Save. (Do not click Save on the Scripts pane).
  5. To attach your new script to your calculated field, right-click on the calculated field and choose Edit Calculated Fields. Select the calculated field from the list.
  6. In the list of Properties on the right, find Behaviour – Scripts – Get a Value.
  7. Beside “Get a Value”, choose your new script from the list.
  8. Example is listed below.

 

Example: Term start and end dates based on the term code would be different for each school that is running the report, and therefore the calculated field would need a script. (Two calculated fields, and two scripts: one for StartDate and one for EndDate).

 

Private Sub TermEndDate_GetValue(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.GetValueEventArgs)
Dim curStu As Student = DirectCast(GetCurrentRow(), Student)
If curStu IsNot Nothing Then
' Pass the desired term code into FindByTermCode
Dim t As Term = curStu.School.Terms.FindTermByTermCode("MBS21")
If t IsNot Nothing Then
' Term has StartDate or EndDate
e.Value = t.EndDate
End If
End If
End Sub

To use this calculated field with this script attached to display the end date on the report, bind the calculated field directly to a cell or label on the report.

To use this calculated field within another calculated field, refer to this new field within square brackets in the expression of the other calculated field, e.g.,