Enhancing Reports with Data Functions¶
Reports often benefit from calculations and data transformations to provide clearer insights. This can be achieved using functions that perform various operations on your report data. Here's a breakdown of some common function categories provided via IFS Report Studio:
- Aggregate Functions: These functions summarize large datasets into concise values. Examples include
SUM
(total),AVERAGE
(mean),MIN
(smallest value),MAX
(largest value), andCOUNT
(number of items). - Date and Time Functions: These functions work with date and time data, allowing for calculations, formatting, and extractions.
- Logical functions: allow you to create conditional expressions within your reports. They evaluate conditions as TRUE or FALSE and perform actions based on the outcome.
- Math Functions: These functions perform mathematical calculations on numeric data. Examples include advanced functions like
SQR
(square root),ROUND
(round to a specific number of decimals), andPOWER
(raise a number to a power). - Reporting Functions: Custom functions designed to simplify common reporting tasks within IFS Report Studio. These functions could encapsulate complex calculations or data manipulations specific to the report generation process.
- String Functions: These functions manipulate text data for formatting, cleaning, or extracting specific information. Examples include
UPPER
(convert to uppercase),LOWER
(convert to lowercase),TRIM
(remove leading/trailing spaces),SUBSTRING
(extract a portion of a string), andCONCAT
(join multiple strings).
By understanding and applying these functions, you can significantly enhance the informativeness and clarity of your reports, transforming raw data into actionable insights.
Aggregate Functions:¶
Function | Description | Example |
---|---|---|
Avg(Value) | Evaluates the average of the values in the collection. | [Products].Avg([UnitPrice]) |
Count() | Performs collection size calculation, returning the number of elements present. | [People].Count() |
Exists() | Evaluates whether the object is an element of the collection | [Products][UnitPrice] == 70].Exists() |
Join() | Concatenates all Expression values in the Collection based on the specified Condition (optional) into a single string separated by the specified Separator (optional). If you do not specify a Separator, the function uses a comma. The function has the following overloads: [Collection][Condition].Join(Expression) [Collection][Condition].Join(Expression, Separator) | The following expression concatenates CompanyName field values within a report grouped by the CategoryID field into a single string separated by a semicolon [[CategoryID] == [^.CategoryID]].Join([CompanyName], ';') |
Max(Value) | Performs maximum value aggregation on the collection, considering the expression values of each element | [Marks].Max([Student]) |
Min(Value) | Performs minimum value aggregation on the collection, considering the expression values of each element | [Marks].Min([Student]) |
Single() | Performs single-element retrieval from a collection, returning the object if it's the only one present. | [Group].Single() is not null |
Single(Expression) | Provide expression as a parameter: [Group][Condition].Single(Expression). This function returns the Expression if the Group contains only one object that meets the specified Condition (optional). | [Group].Single([Property1]) - returns the found object's property value. |
Sum(Value) | Performs aggregation on the collection, returning the sum of all the expression values. | [Products].Sum([UnitsInStock]) |
String Functions¶
Function | Description | Example |
---|---|---|
Ascii(String) | Extracts the ASCII code of the first character | Ascii('d') |
Char(Number) | Decodes an ASCII integer into a character | Char(65) + Char(51) |
CharIndex(String1, String2) | Determines the zero-based index of the first character where String1 appears within String2. | CharIndex('e', 'reportstudio') |
CharIndex(String1, String2, StartLocation) | Determines the zero-based index of the first character where String1 appears within String2, starting the search from the specified StartLocation. | CharIndex('e', 'reportstudio', 2) |
Concat(String1, ... , StringN) | Performs string concatenation, returning a new string formed by joining the current string with any supplied strings | Concat('A', ')', [Product]) |
Contains(String1, SubString1) | Performs a substring search within String1, returning True if SubString1 is found, False otherwise. | Contains([ProductName], 'drinks') |
EndsWith(String1, SubString1) | Performs a substring comparison at the end of String1, returning True if it matches SubString1, otherwise False. | EndsWith([Summary], 'Thankyou') |
Insert(String1, StartPosition, String2) | Performs string insertion, integrating String2 into String1 at the specified StartPositon. | Insert([ID], 0, 'NAME') |
Len(Value) | Determines the string length (number of characters) or the memory size (in bytes) required to store a variable. | Len([Summary]) |
Lower(String) | Provides String in lowercase. | Lower([Address]) |
PadLeft(String, Length) | Performs left-alignment on a string, adding whitespace characters to the left side until the specified total length is achieved. | PadLeft([Address], 30) |
PadLeft(String, Length, Char) | Performs left-alignment on a string, adding the specified character to the left side until the specified total length is achieved. | PadLeft([Address], 25, '>') |
PadRight(String, Length) | Performs right-alignment on a string, adding whitespace characters to the left side until the specified total length is achieved. | PadRight([Address], 25) |
PadRight(String, Length, Char) | Performs right-alignment on a string, adding the specified character to the left side until the specified total length is achieved. | PadRight([Address], 25, '>') |
Remove(String, StartPosition) | Performs string truncation, removing all characters from the specified position to the end. | Remove([Address], 10) |
Remove(String, StartPosition, Length) | Performs string slicing, removing a specified number of characters from a designated index within the string. | Remove([Address], 0, 10) |
Replace(String1, SubString2, String3) | Performs string substitution, generating a new string where all instances of SubString2 in String1 are replaced with String3. | Replace([Age], 'The ', '') |
Reverse(String) | Performs string reversal, rearranging the characters within the string in reverse order. | Reverse([Address]) |
StartsWith(String1, SubString1) | Performs a substring comparison at the beginning of String1, returning True if it matches SubString1, False otherwise. | StartsWith([Description], 'Hi') |
Substring(String, StartPosition, Length) | Performs string slicing, extracting a substring of a specified length from the string, starting at the designated StartPosition. | Substring([Summary], 2, 3) |
Substring(String, StartPosition) | Performs string slicing, extracting a substring from the designated StartPosition to the end of the string. | Substring([Summary], 2) |
ToStr(Value) | Implements the str method to generate a string representation of an object. | ToStr([Name]) |
Trim(String) | Performs string stripping, removing all whitespace characters (including spaces) from the beginning and end of the string. | Trim([FullName]) |
Upper(String) | Performs uppercase conversion on the string, resulting in all characters being uppercase. | Upper([FulltName]) |
StringCase(int index, string list) | Extracts and returns one string from a comma-delimited list of strings, based on the value given in the case. | case: Any positive number including zero (0). list: A string composed of comma-delimited sub-strings. For example: 'dog, cat, horse, mouse, man'. |
StringEqualPad(string, length, char) | Maintains the string length at the specified size by either truncating the string or adding the specified characters to both sides of the string equally. | StringEqualPad(JOE,6,'') In this example, the StringEqualPad would return 'JOE'. The function would pad the contents of the input item JOE with '' characters equally to the left and right of the string to make it 9 characters in length |
StringLeft(string, length) | Extracts and returns the specified number of characters, starting the extraction from the left side of the input string. | StringLeft('Good Morning',4) Returns a string value 'Good'. |
StringMid(string, start, length) | Returns a substring of the input string, starting at the supplied starting point and of the specified length. | StringMid('who, what, when, where, why',4,4) In this example, the StringMid function would return the string 'what'. |
StringProper(string str) | Formats each word in the string so that it starts with a capital letter and is followed by lower case letters. | StringProper('title page') In this example, the StringProper function capitalizes the first letter of each word in the string. The resulting output appears as 'Title Page'. |
StringRepeat(string str) | This function takes the string you entered and repeats it the specified number of times. If you want one or more spaces between each repetition of the value, then you must be sure to include those spaces in the string that you enter. | StringRepeat('Line',4) In this example, the StringRepeat function outputs a string that looks like “LineLineLineLine”. |
StringReplace(string original, int start, int length, string replacement) | Returns original where the characters starting at start are replaced with length number of characters from replacement. | StringReplace('your property',0,4,' our ') In this example, the StringReplace function changes 'your property' to 'our property'. |
StringScan(string str, string part) | This function searches for a specified character or characters and returns its location (offset) in the input string starting at 0. If it does not find the part, the function returns a value of -1. | StringScan('JohnMaryBethDavid',' Beth') In this example, the StringScan function would return the number 8, showing the offset of the first occurrence of the part 'Beth'. |
StringSpaces(string str, string part) | Returns a string with the specified number of blanks. | StringSpaces(5) returns the string ‘ ’ (a string consisting of 5 blanks / white spaces). |
StringTranslate(string str, string part) | Finds the value specified by type in list1. The function takes the position of the item in list1 and finds and returns the item in list2 that is in the same position. | StringTranslate (region, 'N,S, W,E', 'North, South, West, East') In this example, the region is an input item with a data type of string. The StringTranslate function returns one of the following depending on the value of type. type substring returned N North S South W West E East |
StrIFF(value,string1,string2,string3) | Returns a specific string based on the value provided by the value parameter. You can specify a maximum of three strings. 1. value: A number value you specify, that identifies the string to be returned. 2. string1: A character value you specify. 3. string2: A character value you specify. 4. string3: A character value you specify. Returns if: value < 0 string1 is returned value = 0 string2 is returned value > 0 string3 is returned |
StrIFF(2, 'name', 'address', 'phone') In this example, the function would return 'phone' as the output. |
Math Functions¶
Function | Description | Example |
---|---|---|
Abs(Value) | Provides the given numeric expression's absolute, positive value. | Abs(1 - [Discount]) |
Acos(Value) | Provides a number's arccosine (the angle in radians, whose cosine is the given float expression). | Acos([Value]) |
Asin(Value) | Provides a number's arcsine (the angle in radians, whose sine is the given float expression). | Asin([Value]) |
Atn(Value) | Provides a number's arctangent (the angle in radians, whose tangent is the given float expression). | Atn([Value]) |
Atn2(Value1, Value2) | Provides the angle whose tangent is the quotient of two specified numbers in radians. | Atn2([Value1], [Value2]) |
BigMul(Value1, Value2) | Provides an Int64 containing the full product of two specified 32-bit numbers. | BigMul([Volume], [Quantity]) |
Ceiling(Value) | Provides the smallest integer that is greater than or equal to the numeric expression. | Ceiling([Value]) |
Cos(Value) | Provides the angle's cosine, in radians. | Cos([Value]) |
Cosh(Value) | Provides the angle's hyperbolic cosine, in radians. | Cosh([Value]) |
Exp(Value) | Provides the float expression's exponential value. | Exp([Value]) |
Floor(Value) | Provides the largest integer less than or equal to the numeric expression. | Floor([Value]) |
Log(Value) | Provides a specified number's natural logarithm. | Log([Value]) |
Log(Value, Base) | Provides the logarithm of a specified number in a specified Base. | Log([Value], 2) |
Log10(Value) | Provides a specified number's base 10 logarithm. | Log10([Value]) |
Max(Value1, Value2) | Provides the maximum value from the specified values. | Max([Value1], [Value2]) |
Min(Value1, Value2) | Provides the minimum value from the specified values. | Min([Value1], [Value2]) |
Power(Value, Power) | Provides a specified number raised to a specified power. | Power([Value], 3) |
Rnd() | Provides a random number that is less than 1, but greater than or equal to zero. | Rnd()*100 |
Round(Value) | Rounds the given value to the nearest integer. | Round([Value]) |
Round(Value, Precision) | Rounds the given value to the nearest integer, or to a specified number of decimal places. | Round([Value], 2) |
Sign(Value) | Provides the positive (+1), zero (0), or negative (-1) sign of the given expression. | Sign([Value]) |
Sin(Value) | Provides the sine of the angle defined in radians. | Sin([Value]) |
Sinh(Value) | Provides the hyperbolic sine of the angle defined in radians. | Sinh([Value]) |
Sqr(Value) | Provides the square root of a given number. | Sqr([Value]) |
Tan(Value) | Provides the tangent of the angle defined in radians. | Tan([Value]) |
Tanh(Value) | Provides the hyperbolic tangent of the angle defined in radians. | Tanh([Value]) |
ToDecimal(Value) | turnss Value to an equivalent decimal number. | ToDecimal([Value]) |
ToDouble(Value) | turns Value to an equivalent 64-bit double-precision floating-point number. | ToDouble([Value]) |
ToFloat(Value) | turns Value to an equivalent 32-bit single-precision floating-point number. | ToFloat([Value]) |
ToInt(Value) | turns Value to an equivalent 32-bit signed integer. | ToInt([Value]) |
ToLong(Value) | turns Value to an equivalent 64-bit signed integer. | ToLong([Value]) |
NumberTruncate(number,precision) | Truncates the input number at the specified number of decimal places. No rounding of values occurs. | NumberTruncate(89432.563,1) The function Provides a number value that prints as 89432.5. This function does NOT round the number, it only truncates the number. |
Date/Time Functions¶
Function | Description | Example |
---|---|---|
AddDays(DateTime, DaysCount) | Provides a date-time value that is the specified number of days from the specified DateTime. | AddDays([ExecutedDate], 30) |
AddHours(DateTime, HoursCount) | Provides a date-time value that is the specified number of hours from the specified DateTime. | AddHours([OrderTime], 2) |
AddMilliSeconds(DateTime, MilliSecondsCount) | Provides a date-time value that is the specified number of milliseconds from the specified DateTime. | AddMilliSeconds(([OrderTime], 5000)) |
AddMinutes(DateTime, MinutesCount) | Provides a date-time value that is the specified number of minutes from the specified DateTime. | AddMinutes([OrderTime], 30) |
AddMonths(DateTime, MonthsCount) | Provides a date-time value that is the specified number of months from the specified DateTime. | AddMonths([ExecutedDate], 1) |
AddSeconds(DateTime, SecondsCount) | Provides a date-time value that is the specified number of seconds from the specified DateTime. | AddSeconds([OrderTime], 60) |
AddTicks(DateTime, TicksCount) | Provides a date-time value that is the specified number of ticks from the specified DateTime. | AddTicks([OrderTime], 5000) |
AddTimeSpan(DateTime, TimeSpan) | Provides a date-time value that is from the specified DateTime for the given TimeSpan. | AddTimeSpan([OrderTime], [Duration]) |
AddYears(DateTime, YearsCount) | Provides a date-time value that is the specified number of years from the specified DateTime. | AddYears([OpenDate], -1) |
DateDiffDay(startDate, endDate) | Provides the number of day boundaries between two non-nullable dates. | DateDiffDay([OrderTime], Now()) |
DateDiffHour(startDate, endDate) | Provides the number of hour boundaries between two non-nullable dates. | DateDiffHour([OrderTime], Now()) |
DateDiffMilliSecond(startDate, endDate) | Provides the number of millisecond boundaries between two non-nullable dates. | DateDiffMilliSecond([OrderTime], Now()) |
DateDiffMinute(startDate, endDate) | Provides the number of minute boundaries between two non-nullable dates. | DateDiffMinute([OrderTime], Now()) |
DateDiffMonth(startDate, endDate) | Provides the number of month boundaries between two non-nullable dates. | DateDiffMonth([OrderTime], Now()) |
DateDiffSecond(startDate, endDate) | Provides the number of second boundaries between two non-nullable dates. | DateDiffSecond([OrderTime], Now()) |
DateDiffTick(startDate, endDate) | Provides the number of tick boundaries between two non-nullable dates. | DateDiffTick([OrderTime], Now()) |
DateDiffYear(startDate, endDate) | Provides the number of year boundaries between two non-nullable dates. | DateDiffYear([OrderTime], Now()) |
DateTimeFromParts(Year, Month, Day, Hour, Minute, Second, Millisecond) | Provides a date value constructed from the specified Year, Month, Day, Hour, Minute, Second, and Millisecond. | DateTimeFromParts(2023, 5, 7, 31) |
GetDate(DateTime) | Bring out a date from the defined DateTime. | GetDate([ExecutedDateTime]) |
GetDay(DateTime) | Bring out a day from the defined DateTime. | GetDay([ExecutedDate) |
GetDayOfWeek(DateTime) | Bring out a day of the week from the defined DateTime. | GetDayOfWeek([ExecutedDate]) |
GetDayOfYear(DateTime) | Bring out a day of the year from the defined DateTime. | GetDayOfYear([ExecutedDat]) |
GetHour(DateTime) | Bring out an hour from the defined DateTime. | GetHour([OrderTime]) |
GetMilliSecond(DateTime) | Bring out milliseconds from the defined DateTime. | GetMilliSecond([OrderTime]) |
GetMinute(DateTime) | Bring out minutes from the defined DateTime. | GetMinute([OrderTime]) |
GetMonth(DateTime) | Bring out a month from the defined DateTime. | GetMonth([OrderTime]) |
GetSecond(DateTime) | Bring out seconds from the defined DateTime. | GetSecond([OrderTime]) |
GetTimeOfDay(DateTime) | Bring out the time of day from the defined DateTime in ticks. | GetTimeOfDay([OrderTime]) |
GetYear(DateTime) | Bring out a year from the defined DateTime. | GetYear([OrserTime]) |
IsApril(DateTime) | Provides True if the specified date falls within April. | IsApril([ExecutedDate]) |
IsAugust(DateTime) | Provides True if the specified date falls within August. | IsAugust([ExecutedDate]) |
IsDecember(DateTime) | Provides True if the specified date falls within December. | IsDecember([ExecutedDatDate]) |
IsFebruary(DateTime) | Provides True if the specified date falls within February. | IsFebruary([ExecutedDatDate]) |
IsJanuary(DateTime) | Provides True if the specified date falls within January. | IsJanuary([ExecutedDatDate]) |
IsJuly(DateTime) | Provides True if the specified date falls within July. | IsJuly([ExecutedDatDate]) |
IsJune(DateTime) | Provides True if the specified date falls within June. | IsJune([ExecutedDatDate]) |
IsLastMonth(DateTime) | Provides True if the specified date falls within the previous month. | IsLastMonth([ExecutedDatDate]) |
IsLastYear(DateTime) | Provides True if the specified date falls within the previous year. | IsLastYear([ExecutedDate]) |
IsMarch(DateTime) | Provides True if the specified date falls within March. | IsMarch([ExecutedDate]) |
IsMay(DateTime) | Provides True if the specified date falls within May. | IsMay([ExecutedDate]) |
IsNextMonth(DateTime) | Provides True if the specified date falls within the next month. | IsNextMonth([ExecutedDate]) |
IsNextYear(DateTime) | Provides True if the specified date falls within the next year. | IsNextYear([ExecutedDate]) |
IsNovember(DateTime) | Provides True if the specified date falls within November. | IsNovember([ExecutedDate]) |
IsOctober(DateTime) | Provides True if the specified date falls within October. | IsOctober([ExecutedDate]) |
IsSameDay(DateTime) | Provides True if the specified date/time values fall within the same day. | IsSameDay([ExecutedDate]) |
IsSeptember(DateTime) | Provides True if the specified date falls within September. | IsSeptember([ExecutedDate]) |
IsThisMonth(DateTime) | Provides True if the specified date falls within the current month. | IsThisMonth([ExecutedDate]) |
IsThisWeek(DateTime) | Provides True if the specified date falls within the current week. | IsThisWeek([ExecutedDate]) |
IsYearToDate(DateTime) | Provides True if the specified date falls within the year-to-date period. This period starts from the first day of the current year and continues to the current date (including the current date). | IsYearToDate([ExecutedDate]) |
IsThisYear(DateTime) | Provides True if the specified date falls within the current year. | IsThisYear([ExecutedDate]) |
LocalDateTimeDayAfterTomorrow() | Provides a date-time value corresponding to the day after Tomorrow. | AddDays(LocalDateTimeDayAfterTomorrow(), 7) |
LocalDateTimeLastMonth() | Provides a DateTime value corresponding to the first day of the previous month. | AddMonths(LocalDateTimeLastMonth(), 7) |
LocalDateTimeLastWeek() | Provides a date-time value corresponding to the first day of the previous week. | AddDays(LocalDateTimeLastWeek(), 5) |
LocalDateTimeLastYear() | Provides a DateTime value corresponding to the first day of the previous year. | AddYears(LocalDateTimeLastYear(), 5) |
LocalDateTimeNextMonth() | Provides a date-time value corresponding to the first day of the next month. | AddMonths(LocalDateTimeNextMonth(), 5) |
LocalDateTimeNextWeek() | Provides a date-time value corresponding to the first day of the following week. | AddDays(LocalDateTimeNextWeek(), 5) |
LocalDateTimeNextYear() | Provides a date-time value corresponding to the first day of the following year. | AddYears(LocalDateTimeNextYear(), 5) |
LocalDateTimeNow() | Provides a date-time value corresponding to the current moment in time. | AddDays(LocalDateTimeNow(), 5) |
LocalDateTimeThisMonth() | Provides a date-time value corresponding to the first day of the current month. | AddMonths(LocalDateTimeThisMonth(), 5) |
LocalDateTimeThisWeek() | Provides a date-time value corresponding to the first day of the current week. | AddDays(LocalDateTimeThisWeek(), 5) |
LocalDateTimeThisYear() | Provides a date-time value corresponding to the first day of the current year. | AddYears(LocalDateTimeThisYear(), 5) |
LocalDateTimeToday() | Provides a date-time value corresponding to Today. | AddDays(LocalDateTimeToday(), 5) |
LocalDateTimeTomorrow() | Provides a date-time value corresponding to Tomorrow. | AddDays(LocalDateTimeTomorrow(), 5) |
LocalDateTimeTwoMonthsAway() | Provides a DateTime value corresponding to the first day of the following month. | AddMonths(LocalDateTimeTwoMonthAway(), 5) |
LocalDateTimeTwoWeeksAway() | Provides a DateTime value corresponding to the first day of the following week. | AddDays(LocalDateTimeTwoWeeksAway(), 5) |
LocalDateTimeTwoYearsAway() | Provides a DateTime value corresponding to the first day of the following year. | AddYears(LocalDateTimeTwoYearsAway(), 5) |
LocalDateTimeYearBeforeToday() | Provides a DateTime value corresponding to the same date one year ago. | AddYears(LocalDateTimeYearBeforeToday(), 5) |
LocalDateTimeYesterday() | Provides a date-time value corresponding to Yesterday. | AddDays(LocalDateTimeYesterday(), 5) |
Now() | Provides the current system date and time. | AddDays(Now(), 5) |
Today() | Provides the current date. Regardless of the actual time, this function Provides midnight of the current date. | AddMonths(Today(), 1) |
UtcNow() | Provides the current system date and time, expressed as Coordinated Universal Time (UTC). | AddDays(UtcNow(), 7) |
Logical Functions¶
-
Functions Description Example Iif(Expression1, True_Value1, ..., ExpressionN, True_ValueN, False_Value) This function implements a conditional expression structure, taking 2N+1 arguments and returning the value associated with the first true logical expression evaluation. (N - the number of specified logical expressions): -Each odd argument specifies a logical expression. -Each even argument specifies the value that is returned if the previous expression evaluates to True. -The last argument specifies the value that is returned if the previously evaluated logical expressions yielded False. Iif(Address = 'Add1', 1, Address = 'Add2', 2, Address = 'Add3', 3, 4)") IsNull(Value) Returns True if the specified Value is NULL. IsNull([ExecuredDate]) IsNull(Value1, Value2) Returns Value1 if it is not set to NULL; otherwise, Value2 is returned. IsNull([OrderDate], [ShipmentDate]) IsNullOrEmpty(String) Returns True if the specified String object is NULL or an empty string; otherwise, False is returned. IsNullOrEmpty([OrderName])
Reporting Functions¶
Function | Description | Example |
---|---|---|
Argb(Alpha, Red, Green, Blue) | Constructs a color string in RGBA format, specifying the alpha (transparency) and individual red, green, and blue channel values. | Argb(1,200, 30, 200) / Result: '1,200,30,200'/ |
GetDisplayText(?parameterName) | Performs parameter value display text retrieval, utilizing a lookup dictionary if available, otherwise converting the value to a string representation. | /OrderParameter stores static or dynamic predefined values where OrderID is a parameter value and OrderName is a display text. / GetDisplayText(?employeeParameter) |
Rgb(Red, Green, Blue) | Constructs a color string in RGB format, specifying the individual red, green, and blue channel values. | Rgb(30,200,150) / Result: '30,200,150' / |