Navigation:  How to > The Report Wizard > The Report Wizard Step 3: Create Work Field Columns > Definition of all Functions

Steps 3 and 4 of the Report Wizard offer several functions to be used in creating calculations. For your convenience the complete definitions of all the available functions are listed below.

 

ADDDAYS( aDate, aNumber)    

Adds the number of days specified by aNumber to the date specified by aDate to give a new date. For example, to calculate the date 10 days past the loan due date you would type ADDDAYS( LNDUDT, 10). To calculate the date 10 days before the due date you would type ADDDAYS( LNDUDT, -10).

 

ADDMONTHS( aDate, aNumber)    

Adds the number of months specified by aNumber to the date specified by aDate to give a new date. For example, to calculate the date 3 months past the loan due date you would type ADDMONTHS( LNDUDT, 3). To calculate the date 3 months before the due date you would type ADDMONTHS( LNDUDT, -3).

 

ADDYEARS( aDate, aNumber)    

Adds the number of years specified by aNumber to the date specified by aDate to give a new date. For example, to calculate the date 7 years past the loan due date you would type ADDYEARS( LNDUDT, 7). To calculate the date 7 years before the due date, you would type ADDYEARS( LNDUDT, -7).

 

ALL (RepeatingField =<><=>= value)

This function is an advanced function that allows you to test all values of a repetitive field for certain conditions. For example, there are 12 Deposit Master hold and action code fields that all have the same mnemonic (DMHACC). This function can test all 12 of these fields using this function. To use this function, replace "aRepeatingField" with the name of the field to be tested. Replace "aLogicalOperator" with one of the following operators: = (is equal to), <> (is not equal to), > (is greater than), >= (is greater than or equal to), < (is less than), or <= (is less than or equal to). Replace "aValueList" with either a single value or a list of values separated by commas. If this expression is true, its value becomes 1. If it is false, its value is 0. This can be used in conjunction with the IF, THEN, and ELSE operators to make quick decisions.

 

 

For example, if you wanted to select all accounts that have no hold codes, you could type ALL( Hold Action Code = 0). Only accounts that have all hold codes equal to 0 would be selected.

 

When using the ALL function, the statement must be true for each of the repeated fields or else it will be false. If we used ALL( Hold Action Code <> 0) in the example above, then only accounts that have a non-zero value in each of the 12 hold code fields would be selected. Refer to the "ANY" function for selecting if any of the items are true.  

 

 

ANY (RepeatingField =<><=>= value)

This function is an advanced function that allows you to test for any repetitive field for certain conditions. For example, there are 12 Deposit Master hold and action code fields that all have the same mnemonic (DMHACC). This function can test all 12 of these fields to see if any of them have the desired values using this function. To use this function, replace the parameter, aRepeatingField, with the name of the field to be tested. Replace aLogicalOperator with one of the following operators: = (is equal to), <> (is not equal to), > (is greater than), >= (is greater than or equal to), < (is less than), or <= (is less than or equal to). Replace the parameter, aValueList, with either a single value or a list of values separated by commas. If this expression is true, its value becomes 1. If it is false, its value is 0. This can be used in conjuction with the IF, THEN, and ELSE operations to make quick decisions.

 

 

For example, IF ANY( Hold Action Code = 6, 7, 11) THEN ‘LEGAL’ ELSE ‘ ‘ used in a work field will place the word LEGAL into the field if any one of the 12 hold codes on the account contain any of the values 6, 7, or 11. If none of 12 contain 6, 7, or 11 then it places a blank into the work field.

 

If in this same report we included the selection ANY( Hold Action Code <> 0) we would get a report that shows all accounts that have any holds at all containing a column with the word LEGAL for accounts with a hold code of 6, 7, or 11.

 

When using the ANY function, if the statement is true for any of the repeated fields the statement is true. If we used !(ANY( Hold Action Code = 0)) in the example above, then only accounts that have a non-zero value in each of the 12 hold code fields would be selected. Refer to the ALL function for selecting if all of the items are true.

 

 

DATE( aMonth, aDay, aYear)

Creates a date field from numeric fields. The valid values for aMonth are 1 - 12 (January - December). The valid values for aDay are 1 - 31 (depending on the month and the year). The field, "aYear," includes the century. For example, August 27, 2010, would be entered as DATE( 8, 27, 2010)

 

DAY( aDate)

Returns the day of the date given in aDate. For example, the day of August 27, 2010 is 27.

 

DIFFDAYS( StartDate, EndDate)

Returns the number of days from the start date to the end date. If the end date is prior to the start date, the value returned is negative.

 

DIFFMONTHS( StartDate, EndDate)

Returns the number of full months from the start date to the end date. If the end date is prior to the start date, the value returned is negative.

 

DIFFYEARS( StartDate, EndDate)

Returns the number of full years from the start date to the end date. If the end date is prior to the start date, the value returned is negative.

 

FIND( ‘Value’, Field)

Searches a text field (Field) for a given substring (Value). If the substring is found, the position of the first location of the substring is returned. If it is not found 0 is returned. This can be used for searching for certain values in text fields and determining if they exist or not. For example:

 

IF FIND( ‘AUTO’, Collateral Description) THEN ‘Car Loan’

 

If the collateral description contains the word AUTO anywhere within it then the text Car Loan is placed into the text work field for which this is defined.

 

FORALL record( aLogicalExpression)

 

FORANY record( aLogicalExpression)

FORALL tests the aLogicalExpression for each of the records specified by record and returns 1 (true) if all of them are true, otherwise it returns 0 (false).

 

FORANY tests the aLogicalExpression for each of the records specified by record and returns the count of items that are true. If none are true, it returns 0. For this function 0 is false, and non-zero is true.

 

These advanced functions are used when multiple sub-records are linked to a master record to apply the logical expression to all of the sub-records. Examples of situations where records may be linked in this manner include multiple history records linked to a master record or multiple reserve disbursement records linked to a loan master record. The multiple records are linked to the master record in Report Wizard Step 2: Select Fields. It is possible to link records to records for several levels. For example, you may want to link the Reserve Disbursement record to the Loan Master record for a report. You may then want to link several Company Agents to the Reserve Disbursement record. When multiple records are linked in this manner, the complete field name includes the path through all the linked records to get to the field. The root record (which is the main record that you first selected for the report) is not included in the path name. One of the fields in the Company Agent record is the Payee Number. The path of the 1st occurrence of this field in the report is:

 

 ReserveDisbursement1.CompanyAgent1.PayeeNumber

 

Notice that the records in a path name are separated by periods and that the count of a record is appended to the record name. In the example above the third Reserve Disbursement record would be ReserveDisbursement3.

 

In the FORALL and the FORANY functions, you replace the record parameter with the complete path name less the record count for the record you desire to work with. For example, if you want to test all the Payee Numbers tied to the loan master to see if they are zero you would type the following:

 

         FORALL ReserveDisbursement.CompanyAgent( PayeeNumber = 0)

 

This would apply the logical expression within the parentheses to each of the Company Agent records within the Reserve Disbursement records.

 

How would you code FORALL or FORANY for a path that contains multiple links of multiple records? Let’s consider that the ABC record is a record linked to a master record. It has 5 multiple linked XYZ records tied to it. For each of these 5 we have linked 2 QRS records. In the QRS record, there is a field called MyField that we want to reference. This means that for each ABC record we read, there are 10 MyFields that we want to access. The paths to these fields are:

 

ABC1.XYZ1.QRS1.MyField

ABC1.XYZ1.QRS2.MyField

ABC1.XYZ2.QRS1.MyField

ABC1.XYZ2.QRS2.MyField

ABC1.XYZ3.QRS1.MyField

ABC1.XYZ3.QRS2.MyField

ABC1.XYZ4.QRS1.MyField

ABC1.XYZ4.QRS2.MyField

ABC1.XYZ5.QRS1.MyField

ABC1.XYZ5.QRS2.MyField

 

If we wanted to test to see if any of these fields contained the value 12 we could type 10 IF statements and fill out the complete path name to each field. That would become very difficult if there were dozens of MyFields instead of just 10. Or we can use a FORANY statement to test them in a single IF statement. For example consider the following:

 

 IF FORANY ABC.XYZ.QRS( MyField = 12) THEN ‘GOTIT’

 

If any of the 10 fields contain the number 12, this statement will place the text GOTIT into the text field for which this is the definition.

 

Now let’s suppose that we only want to test MyField if the field XYZcode in the XYZ record is 'Y.' We want to ignore any records for which the XYZcode is not 'Y.'  A statement to perform this function looks like this:

 

IF FORANY ABC.XYZ( XYZcode = ‘Y’ & FORANY QRS( MyField = 12)) THEN ‘GOTIT’

 

Notice that the inner FORANY statement does not have the ABC.XYZ portion of the path name. That is because it is supplied by the outer FORANY statement. The outer statement will process ABC.XYZ records 1 through 5. The inner statement will process the QRS 1 and 2 records for each of the outer ABC.XYZ statements. In this example, the MyField will be tested to see if it is equal to 12 only if the XYZcode field of the XYZ record contains ‘Y’.

 

It is legal to mix the nesting of FORALL and FORANY functions. In the example above if the inner function is FORALL instead of FORANY the outer FORANY function will return true only if at least one ABC.XYZ records has the XYZcode field equal to ‘Y’ and both QRS1.MyField and QRS2.MyField are equal to 12.

 

What is a valid aLogicalExpression? A logical expression is something that compares one thing to another and returns true. You can use a value list in these expressions just as you can in the ANY and ALL functions described above. Continuing to use the example of MyField above, the following are some examples of valid logical functions.

 

 

The first is true if MyField is less than 30. The second is true if MyField is equal to 6, equal to 7, or equal to 12. The third is true if MyField is equal to 21 through 34. The fourth is true if the value in MyField is the same as the value in AnotherField or if MyField is zero. The fifth is true if the sum of MyField and AnotherField is greater than 100.

 

 

MONTH( aDate)

Returns the month of the date given in aDate. For example, the month of August 27, 2010 is 8.

 

ROUND( aNumericalFieldOrExpression, aNumberOfDecimalPlaces)

Rounds the aNumericalFieldOrExpression to the number of decimal places specified by aNumberOfDecimalPlaces. If the digit following the desired decimal place is 0-4 the number is rounded down. If it is 5-9, the number is rounded up. If the round function is used to create a number that has more decimal places than it currently has, no rounding takes place. The resulting number will have the number of decimal places specified by aNumberOfDecimalPlaces. The following examples show how the round function works.

 

 

RUNDATE()

Returns today’s date (the date that the report runs).

 

TRUNCATE( aNumericalFieldOrExpression, aNumberOfDecimalPlaces)

Truncates the aNumericalFieldOrExpression to the number of decimal places specified by aNumberOfDecimalPlaces. No rounding is performed. If the truncate function is used to create a number that has more decimal places than it currently has, zeros are added to the end. The result number will have the number of decimal places specified by aNumberOfDecimalPlaces. The following examples show how the truncate function works.

 

         

YEAR (aDate)

Returns the year of the date given in aDate. For example, the year of August 27, 2017 is 2017.

 

 

---