HansaWorld - Integrated ERP and CRM

HansaManuals Home >> Standard ERP >> Report Generator >> Creating a New Report

Previous Next Entire Chapter in Printable Form Search

This text refers to program version 8.5

Creating a Report Generator Report - Useful Functions

This page describes some Standard ERP functions that will be useful in many Report Definitions. You can use these functions in your "Print If" conditions, in the Formula field in the 'Selection' dialogue box and in the 'Code' dialogue box (all on the 'Data' card), and in the 'Formula' dialogue box ('Layout' card). The function names are case-sensitive.

The descriptions in this section contain example lines of Code that use the following format:

variable1 = function(variable2,variable3);

The function will process the contents of variable2 and variable3 and copy the result into variable1. You can then use variable1 in calculations elsewhere in the report.

In all cases, you can replace the example line of Code with a Formula on the 'Layout' card as follows:

function(variable2,variable3)

The result will be a faster report that uses fewer variables as you don't need to declare variable1. This will be useful if all you need to do is print the result of the function. However, it is less flexible as you won't be able to use the result of the function (the contents of variable1) elsewhere in the report.

In a line of Code, the result of the function is placed in a variable. In a Formula, the result of the function is placed in an element in the report output. Some functions do not return a result to a variable in the same way. You can only use these functions in a line of Code. There will be no Formula equivalent. Examples are noted in the descriptions below.

blank(field or variable), nonblank(field or variable)

blank returns true if the field or variable is empty. nonblank returns true if the field or variable is not empty. You can also use !blank in place of nonblank.

The following example Print If condition will effectively print all Contacts with no Fax Number:

blank(vrContact.Fax)

If the field or variable is a number, blank will return true if the field or variable is empty, and false if it contains 0 or 0.00. nonblank will return false if the field or variable is empty, and true if it contains 0 or 0.00.

blankval

You can use blankval to empty a decimal variable. This can be useful if you want white space to appear in a report when otherwise 0.00 would be printed. The following example Code sets a decimal variable to blankval if it previously contained 0.00:

if(vdTestVar==0.00) then begin vdTestVar= blankval; end;

blankval will not empty an integer variable, but it will set the variable to 0.

You cannot test for blankval. For example, you cannot use the following Print If condition to print all Contacts where the Sales Credit Limit is blank:

vrContact.CreditLimit==blankval

This Print If condition will print all Contacts where the Sales Credit Limit is blank and those where the Sales Credit Limit is 0.00. If you need to test to see if a field or variable is blank (in the example, to print all Contacts where the Sales Credit Limit is blank but not 0.00), use !blank or nonblank in the Print If condition:

nonblank(vrContact.CreditLimit)

If you need to test for 0.00 (i.e. to print all Contacts where the Sales Credit Limit is 0.00 but not blank), use the following Print If condition:

(vrContact.CreditLimit == 0.00) and (nonblank(vrContact.CreditLimit))

CurrentDate

Use CurrentDate to print the current date in a report or to use the current date in a formula or calculation.

The following example Code will place the current date into a date variable, which can then be printed or used elsewhere in the report:

vdDate = CurrentDate;

The date will be printed in the report using the format specified in the various Date and Numeric Format settings. This is a numeric format (e.g. 01/01/2007). If you want to print the date in words, use the GetDateMonthInWords, GetMonthName or MonthShortName functions described below.

The following example Print If condition will print all Contacts that were last modified on the day the report is produced:

vrContact.DateChanged == CurrentDate

CurrentTime

Use CurrentTime to print the current time in a report or to use the current time in a formula or calculation.

The following example Code will place the current time into a time variable, which can then be printed or used elsewhere in the report:

vtTime = CurrentTime;

The time will be printed in the report using the HH:MM:SS format.

CurrentUser

Use CurrentUser to print the Signature (initials) of the Person currently logged in (i.e. the Person producing the report) in a report or to use the Signature in a formula or calculation.

The following example Code will place the Signature into a string variable, which can then be printed or used elsewhere in the report:

vsUser = CurrentUser;

The following example Print If condition will print all Contacts whose Salesman is the current user:

vrContact.SalesMan == CurrentUser

To achieve the same result more quickly, use CurrentUser in the Formula field in the 'Selection' dialogue box:

SetInSet(string variable,string field), SetInSet2(string variable,string field)

These two functions allow you to find a single value (the value in the string variable) in fields that can store several values, such as the Tag/Object, Contact Classification and Item Classification fields (and the Persons and Cc fields in Activities). Please refer here for details and examples.

ValToString(number,type,thousands separator,decimal separator,rounding)

Use this function to convert a number (i.e. an integer or decimal field or variable, or a number) to a string. Please refer here for details and an example.

DateToString(date,date format)

Use this function to convert a date (i.e. a date field or variable, or a date) to a string. Please refer here for details and an example.

TimeToString(time)

Use this function to convert a time (i.e. a time field or variable, or a time) to a string. Please refer here for details and an example.

GetDay(date), GetMonth(date), GetYear(date)

These functions return as integers the day number, month number and year number respectively from a date. GetYear always returns a four-digit integer that always includes the century, irrespective of the Date and Numeric Format settings.

The following example Print If condition will print all Contacts that were last modified on the first day of a month:

GetDay(vrContact.DateChanged) == 1

The following example Code will place the day from the Start Date of an Activity into an integer variable, which can then be printed or used elsewhere in the report:

viDay = GetDay(vrAct.TransDate);

The following example Print If condition will print all Contacts that were last modified in 2019:

GetYear(vrContact.DateChanged) == 2019

This example Print If condition will print all Contacts that were last modified in the current year:

GetYear(vrContact.DateChanged) == GetYear(CurrentDate)

GetDateMonthInWords(date,string variable)

This function copies a date in a format that includes the month name into a string variable. For example, if the current date is 27/09/2019, this Code will place "27 September 2019" into vsDate:

GetDateMonthInWords(CurrentDate,vsDate);

The month name will be in the home Language of your Standard ERP system.

You cannot use GetDateMonthInWords in a Formula on the 'Layout' card.

GetMonthName(language,date,string variable)

This function copies the month name of a date in the specified Language into a string variable. For example, if the current date is 27/09/2019, this Code will place "September" into vsMonth:

GetMonthName("ENG",CurrentDate,vsMonth);

This Code will place the appropriate translation of the month name into vsMonth, depending on the Language of the current Invoice:

GetMonthName(vrInvoice.LangCode,vrInvoice.InvDate,vsMonth);

The month name in the specified Language will be taken from the Days and Months setting in the System module. If this setting does not contain the relevant month name in the specified Language, the month name will be in the home Language of your Standard ERP system. If you do not want to specify a Language (i.e. you want to bypass the Days and Months setting and always use the home Language, the first parameter should be an empty set of quotation marks:

GetMonthName("",vrInvoice.InvDate,vsMonth);

You cannot use GetMonthName in a Formula on the 'Layout' card.

MonthShortName(date)

This function returns the abbreviated month name of a date as a string. For example, if the current date is 27/09/2019, this Code will place "Sep" into vsMonth:

vsMonth = MonthShortName(CurrentDate);

The abbreviated month name will always be three characters and will be in the home Language of your Standard ERP system.

DateDiff(date2,date1)

This function returns as an integer the number of days between date2 and date1. If date1 is later than date2, the result will be negative. The following example Code places the age of an Invoice (based on its Due Date) when the report is produced into an integer variable:

viAge = DateDiff(CurrentDate,vrInvoice.PayDate);

The following example Print If condition will print all Invoices older than 30 days (based on their Invoice Dates):

DateDiff(CurrentDate,vrInvoice.InvDate)

If you want to use an actual date in the function, enclose it in quotation marks as if it were a string. This applies to every function with a date parameter:

DateDiff("01/01/2019",vrInvoice.InvDate);

TimeToSeconds(time,long integer)

Use this function to convert a time to a number of seconds. 00:00:00 will be converted to 0 seconds, 23:59:59 to 86399 seconds. The following example Code will convert the Start Time of an Activity to seconds and place that figure into vlSeconds:

TimeToSeconds(vrActivity.StartTime,vlSeconds);

If you want to convert an actual time to seconds, enclose it in quotation marks as if it were a string. This applies to every function with a time parameter:

TimeToSeconds("23:59:59",vlSeconds);

You cannot use TimeToSeconds in a Formula on the 'Layout' card.

SecondsToTime(long integer,time)

Use this function to convert a number of seconds to a time. The following example Code converts the Start Time of an Activity to seconds, adds one hour, and converts the result back to a time:

TimeToSeconds(vrActivity.StartTime,vlSeconds);

vlSeconds = vlSeconds + 3600;

SecondsToTime (vlSeconds,vtTime);

You cannot use SecondsToTime in a Formula on the 'Layout' card.

TimeDiffInSeconds(time1,time2)

This function returns as a long integer the number of seconds between time1 and time2. If time2 is later than time1, the function assumes they are times from the same day. If time1 is later than time2, the function assumes that time2 is from the following day. For example, if time1 is 10:00:00 and time2 is 09:00:00, the function will return 82800 (23 hours expressed in seconds). The following example Code places the time taken to complete an Activity into a long variable:

vlElapsed = TimeDiffInSeconds(vrAct.StartTime, vrAct.EndTime);

If it is likely that time1 and time2 will be from different days, you can use HoursDiff (described below), which returns the difference between time1 and time2 as a number of hours. Alternatively, use DateDiff to calculate the number of days, subtract one if time1 is later than time2, convert the result to seconds, and add this result to the result of TimeDiffInSeconds.

TimeDiff(time1,time2)

This function is similar to TimeDiffInSeconds described above, but returns the result as a time.

HoursDiff(date1,time1,date2,time2)

This function returns as a decimal the number of hours between time1 and time2, taking the dates into account. For example, if the difference between the two times is 3 hours 30 minutes, HoursDiff will return 3.5. The following example Code places the time taken to complete an Activity into a decimal variable:

vdElapsed = HoursDiff(vrAct.TransDate, vrAct.StartTime,vrAct.EndDate,vrAct.EndTime);

AddTime2(time,long integer)

Use this function to add a number of minutes to a time. The result is returned as a time. The following example Code adds one hour to the current time:

vtTime = AddTime2(CurrentTime,60);

Left(string1,number)

This function returns as a string the initial characters of string1. For example, if the number is three, Left will return the first three characters of string1. The following example Code places the first two characters of the current date into a string variable (i.e. if you are using the dd/mm/yyyy format with leading zeros, it will place the date into the string variable):

vsDay = Left(CurrentDate,2);

Right(string1,number)

This function returns as a string the final characters of string1. For example, if the number is three, Right will return the last three characters of string1. The following example Code places the last four characters of the current date into a string variable (i.e. if you are using the dd/mm/yyyy format with leading zeros, it will place the year into the string variable):

vsYear = Right(CurrentDate,4);

IVGetTurnover(customer number,item number,item group code,item class,branch,bar tab class, person signature,string variable,date1,date2)

Use IVGetTurnover if you need to create a report listing sales figures for a particular period. IVGetTurnover can return sales figures for Customers, Items, Item Groups, Item Classifications, Persons or any combination. Please refer here for details and examples.

MulRateToBase1(currency,value,from rate,to rate (base currency 1),to rate (base currency 2),base currency 1,base currency 2,round off)

If you are using Currencies, you will often need to convert values to Base Currency 1 (your home Currency) in reports. You can do this using the MulRateToBase1 function. The figure that is to be converted should be the second parameter. The following example will convert the Subtotal in an Invoice (vrInvoice.Sum1) to Base Currency 1 and place the result in vdBC1Val:

vdBC1Val = MulRateToBase1(vrInvoice.CurncyCode, vrInvoice.Sum1, vrInvoice.FrRate, vrInvoice.ToRateB1, vrInvoice.ToRateB2, vrInvoice.BaseRate1, vrInvoice.BaseRate2,DefaultCurRoundOff)

"CurncyCode" is the internal name for the Currency field, and "FrRate", "ToRateB1", "ToRateB2", "BaseRate1" and "BaseRate2" are the internal names for the five exchange rate fields on the 'Currency' card of the Invoice. The same six internal names are used for these fields in every register.

"DefaultCurRoundOff" is an expression that means the result of the currency conversion will be rounded according to the appropriate rule in the Currency Round Off or Round Off settings before it is copied to vdBC1Val. It is recommended that you always use "DefaultCurRoundOff" as the final parameter.

You can pass all figures to MulRateToBase1 (i.e. you do not need to test to see what Currency a figure is in first). If a figure is already in Base Currency 1, you can pass it to MulRateToBase1 and it will not be changed.

MulRateToBase2(currency,value,from rate,to rate (base currency 1),to rate (base currency 2),base currency 1,base currency 2,round off)

MulRateToBase2 is similar to MulRateToBase1, but converts values to Base Currency 2.