Salesforce

Report Generator Functions (Magic xpa 3.x)

« Go Back

Information

 
Created ByKnowledge Migration User
Approval Process StatusPublished
Objective
Description

Report Generator Functions (Magic xpa 3.x)

When defining variables in the Report Generator the following functions are available:

String Functions

Function

Description

Contains

Checks if a value exists in a group of values.

Syntax: Contains(string, search string)

Returns: It returns Boolean Value True/False

Example: Contains(~name~Var,'ve')
When ~name~Var has the value of ’Edeveloper’ then a True value is returned.

DelLeft

Removes a specified number of characters from an Alpha string, starting with the leftmost character.

Syntax:DelLeft (string, Len)

Parameters:
string
: An Alpha string.
Len
: The number of characters to be removed, starting from the leftmost character.

Returns: The Alpha string with a truncated string

Example: DelLeft(variable,3)
If variable =’Magic’ then it displays the string as ‘ic’

DelRight

Removes a specified number of characters from an Alpha string, starting with the rightmost character.

Syntax:DelRight (string, Len)

Parameters:
string
: An Alpha string.
Len
: The number of characters to be removed, starting from the rightmost character.

Returns: The Alpha string with a truncated string

Example: DelRight(variable,3)
If variable =’Magic’ then it displays the string as ‘Ma’

EndsWith

Checks if a string ends with another string.

Syntax:EndsWith (string, search string)

Parameters:
string
: An Alpha string
search string
: The string to be searched

Returns: The Boolean value(True/False)

Example: EndsWith(‘Magic’,’ic’)
If the string ends with ‘ic’ then it returns True, otherwise it returns False

Format

Applies a selected format to a string value.

Syntax: Format (string, format string)

In

Checks if a value exists in a list of values.

Syntax: In (value1, value2, value3,…)

Returns: Boolean TRUE/FALSE value

Example: ~Rec_alp1~Var In (1,3) will return True when “Rec_alp1~Var has the value of 1 or 3.

IndexOf

Returns a number that represents the first position of a substring within an Alpha string.

IndexOf (string, search string)

Returns: the number 0,1,2,3…

Example: IndexOf (string,’M’)
If the string =’Magic’ then it returns the position of M in the string.
Here it returns 1, meaning the position of ‘M’ in the string.

IsNull

Checks for the presence of a NULL value in a variable.

IsNull (value)

Returns: a Boolean value

Left

Returns a specified number of characters from an Alpha string, starting with the leftmost character.

Syntax: Left (string, Len)

Parameters:
String
: An Alpha string
Len
: The number of characters to be returned, starting from the leftmost character.

Returns: The Alpha string

Example: Left (string, 3)
If the string is ‘Magic’, then it returns ‘Mag’

Length

Returns the defined length of an Alpha string.

Syntax: Length (string)

Parameters:
string
: A variable whose length is calculated

Returns: The defined length of an Alpha string.

Example: Length(Var ~String1~Var)
If Var ~String1~Var contains ‘Magic’ then it returns 5.

Replace

Replaces an Alpha substring within a string with another substring.

Replace (string, search string, new string)

ReplaceAll

Replaces any occurrences of an Alpha substring within a string with another substring.

Syntax: ReplaceAll (string, search string, new string)

Parameters:
string
: An Alpha string
search string
: The string to search
new string
: The string that will replace the search string

Returns: The Alpha string.

Example: ReplaceAll (~Name~Var,'Magic','Software')
Searches for the string ‘Magic’ and replaces it with ‘Software’.

RepNull

Replaces a NULL value within a string with another substring.

Syntax: RepNull (string, new string)

Parameters:
string
: An Alpha string
new string
: The string to replace the original string with

Right

Returns a specified number of characters from an Alpha string, starting with the rightmost character.

Syntax: Right (string, Len)

Parameters:
string
: An Alpha string
Len
: The number of characters to be returned, starting from the rightmost character.

Returns: The Alpha string

Example: Right(string, 3)
If the string is ‘Magic’ then it returns ‘gic’.

Split

Returns a token from a delimited string.

Syntax: Split (string, search string, position)

Parameters:
string
: A variable as defined in a table.
search string
: The search string that you want to look for in the string.
position
: The position order of the token within the delimited Alpha string.

StartsWith

Checks if a string begins with another string.

Syntax: StartsWith (string, search string)

Parameters:
string
: A variable as defined in a table.
search string
: The search string that you want to look for in the string.

Returns: Boolean TRUE/FALSE value

Example: StartsWith(string,’M’)
If the string is ‘Magic’ it returns the TRUE value.

SubString

Returns a specified number of characters from an Alpha string.

Syntax: SubString (string, position, length)

Parameters:
string
: A variable as defined in a table.
position
: Determines from where to start copying characters to the new string.
length
: The number of characters that you want to retrieve.

Returns: A specified number of characters from an Alpha string.

Example: SubString(string, 3,3)
This example will display the string starting from the 3rd character and display up to 3 characters.
If the string is ‘Magic’ then it returns ‘gic’.

ToString

Translates Number to String.

Syntax: ToString (number)

Parameters:
Number
: numeric variable

Returns: the Alpha string

Example: ToString(1) returns the string ‘1’.

Trim

Removes leading and trailing blanks from a string.

Syntax: Trim (string)

Parameters:
string
: An Alpha string.

Numeric Functions

Function

Description

Floor

Returns the largest integer value that is equal to or less than the given number.

Syntax: Floor (number)

Returns: The largest integer value that is equal to or less than the given number.

Example: ~nm~Var > Floor(4) displays records >4

Greatest

Returns the greatest number from a group of numbers.

Syntax: Greatest (number1, number2,…)

Parameters:
Number1,Number2, …
: Contains the variable to find the greatest among them.

Example:
Greatest (5, 6) returns 6 or
~Rec_alp1~Var > ~Greatest~Var

Least

Returns the lowest number from a group of numbers.

Syntax: Least (number1, number2,…)

Parameters: number1,number2 are variables of numeric type.

Returns: The lowest number from a group of numbers.

Example: Least (~n1~Var,~n3~Var)
If n1=5 and n2=4 then it returns 4.

Mod

Returns the remainder of an integer division.

Syntax: Mod (number1, number2)

Parameters: number1,number2 are variables of numeric type.

Returns: The remainder of an integer division.

Example: Mod (~n1~Var,~n3~Var)
If n1=5 and n2=4 then it returns 1.

Round

Returns a rounded number.

Syntax: Round (number)

Parameters:
number
: The value to be rounded

Returns: The rounded number

Example: Round (~LongNumber~Var)
If ~LongNumber~Var =10.56 then it returns 11.
If ~LongNumber~Var =10.12 then it returns 10.

RoundUp

Returns a rounded number to the higher value.

Syntax: RoundUp (number)

Parameters:
number
accepts the number

Returns: The rounded number to the higher value.

Example: RoundUp (~LongNumber~Var)
If ~LongNumber~Var =10.56 then it returns 11
If ~LongNumber~Var =10.12 then it returns 11

ToNumber

Translates String to Number.

ToNumber (string)

Syntax: ToNumber (string)

Parameters:
string
accepts the string

Returns: a number

Example: ToNumber (~alpha1~Var)

General Functions

Function

Description

FileExists

Checks if a specified file exists on a drive.

Syntax: FileExists (string)

Parameter
string
: An Alpha string that represents the file specification. The string may contain a path.

Returns: Boolean - True or False

Example: FileExists('c:\Temp\magic.ini') returns True if the Magic.ini file exists in the C:\Temp directory.

Condition Functions

Function

Description

Decode

Switches between various values according to a controlling expression (similar to the Magic xpa Case function).

Syntax :Decode (controlling expression, case1, value1, case2, value2,..., default value)

Parameters:
controlling expression
: The expression by which to switch between the various values.

case1: Possible values of the controlling expression

value1: Returned value when the controlling expression equals case 1.

case2,value1.: same as above

Returns: The value that is returned when the controlling expression equals the case value. A default value is returned if the controlling expression does not equal a case value.

Example: Decode (~n3~Var,5,’Magic1’ ,6,’aaa’ ,’bbb’)
If ~n3~Var=5 then it returns ‘Magic1’
If ~n3~Var=6 then it returns ‘aaa’
If ~n3~Var = something other than the above 2 values it returns ‘bbb’

If

Evaluates a Boolean expression and returns one value if True and another if False.

Syntax: If (controlling expression, value1, value 2)

Parameters:

controlling expressions: The expression by which to switch between the various values.

value1: Returns the TRUE value if controlling expressions is True, otherwise it returns FALSE.

Returns: Evaluates a Boolean expression and returns one value if True another if False.

Example: If (~n3~Var=5,True ,False)
If ~n3~Var=5 then it returns TRUE
Else it returns FALSE

Date Functions

Function

Description

AddDays

Adds a specified number of days to the date.

Syntax: AddDays (date, number)

Parameters:
date
: A Date variable.
number
: The number of days to add to the date.

Returns: A Date value.

Example: AddDays(~Int_date2_1901~Var,1) returns 03/03/1993 if ~Int_date2_1901~Var=03/02/1993

AddHours

Adds a specified number of hours to the time.

Syntax: AddHours (time, number)

Parameters:

time: The original time.

number: The number of hours to add to the time.

Returns: A Date value.

Example: AddMonth(~Int_date2_1901~Var,1) returns 04/02/1993 if ~Int_date2_1901~Var=03/02/1993

AddMinutes

Adds a specified number of minutes to the time.

Syntax: AddMinutes (time, number)

Parameters:
time
: The original time.
number
: The number of minutes to add to the time.

AddMonths

Adds a specified number of months to the date.

Syntaxs: AddMonths (date, number)

Parameters:
date
: A date.
number
: The number of months to add to the date.

Returns: A Date value.

Example: AddMonth(~Int_date2_1901~Var,1) returns 04/02/1993 if ~Int_date2_1901~Var=03/02/1993

AddSeconds

Adds a specified number of seconds to the time.

AddSeconds (time, number)

Parameters:
time
: The current time.
number
: The number of seconds to add to the time.

AddYears

Adds a specified number of years to the date.

Syntax: AddYears (date, number)

Parameters:
date
: A date.
number
: The number of years to add to the date.

Returns: A Date value.

Example: AddMonth(~Int_date2_1901~Var,7) returns 04/02/2000 if ~Int_date2_1901~Var=03/02/1993

BeginOfMonth

Returns the date of the start of the month specified in the parameter.

Syntax: BeginOfMonth (date)

Returns: The date value.

Example: BeginOfMonth (‘03/02/1993’) returns 03/01/2000

BeginOfQuarter

Returns the date of the quarter of the month specified in the parameter.

BeginOfQuarter (date)

A quarter is 3 months. The 1st quarter is 1-3 , the 2nd quarter is 4-6 , the 3rd quarter is 7-9, and the 4th quarter is 10-12.

Returns: The date value.

Example: BeginOfQuarter (‘10/30/2007’) returns ‘10/01/2007’

BeginOfYear

Returns the date of the start of the year specified in the parameter.

Syntax:BeginOfYear (date)

Returns: The date value.

Example: BeginOfYear (‘10/30/2007’) returns ‘01/01/2007’

DateNow

Returns the current system date.

Syntax: DateNow ()

Returns: The current system date

Example: DateNow() returns ‘10/08/2207’

Day

Returns the day portion of a date.

Syntax: Day (date)

Returns: The day portion of a date.

Example: Day (‘10/30/2007’) returns 30

DayOfWeek

Returns the name of the day of the week from a date.

Syntax: DayOfWeek (date)

Returns: The name of the day of the week from a date.

Example: DayOfWeek(‘12/30/2007’) returns ‘Sunday’

DayOfWeekHebrew

Returns the name in Hebrew of the day of the week from a date.

Syntax: DayOfWeekHebrew (date)

Returns: The name in Hebrew of the day of the week from a date.

Example: DayOfWeekHebrew(‘12/30/2007’) returns ‘Sunday’ in Hebrew

DaysBetween

Returns the number of days between two dates.

Syntax: DaysBetween (date1, date2)

Returns: the number of days between two dates.

Example: DaysBetween (‘10/30/2007’,’12/30/2007’) returns 61

DaysInMonth

Returns the number of days in a month.

Syntax: DaysInMonth (date)

Returns: The number of days in a month.

Example: DaysInMonth (10/30/2007) returns 31

EndOfMonth

Returns the date of the end of the month specified in the parameter.

Syntax: EndOfMonth (date)

Returns: Returns the date of the end of the month.

Example: EndOfMonth(‘10/30/2007’) returns 10/31/2007

EndOfQuater

Returns the date of the end of the quarter specified in the parameter.

Syntax: EndOfQuarter (date)

A quarter is 3 months. The 1st quarter is 1-3 , the 2nd quarter is 4-6 , the 3rd quarter is 7-9, and the 4th quarter is 10-12.

Returns :Date value. Returns End date of the quarter of the month

Example: EndOfQuarter (‘10/30/2007’) returns ‘12/31/2007’

EndOfYear

Returns the date of the end of the year specified in the parameter.

Syntax:EndOfYear (date)

Returns: The date of the end of the year.

Example: EndOfYear(‘10/30/2007’) returns 12/31/2007

FloorDay

Sets the time to zero on a given date time value.

Syntax: FloorDay (datetime)

The Report Generator always gets the date as a date+time. This function sets the time portion to 00:00:00 and leaves the date section as is.

FormatDate

Applies a selected format to a date value.

Syntax: FormatDate (date, current format, new format)

Example: FormatDate ('01/11/2007','DD/MM/YYYY','MMM’) returns ‘Nov’ (as for November)

Hour

Returns the hour portion of a time.

Syntax: Hour (time)

Returns: The hour portion of a time.

Example: Hour (’13:12:12’) returns 13

Minute

Returns the minutes portion of a time.

Minute (time)

Returns: The minutes portion of a time.

Example: Minute (’13:12:10’) returns 12

Month

Returns the month portion of a date.

Syntax: Month (date)

Returns: The month portion of a date.

Example: Month (‘10/30/2007’) returns 10

MonthsBetween

Returns the number of months between two dates.

Syntax: MonthsBetween (date1, date2)

Returns: The number of months between two dates.

Example: MonthsBetween (‘10/30/2007’,’12/30/2007’) returns 2

Seconds

Returns the seconds portion of a time.

Syntax: Seconds (time)

Returns: The seconds portion of a time.

Example: Seconds (’13:12:10’) returns 10

TimeNow

Returns the current system time.

Syntax: TimeNow ()

Returns: The current system time.

Example : TimeNow() returns 15:14:57.8934848

ToDate

Translates String to Date.

Syntax: ToDate (string)

Parameters:
string
: string of date

Returns: A date.

Example: ToDate(‘10/30/2007’) returns 10/30/2007

Year

Returns the year portion of a date.

Syntax: Year (date)

Returns: The year portion of a date.

Example: Year(‘10/30/2007’) returns 2007

Reference
Attachment 
Attachment