Functions
The data dictionary has the Functions category. This category contains the elements using which you can calculate a specific total or return the desired value. All elements of the Function category are divided into groups. The table below shows a list of functions and their brief description and examples.
Information |
Please note that when processing number values in reports, a data type of the result depends on a data type of arguments. In dashboards, all arguments are converted to the highest possible type. As a rule, it's either double or decimal. Accordingly, the result of function calculation will mostly have decimal or double data type.
|
View the Functions:
Function |
Description |
Sample |
Date: |
||
Calculates the distance between the specified dates |
{DateDiff(DateSerial(2022,1,30),DateSerial(2022,1,1))} - the result is 29.00:00:00, that means 29 days. |
|
Specifies date. |
{DateSerial(2022,1,30)} - the result is 1/30/2022 12:00:00 AM |
|
Shows a day from the specified date |
{Day(DateSerial(2022,1,30))} - the result is 30, since in arguments the January 30 2016 is specified |
|
Display a day of the week from a specified date in text form. Date (the DateTime type) |
{DayOfWeek(DateSerial(2022,1,30))} - the result is Sunday. |
|
Displays a day of the year |
{DayOfYear(DateSerial(2022,2,14))} - the result is 45, since February 14 is the 45th day of a year. |
|
Displays the number of days in the month: |
{DaysInMonth(DateSerial(2024,2,1))} - the result will be 29, because 2024 is a leap year and there are 29 days in February. |
|
Displays the number of days in a year: |
{DaysInYear(2024)} - the result will be 366 days, since 2024 is a leap year. |
|
Displays an hour: |
{Hour(DataSource.Column)} - an hour will be displayed from each value. For example, if time is 16:22:36, then the result is 16. |
|
Displays minutes: |
{Minute(DataSource.Column)} - minutes will be displayed from each value. For example, if time is 16:22:36, then the result is 22. |
|
Displays months: |
{Month(DateSerial(2022,12,1))} - the result will be 12, as the date is set on December 1, 2022. |
|
Displays the month name of the specified date The true or false value (the bool type), to display the result with a capital letter or with a small letter. |
{MonthName(DateSerial(2022,1,1))} - the result is January, because the 1 of January 2022 is set. |
|
Displays seconds: |
{Second(DataSource.Column)} - seconds will be displayed from each value. For example, if time is 16:22:36, then the result is 36. |
|
Displays time: Specifies hours, minutes, seconds in arguments (the long type) |
{TimeSerial(1,14,20)} - the result is 01:14: 20, 1 hour, 14 minutes, 20 seconds. |
|
Displays year: Specifies date in arguments (the DateTime type) |
{Year(DateSerial(2022,1,2))} - the result will be 2022, since the date is January 2, 2022. |
|
Math: |
||
Displays the absolute number. |
{Abs(-42)} - the result is 42 |
|
Displays the angle value in radians. |
{Acos(-1)} - the angle in radians will be calculated for the value cos = -1, i.e. the angle will be ~ 3.14. |
|
Displays the angle value in radians. |
{Asin(0)} - the angle in radians will be calculated for the value sin = 0, i.e. the angle is 0 |
|
Displays the angle value in radians. |
{Atan(-1)} - the angle in radians will be calculated for the value tan = -1, i.e. the angle will be ~ -0.79 |
|
Displays the maximum integer value for a specified number The value is specified in arguments (the double, decimal type) |
{Ceiling(25.124)} - It is worth noting that when this function is used, the number is not rounded. |
|
Calculates and displays the cos value: The value of the angle in radians is specified in arguments (the double type) |
{Cos(0)} - the result is 1. |
|
Displays the result of the division of one argument to another: The dividend and divisor and value that is the result, if the divisor is equal to 0.
Returns the value of the double, decimal, and long types |
{Div(2,1)} - the result is 2, because 2 / 1 = 2 |
|
Displays the result of rising to the specified degree the number e: The arguments indicate the degree to which the number e must rise (the long type) |
{Exp(4)} - the number e will be raised to the 4th degree. |
|
Displays the minimum integer value to the specified number: The value is specified in arguments (the double, decimal type) |
{Floor(123.59)} - the result will be 123 because this is the nearest minimum integer. It should be noted that this function does not round numbers. |
|
Calculates the natural logarithm: The value is specified in arguments (the double type) |
{Log(x)}, where x is a number or an expression, the result is a calculation of the natural logarithm. |
|
Compares the two values and displays the maximum: Two values are specified in arguments (the long, decimal, double type) |
{Maximum(5,9)} - the result is 9.
|
|
Compares the two values and displays the minimum: Two values are specified in arguments (the long, decimal, double type) |
{Minimum(5,9)} - the result is 5. |
|
Rounds up the value to an integer or up to the certain number of decimal: In arguments, the following is specified: Number of characters to which the fractional part should be rounded (the int type) |
{Round(7.56)} - the result is 8 |
|
Displays an indicator. For positive numbers 1, 0 - for all zero values, -1 - for negative values: The value is specified in arguments (the long, decimal, double types). |
{Sign(256)} - the result is 1. |
|
Calculates sin of an angle: The value of an angle in radians is specified in arguments (the double type). |
{Sin(0)} - the result is 0. |
|
Calculates the square root of the number: |
{Sqrt(4)} - the result will be 2 because the square root of 4 is 2. |
|
Calculates tg of an angle: The value of an angle in radians is specified in arguments (the double type). |
{Tan(90)} - the result is ~ -1.995 |
|
Displays only the integer part without rounding: |
{Truncate(Sqrt(5))} - the result will be number 2 because the square root of 5 is ~ 2.236. The whole part in this number is 2. {Truncate(DataSource.Column1)} - only the integer part of all Column1 values will be displayed. |
|
Print State: |
||
Identifies null values in the specified data column. If there is a null value, the result is true, otherwise - false. In arguments, the following is specified: |
{IsNull(DataSource.Column)} - in the rendered report, instead of null values, the true values will be output, and instead of other values, false values will be shown. |
|
Displays the value from the next line. If the value of the next line is null, the result is 0. The data source is specified in arguments (the object type) and a column name (the string type). |
For example, the Column column contains values 2, 5, 9. Then, using the function {Next(DataSource, "Column")}, the first value will be 5, the second 9, and the third will be null. |
|
Compares the value of the string with the value of the next line. If the value of the next line is 0 or null, the result is true, otherwise - false. In arguments, the following is specified: The data source (the object type) |
For example, the Column data column contains the values 2, 0, 9. Then, using the function {NextIsNull(DataSource, "Column")}, the first value is true; the second is false; the third is true. |
|
Displays the value from the previous line. If the value of the next line is null, the result is 0. In arguments, the following is specified: The data source (the object type) |
For example, the Column column contains values 2, 5, 9. Then, using the function {Previous(DataSource, "Column")}, the first value will be null, the second value will be 2, the third value will be 5. |
|
Compares the value of the string with the value of the previous row. If the value of the previous line is 0 or null, the result is true, otherwise - false. In arguments, the following is specified: |
For example, the Column data column contains the values 2, 9, 0. Then, using the function {PreviousIsNull (DataSource, "Column")}, the first value is true; the second is false; the third is false. |
|
Programming Shortcut: |
||
Displays the value by index. The arguments specify the index and values. |
All product groups are grouped by category: expensive goods, medium price goods, cheap goods. An index is assigned to each group: expensive - index 1, average - index 2, cheap - index 3. The report should be displayed instead of their index - category. In this case, you can use the Choose function.
{Choose(DataSource.Column1, "expensive", "average", "cheap")} - instead of index 1, the value expensive will be displayed, instead of index 2 - average, instead of index 3 - cheap. |
|
Used to display a particular value, depending on the condition: In arguments, the condition is specified, the value if the condition is true (true) and the value if the condition is false (false) |
In the inventory report, you need to track the number of items. The logistician's task is that, when the quantity of goods is coming to 0 (less than 6), it is necessary to order these goods. To highlight critical positions in the report visually, you can use the function {IIF (,,)}
{IIF(DataSource.Column1 > 6,"Minimum","Normal")}, |
|
Assigns the specified value when the specified condition is complete: |
For example, a list of employees is displayed in the report, and you need to display their position: Nancy is the lead project manager, Andrew is the chief developer, the remaining employees (6 people) are Juniors. In this case, the Switch function will have three pairs of "condition-value" arguments: {Switch(Employees.FirstName == "Nancy", "Manager", Employees.FirstName == "Andrew", "Developer", Employees.FirstName! = "", "Junior" )} |
|
Strings: |
||
Converts these numbers to Arabic numerals: |
{Arabic(2)} - the number 2 will have an Arabic spelling. |
|
Converts date to text value: |
{DateToStr(DataSource.Column1)} - all dates from Column1 will be displayed in text form. |
|
Inserts a value after a certain character into another value: In arguments, the following is specified: The number of a character, after which the value is inserted (the int type), The value for insertion (the string type) |
{Insert("25",2," dollars")} - in the value 25, after the second symbol, the value dollars will be inserted, i.e. the result will be 25 dollars. |
|
Displays the specified number of characters from the left side of the value: |
{Left("Beverages", 4)} - only four characters from the Beverages value will be displayed, the result will be Beve. |
|
Displays the number of characters for the specified value: The value is specified in arguments (the string type) |
{Length("Beverages")} - the result will be number 9 because the value Beverages consists of nine characters. |
|
Displays characters from a value. In this case, you can set the reference position: Index of the reference position (the int type) Number of characters to display (the int type) |
{Mid("Beverages",2,3)} - three symbols will be displayed after the first two, the result will be ver. |
|
Converts specified numbers to numbers in Persian: |
{Persian(5)} - number 2 will have Persian spelling. |
|
Deletes the specified number of characters from the index of a specific position: Index of the reference position (the int type) Number of characters to delete (the int type) |
{Remove("Beverages",2,3)} - after the second character, three characters will be deleted, the result is Beages. |
|
Replaces certain characters or their combination with other characters: Characters to be replaced (the string type) Characters to be inserted (the string type) |
{Replace("Beverages","ver","NEW")} - in the value Beverages, the ver characters will be replaced by the characters NEW, the result is BeNEWages. |
|
Displays the specified number of characters from the right side of the value: |
{Right("Beverages",3)} - three characters from the right side of the value will be displayed, ges. |
|
Converts Arabic numerals to Roman numerals: |
{Roman(4)} - the number 4 will have a Roman spelling. |
|
Displays a certain number of characters from the specified position: The index of position (the int type), how many characters are skipped Number of characters to display (the int type) |
{Substring("Beverages",6,3)} - the first six characters are skipped and three characters will be displayed, the result is ges. |
|
Displays the currency value as the text. Argument (true or false) to display text with a capital letter; Argument (true or false) to display cents; Single and plural formats for currency and cents (the string type); You can also specify a base unit for the integer part and a fractional.
In addition, various combinations of arguments are possible. There are also some types of this function that support different cultures. Pay attention to you can specify the currencies ISO code (the string type). |
{ToCurrencyWords(100)} - the used currency is dollars of the USA, so that the result will be: "One hundred dollars and zero cents. {ToCurrencyWords(100, false)} - the result will be displayed without displaying cents (since it is set to true), the result will be: "One hundred dollars".
{ToCurrencyWords(100,false,true)} - the result will be displayed with the first lowercase letter (since it is set to false) and with displaying cents (since it is set to true), the result will be: "one hundred dollars and zero cents".
{ToCurrencyWords(125.9,true,true,"currency","cent name")} - in this case, the result will be displayed with the first uppercase letter (since it is set to true) and with displaying cents (since it is set to true). Also, we defined the basic unit as "currency", and the fractional unit as "cent name". The result will be: "One hundred and twenty-five currency and ninety cent name".
{ToCurrencyWordsEnIn("dollars","cents",1.25M,0,true)} - the base unit for the integer part as dollars will be specified, the fractional part - cents, the number for conversion 1.25, then the number of decimal signs to convert and the value true means that the entry will start with the capital letter. |
|
Displays the value in lowercase: |
{ToLowerCase("EURO")} - the result is euro. |
|
Converts numerals to ordinal: |
{ToOrdinal(25)} - - the result is 25th. |
|
Converts the text to the format - the first character is capital, the rest characters are in lowercase: |
{ToProperCase("dOllars")} - - the result is Dollars. |
|
Displays the value in uppercase: |
{ToUpperCase("dollars")} - the result is DOLLARS. |
|
Displays the numerals as text: A numeric value that will be converted to text (decimal, double, long) |
{ToWords(100)} - the result is one hundred. |
|
Trims the spaces at the beginning or end of the line: |
{Trim(" <1 dollars> ")} - the result in this case is <1 dollars>". |
|
Checks the value for conversion to decimal, double, long: |
{TryParseLong("100")} - The value can be converted to long. |
|
|