Formula designer in Electronic reporting

This topic explains how to use the formula designer in Electronic reporting (ER). When you design a format for a specific electronic document in ER, you can use Microsoft Excel–like formulas for data transformation to meet the requirements for that document's fulfillment and formatting. Various types of functions are supported - text, date and time, mathematical, logical, information, data type conversion, and other (business domain–specific functions).

Formula designer overview

Electronic reporting (ER) supports the formula designer. Therefore, at design time, you can configure expressions that can be used for the following tasks at run time:

  • Transforming data that is received from a Microsoft Dynamics 365 for Finance and Operations database, and that should be populated in an ER data model that is designed to be a data source for ER formats (filtering, grouping, data type conversion, etc.).
  • Formatting data that must be sent to a generating electronic document in accordance with the layout and conditions of a specific ER format (in accordance with the requested language or culture, encoding, etc.).
  • Controlling the process of electronic document generation (enabling/disabling output of specific elements of the format, depending on processing data, interrupting document creation, throwing messages for end users, etc.).

The formula designer page can be opened when you do any of the following:

  • Bind data source items to data model components.
  • Bind data source items to format components.
  • Complete maintenance of calculated fields as part of data sources.
  • Define the visibility conditions for user input parameters.
  • Design a format's transformations.
  • Define the enabling conditions for the format's components.
  • Define the file names for the format's FILE components.
  • Define the conditions for process control validations.
  • Define the message text for process control validations.

Designing ER formulas

Data binding

The ER formula designer can be used to define an expression that transforms data that is received from data sources, so that the data can be populated in the data consumer at run time:

  • From Finance and Operations data sources and run-time parameters to an ER data model.
  • From an ER data model to an ER format.
  • From Finance and Operations data sources and run-time parameters to an ER format.

The following illustration shows the design of an expression of this type. In this example, the expression returns the value of the Intrastat.AmountMST field of the Finance and Operations Intrastat table, after that value has been rounded to two decimal places. picture-expression-binding The following illustration shows how an expression of this type can be used. In this example, the result of the designed expression is populated in the Transaction.InvoicedAmount component of the Tax reporting model data model. picture-expression-binding2 At run time, the designed formula, ROUND (Intrastat.AmountMST, 2), will round the value of the AmountMST field for each record of the Intrastat table to two decimal places, and populate the rounded value to the Transaction.InvoicedAmount component of the Tax reporting data model.

Data formatting

The ER formula designer can be used to define an expression that formats data that is received from data sources, so that the data can be sent as part of the generating electronic document. If you have formatting that must be applied as a typical rule that should be reused for a format, you can introduce that formatting one time in a format configuration as a named transformation that has a formatting expression. This named transformation can then be linked with many format components for which the output must be formatted according to the expression that was created. The following illustration shows the design of a transformation of this type. In this example, the TrimmedString transformation takes incoming data of the String data type, and truncates leading and trailing spaces when it returns the string value. picture-transformation-design The following illustration shows how a transformation of this type can be used. In this example, several format components that send text as output to the generating electronic document at run time refer to the TrimmedString transformation by name. picture-transformation-usage When format components refer to the TrimmedString **transformation (for example, the **partyName component in the preceding illustration) this sends text as output to the generating document. The text does not include leading and trailing spaces. If you have formatting that must be applied individually, you can introduce that formatting as an individual expression of a binding of a specific format component. The following illustration shows an expression of this type. In this example, the partyType format component is bound to the data source via an expression that converts incoming data from the Model.Company.RegistrationType field in the data source to uppercase text and sends that text as output to the electronic document. picture-binding-with-formula

Process flow control

The ER formula designer can be used to define expressions that control the process flow of generating documents. You can:

  • Define conditions that determine when a document creation process must be stopped.
  • Specify expressions that either create messages for the end user about stopped processes or throw execution log messages about the continuing process of report generation.
  • Specify the file names of generating documents, and control conditions of their creation.

Each rule of the process flow control is designed as an individual validation. The following illustration shows a validation of this type. Here is an explanation of the configuration in this example:

  • The validation is evaluated when the INSTAT node is created in the generating XML file.
  • If the list of transactions is empty, the validation stops the execution process and returns FALSE.
  • The validation returns an error message that includes the text of label SYS70894 in the user's preferred language.

picture-validation The ER formula designer can also be used to specify a file name for a generating electronic document and control the file creation process. The following illustration shows the design of a process flow control of this type. Here is an explanation of the configuration in this example:

  • The list of records from the model.Intrastat data source is divided into batches, each of which contains up to 1,000 records.
  • The output creates a zip file that contains one file in XML format for every batch that was created.
  • An expression returns a file name for generating electronic documents by concatenating the file name and file extension. For the second batch and all subsequent batches, the file name contains the batch ID as a suffix.
  • An expression enables (by returning TRUE) the process of file creation for batches that contain at least one record.

picture-file-control

Basic syntax

ER expressions can contain any or all of the following elements:

  • Constants
  • Operators
  • References
  • Paths
  • Functions

Constants

You can use text and numeric constants (values that aren't calculated) when you design expressions. For example, the expression VALUE ("100") + 20 **uses the numeric constant 20 and the string constant “100”, and returns the numeric value **120. The ER formula designer supports escape sequences, which means that you can specify the expression string that should be handled differently. For example, the expression "Leo Tolstoy ""War and Peace"" Volume 1" returns the text string Leo Tolstoy "War and Peace" Volume 1.

Operators

The following table shows the arithmetic operators that you can use to perform basic mathematical operations, such as addition, subtraction, division, and multiplication.

Operator Meaning Example
+ Addition 1+2
- Subtraction Negation 5-2 -1
* Multiplication 7*8
/ Division 9/3

The following table shows the comparison operators that are supported, and that you can use to compare two values.

Operator Meaning Example
= Equal X=Y
> Greater than X>Y
< Less than X<Y
>= Greater than or equal to X>=Y
<= Less than or equal to X<=Y
<> Not equal to X<>Y

Additionally, you can use an ampersand (&) as a text concatenation operator to join, or concatenate, one or more text strings into a single piece of text.

Operator Meaning Example
& Concatenate "Nothing to print" & ": " & "no records found"

Operator precedence

The order in which the parts of a compound expression are evaluated is important. For example, the result of the expression** 1 + 4 / 2** differs, depending on whether the addition operation or the division operation is performed first. You can use parentheses to explicitly define how an expression is evaluated. For example, to indicate that the addition operation should be performed first, you can modify the preceding expression to (1 + 4) / 2. If the order of operations that must be performed in an expression isn't explicitly defined, the order is based on the default precedence that is assigned to the supported operators. The following tables shows the operators and the precedence that is assigned to each. Operators that have higher precedence (for example, 7) are evaluated before operators that have lower precedence (for example, 1).

Precedence Operators Syntax
7 Grouping ( … )
6 Member access … . …
5 Function call … ( … )
4 Multiplicative … * … … / …
3 Additive … + … … - …
2 Comparison … < … … <= … … => … … > … … = … … <> …
1 Separation … , …

Operators on the same line have equal precedence. If an expression includes more than one of these operators, the expression is evaluated from left to right. For example, the expression 1 + 6 / 2 * 3 > 5 returns true. We recommend that you use parentheses to explicitly indicate the desired order of evaluation for expressions, to make the expressions easier to read and maintain.

References

All data sources of the current ER component (either a model or a format) that are available during the design of an expression can be used as named references. For example, the current ER data model contains the ReportingDate data source, which returns a value of the DATETIME data type. To properly format that value in the generating document, you can reference the data source in the expression as follows: DATETIMEFORMAT (ReportingDate, "dd-MM-yyyy") All characters in the name of a referencing data source that don't represent a letter of the alphabet must be preceded by a single quotation mark ('). If the name of a referencing data source contains at least one symbol that doesn't represent a letter of the alphabet (for example, punctuation marks or other written symbols), the name must be enclosed in single quotation marks. Here are some examples:

  • The Today's date & time data source must be referred to in an ER expression as follows: 'Today''s date & time'
  • The name() method of the Customers data source must be referred to in an ER expression as follows: Customers.'name()'

Note that the following syntax is used to call methods of the Dynamics 365 for Operation data sources with parameters:

  • The isLanguageRTL method of the System data source with a parameter EN-US of the string data type must be referred to in an ER expression as follows: System.’isLanguageRTL’(“EN-US”).
  • Quotation marks are not mandatory when a method name contains only alphanumeric symbols. They are mandatory for a method of a table when the name includes brackets.

When the System data source is added to an ER mapping which refers to the Dynamics 365 for Operation application class Global, the expression returns boolean value, FALSE. The modified expression, System.’ isLanguageRTL’(“AR”) returns boolean value, TRUE.

Note that the passing to such methods parameters can be defined with the following limitations:

  • Only constants can be passed to such methods, the value of which are defined at design time.
  • Only primitive (basic) data types are supported for such parameters (integer, real, boolean, string, etc.).

Path

When an expression references a structured data source, you can use the path definition to select a specific primitive element of that data source. A dot character (.) is used to separate individual elements of a structured data source. For example, the current ER data model contains the InvoiceTransactions data source, which returns a list of records. The InvoiceTransactions record structure contain the AmountDebit and AmountCredit fields, which return numeric values. Therefore, you can design the following expression to calculate the invoiced amount: InvoiceTransactions.AmountDebit - InvoiceTransactions.AmountCredit

Functions

The next section describes the functions that can be used in ER expressions. All data sources of the expression context (the current ER data model or ER format), and also constants, can be used as parameters of calling functions, in accordance with the list of calling function arguments. For example, the current ER data model contains the InvoiceTransactions data source, which returns a list of records. The InvoiceTransactions record structure contain the AmountDebit and AmountCredit fields, which return numeric values. Therefore, to calculate the invoiced amount, you can design the following expression that uses the built-in ER rounding function: ROUND (InvoiceTransactions.AmountDebit - InvoiceTransactions.AmountCredit, 2)

Supported functions

The following tables describe the data manipulation functions that you can use to design ER data models and ER reports. The list of functions isn't fixed and can be extended by developers. To see the list of functions that you can use, access the functions pane in the ER formula designer.

Date and time functions

Function Description Example
ADDDAYS (datetime, days) Add the specified number of days to the specified datetime value. ADDDAYS (NOW(), 7) returns the date and time seven days in the future.
DATETODATETIME (date) Convert the specified date value to a datetime value. DATETODATETIME (CompInfo. 'getCurrentDate()') returns the current Finance and Operations session date, 12/24/2015, as 12/24/2015 12:00:00 AM. In this example, CompInfo is an ER data source of the Finance and Operations/Table type that refers to the CompanyInfo table.
NOW () Return the current Finance and Operations application server date and time as a datetime value.
TODAY () Return the current Finance and Operations application server date as a date value.
NULLDATE () Return a null date value.
NULLDATETIME () Return a null datetime value.
DATETIMEFORMAT (datetime, format) Convert the specified datetime value to a string in the specified format. (For information about the supported formats, see standard and custom.) DATETIMEFORMAT (NOW(), "dd-MM-yyyy") returns the current Finance and Operations application server date, 12/24/2015, as "24-12-2015", according to the specified custom format.
DATETIMEFORMAT (datetime, format, culture) Convert the specified datetime value to a string in the specified format and culture. (For information about the supported formats, see standard and custom). DATETIMEFORMAT (NOW(), "d", "de") returns the current Finance and Operations application server date, 12/24/2015, as "24.12.2015", according to the selected German culture.
SESSIONTODAY () Returns the current Dynamics 365 for Finance and Operations session date as date value.
SESSIONNOW () Returns the current Dynamics 365 for Finance and Operations session date and time as datetime value.
DATEFORMAT (date, format) Returns string representation of date using specified format. DATEFORMAT (SESSIONTODAY (), "dd-MM-yyyy") returns the current Dynamics 365 for Finance and Operations session date 12/24/2015 as “24-12-2015” according to specified custom format.
DATEFORMAT (date, format, culture) Convert the specified date value to a string in the specified format and culture. (For information about the supported formats, see standard and custom). DATETIMEFORMAT (SESSIONNOW (), "d", "de") returns the current Finance and Operations session date 12/24/2015 as “24.12.2015” according to selected German culture.
DAYOFYEAR (date) Returns integer representation of the number of days between January 1st and the specified date. DAYOFYEAR (DATEVALUE ("01-03-2016", "dd-MM-yyyy")) returns 61. DAYOFYEAR (DATEVALUE ("01-01-2016", "dd-MM-yyyy")) returns 1.

Data conversion functions

Function Description Example
DATETODATETIME (date) Convert the specified date value to a datetime value. DATETODATETIME (CompInfo. 'getCurrentDate()') returns the current Finance and Operations session date, 12/24/2015, as 12/24/2015 12:00:00 AM. In this example, CompInfo is an ER data source of the Finance and Operations/Table type that refers to the CompanyInfo table.
DATEVALUE (string, format) Returns date representation of a string using a specified format. DATEVALUE ("21-Dec-2016", "dd-MMM-yyyy") returns the date 12/21/2016 according to specified custom format and the default application’s EN-US culture.
DATEVALUE (string, format, culture) Returns date representation of a string using a specified format and culture. DATEVALUE ("21-Gen-2016", "dd-MMM-yyyy", “IT”) returns the date 01/21/2016 according to specified custom format and culture. An exception will be thrown for this function’s call, DATEVALUE ("21-Gen-2016", "dd-MMM-yyyy", “EN-US”) informing that a given string is not recognized as a valid date.
DATETIMEVALUE (string, format) Returns datetime representation of a string using a specified format. DATETIMEVALUE ("21-Dec-2016 02:55:00", "dd-MMM-yyyy hh:mm:ss") returns the 2:55:00 AM of Dec 21st, 2016 according to specified custom format and the default application’s EN-US culture.
DATETIMEVALUE (string, format, culture) Returns datetime representation of a string using a specified format and culture. DATETIMEVALUE ("21-Gen-2016 02:55:00", "dd-MMM-yyyy hh:mm:ss", “IT”) returns the 2:55:00 AM of Dec 21st, 2016 according to a specified custom format and culture. An exception will be thrown for this function’s call, DATETIMEVALUE ("21-Gen-2016 02:55:00", "dd-MMM-yyyy hh:mm:ss", “EN-US”) informing that a given string is not recognized as a valid datetime.

List functions

Function Description Example
SPLIT (input, length) Split the specified input string into substrings, each of which has the specified length. Return the result as a new list. SPLIT ("abcd", 3) returns a new list that consists of two records that have a STRING field. The field in the first record contains the text "abc", and the field in the second record contains the text "d".
SPLITLIST (list, number) Split the specified list into batches, each of which contains the specified number of records. Return the result as a new list of batches that contains the following elements:
  • Batches as regular lists (Value component)
  • The current batch number (BatchNumber component)
In the following example, the Lines data source is created as a record list of three records, which is divided into batches, each of which contains up to two records. Data source that is divided into batches This shows the designed format layout, where bindings to the Lines data source are created to generate output in XML format that presents individual nodes for each batch and the records in it. Format layout that has bindings to a data source The following is the result of running the designed format. Result of running the format
LIST (record 1 [, record 2, ...]) Return a new list that is created from the specified arguments. LIST (model.MainData, model.OtherData) returns an empty record, where the list of fields contains all fields of the MainData and OtherData record lists.
LISTJOIN (list 1, list 2, ...) Return a joined list that is created from lists of specified arguments. LISTJOIN (SPLIT ("abc", 1), SPLIT ("def", 1)) returns the list of six records, where one field of the STRING data type contains single letters.
ISEMPTY (list) Return TRUE if the specified list doesn't contain any elements. Otherwise, return FALSE.
EMPTYLIST (list) Return an empty list by using the specified list as a source for the list structure. EMPTYLIST (SPLIT ("abc", 1)) returns a new empty list that has the same structure as the list that is returned by SPLIT function.
FIRST (list) Return the first record of the specified list, if that record isn't empty. Otherwise, throw an exception.
FIRSTORNULL (list) Return the first record of the specified list, if that record isn't empty. Otherwise, return a null record.
LISTOFFIRSTITEM (list) Return a list that contains only the first item of the specified list.
ALLITEMS (path) Return a new flattened list that represents all items that match the specified path. The path must be defined as a valid data source path to a data source element of a record list data type. The path to string, date, etc. data elements should raise an error at design time in ER expression builder. If you enter SPLIT("abcdef" , 2) as a data source (DS), COUNT( ALLITEMS (DS.Value)) returns 3.
ORDERBY (list [, expression 1, expression 2, …]) Return the specified list, which is sorted according to the specified arguments that can be defined as expressions. When Vendor is configured as an ER data source that refers to the VendTable table, ORDERBY (Vendors, Vendors.'name()') returns the list of vendors that is sorted by name in ascending order.
REVERSE (list) Return the specified list in reversed sort order. When Vendor is configured as an ER data source that refers to the VendTable table, REVERSE (ORDERBY (Vendors, Vendors.'name()')) ) returns the list of vendors that is sorted by name in descending order.
WHERE (list, condition) Return the specified list, which is filtered according to the specified condition. Unlike FILTER function, the specified condition is applied to the list in the memory. When Vendor is configured as an ER data source that refers to the VendTable table, WHERE(Vendors, Vendors.VendGroup = "40") returns the list of vendors that belong to the vendor group 40.
ENUMERATE (list) Return a new list that consists of enumerated records of the specified list, and that exposes the following elements:
  • Specified list records as regular lists (Value component)
  • The current record index (Number component)
In the following example, the Enumerated data source is created as an enumerated list of vendor records from the Vendors data source that refers to the VendTable table. Enumerated data source Here is the format, where data bindings are created to generate output in XML format that presents individual vendors as enumerated nodes. Format that has data bindings This is the result of running the designed format. Result of running the format
COUNT (list) Return the number of records in the specified list, if the list isn't empty. Otherwise, return 0 (zero). COUNT (SPLIT("abcd" , 3)) returns 2, because the SPLIT function creates a list that consists of two records.
LISTOFFIELDS (path) Returns a records list created from an argument of one of the following types:
  • Model enumeration
  • Format enumeration
  • Container
The created list will consist of records with the following fields:
  • Name
  • Label
  • Description
The Label and Description fields will return at run-time values based on format’s language settings.
The following example shows the enumeration introduced in a data model. GER LISTOFFIELDS function - model enumeration The following example shows:
  • Model enumeration inserted into a report as a data source.
  • ER expression designed to use model enumeration as parameter of this function.
  • Data source of the record list type inserted into a report using the created ER expression.
GER LISTOFFIELDS function - in format expression The following example shows the ER format elements that are bound to the data source of record list type that was created using the LISTOFFIELDS function. GER LISTOFFIELDS function - format design This is the result of the designed format execution. GER LISTOFFIELDS function - format output Note: Translated text for labels and descriptions is populated to ER format output in accordance with the language settings configured for parent FILE and FOLDER format elements.
STRINGJOIN (list, field name, delimiter) Returns the string of concatenated values of a field from a list separated with a selected delimiter. If you entered SPLIT(“abc” , 1) as a data source DS, expression STRINGJOIN (DS, DS.Value, “:”) returns “a🅱️c”
SPLITLISTBYLIMIT (list, limit value, limit source) Splits the given list into a new list of sub-lists and returns the result in record list content. The limit value parameter specifies the value of the limit to split the origin list. The limit source parameter specifies the step which the total sum is increased on. The limit is not applied to a single item of the given list when the limit source exceeds the defined limit. The following example shows the sample format using data sources. GER SPLITLISTBYLIMIT - formatGER SPLITLISTBYLIMIT - datasources This is the result format execution that presents the flat list of commodity items. GER SPLITLISTBYLIMIT - output The following example shows the same format that was adjusted to present the list of commodity items in batches when a single batch must include commodities with the total weight that should not exceed the limit of 9. GER SPLITLISTBYLIMIT - format 1GER SPLITLISTBYLIMIT - datasources 1 This is the result of the adjusted format execution. GER SPLITLISTBYLIMIT - output 1 Note: The limit is not applied to the last item of the origin list as the value (11) of its limit’s source (weight) exceeds the defined limit (9). Use either the function WHERE or the Enabled expression of the corresponding format element to ignore (skip) sub-lists during the report generation (if needed).
FILTER (list, condition) Returns the given list filtered for the specified condition by modifying the query. Unlike the WHERE function, the specified condition is applied at the database level to any ER data source of the Table records type. FILTER (Vendors, Vendors.VendGroup = "40") returns the list of only vendors belonging to the vendors’ group “40” when Vendor is configured as ER data source referring to the VendTable table

Logical functions

Function Description Example
CASE (expression, option 1, result 1 [, option 2, result 2] ... [, default result]) Evaluate the specified expression value against the specified alternative options. Return the result of the option that is equal to the value of the expression. Otherwise, return the optionally entered default result (the last parameter that isn't preceded by an option). CASE( DATETIMEFORMAT( NOW(), "MM"), "10", "WINTER", "11", "WINTER", "12", "WINTER", "") returns the string "WINTER" when the current Finance and Operations session date is between October and December. Otherwise, it returns a blank string.
IF (condition, value 1, value 2) Return the specified value 1 when the given condition is met. Otherwise, return value 2. If value 1 and value 2 are records or record lists, the result will have only the fields that exist in both lists. IF (1=2, "condition is met", "condition is not met") returns the string "condition is not met".
NOT (condition) Return the reversed logical value of the specified condition. NOT (TRUE) returns FALSE.
AND (condition 1[, condition 2, ...]) Return TRUE if all specified conditions are true. Otherwise, return FALSE. AND (1=1, "a"="a") returns TRUE. AND (1=2, "a"="a") returns FALSE.
OR (condition 1[, condition 2, ...]) Return FALSE if all specified conditions are false. Return TRUE if any specified condition is true. OR (1=2, "a"="a") returns TRUE.

Mathematical functions

Function Description Example
ABS (number) Return the absolute value of the specified number (the number without its sign). ABS (-1) returns 1.
POWER (number, power) Return the result of raising the specified positive number to the specified power. POWER (10, 2) returns 100.
NUMBERVALUE (string, decimal separator, digit grouping separator) Convert the specified string to a number. The specified symbol is used to separate the integer and fractional parts of a decimal number, and the specified thousands separator is also used. NUMBERVALUE("1 234,56", ",", " ") returns the value 1234.56.
VALUE (string) Convert the specified string to a number. Commas and dot characters (.) are considered decimal separators, and a leading hyphen (-) is used as negative sign. Throw an exception if other non-numeric characters are encountered in the specified string. VALUE ("1 234,56") throws an exception.
ROUND (number, decimals) Return the specified number, which is rounded to the specified number of decimal places:
  • If the specified decimals value is more than 0 (zero), the specified number is rounded to the specified number of decimal places.
  • If the specified decimals value is 0 (zero), the specified number is rounded to the nearest integer.
  • If the specified decimals value is less than 0 (zero), the specified number is rounded to the left of the decimal point.
ROUND (1200.767, 2) rounds to two decimal places and returns 1200.77. ROUND (1200.767, -3) rounds to the nearest multiple of 1,000 and returns 1000.
ROUNDDOWN (number, decimals) Return the specified number, which is rounded down (toward zero) to the specified number of decimal places. Note: This function behaves like ROUND, but it always rounds the specified number down. ROUNDDOWN (1200.767, 2) rounds down to two decimal places and returns 1200.76. ROUNDDOWN (1700.767, -3) rounds down to the nearest multiple of 1,000 and returns 1000.
ROUNDUP (number, decimals) Return the specified number, which is rounded up (away from zero) to the specified number of decimal places. Note: This function behaves like ROUND, but it always rounds the specified number up. ROUNDUP (1200.763, 2) rounds up to two decimal places and returns 1200.77. ROUNDUP (1200.767, -3) rounds up to the nearest multiple of 1,000 and returns 2000.

Data conversion functions

Function Description Example
VALUE (string) Convert the specified string to a number. Commas and dot characters (.) are considered decimal separators, and a leading hyphen (-) is used as a negative sign. If other non-numeric characters are encountered in the specified string, an error occurs. VALUE ("1 234,56") throws an exception.
NUMBERVALUE (string, decimal separator, digit grouping separator) Convert the specified string to a number. The specified symbol is used to separate the integer and fractional parts of a decimal number, and the specified thousands separator is also used. NUMBERVALUE("1 234,56", ",", " ") returns the value 1234.56.
INTVALUE (string) Returns integer representation of a string. Any available decimal parts will be truncated. INTVALUE (“100.77”) returns 100.
INTVALUE (number) Returns integer representation of a number. Any available decimal parts will be truncated. INTVALUE (-100.77) returns -100.
INT64VALUE (string) Returns int64 representation of a string. Any available decimal parts will be truncated. INT64VALUE (“22565422744”) returns 22565422744.
INT64VALUE (number) Returns int64 representation of a number. Any available decimal parts will be truncated. INT64VALUE (22565422744.00) returns 22565422744.

Record functions

Function Description Example
NULLCONTAINER (list) Return a null record that has the same structure as the specified record list or record. Note: This function is obsolete. Use EMPTYRECORD instead. NULLCONTAINER (SPLIT ("abc", 1)) returns a new empty record that has the same structure as the list that is returned by the SPLIT function.
EMPTYRECORD (record) Return a null record that has the same structure as the specified record list or record. Note: A null record is a record where all fields have an empty value (0 [zero] for numbers, an empty string for strings, and so on). EMPTYRECORD (SPLIT ("abc", 1)) returns a new empty record that has the same structure as the list that is returned by the SPLIT function.

Text functions

Function Description Example
UPPER (string) Return the specified string, which is converted to uppercase letters. UPPER("Sample") returns "SAMPLE".
LOWER (string) Return the specified string, which is converted to lowercase letters. LOWER ("Sample") returns "sample".
LEFT (string, number of characters) Return the specified number of characters from the start of the specified string. LEFT ("Sample", 3) returns "Sam".
RIGHT (string, number of characters) Return the specified number of characters from the end of the specified string. RIGHT ("Sample", 3) returns "ple".
MID (string, starting position, number of characters) Return the specified number of characters from the specified string, starting at the specified position. MID ("Sample", 2, 3) returns "amp".
LEN (string) Return the number of characters in the specified string. LEN ("Sample") returns 6.
CHAR (number) Return the string of characters that is referenced by the specified Unicode number. CHAR (255) returns "ÿ". Note: The returned string depends on the encoding that is selected in the parent FILE format element. The list of supported encodings can be found in the Encoding Class topic.
CONCATENATE (string 1 [, string 2, …]) Return all specified text strings, which are joined into one string. CONCATENATE ("abc", "def") returns "abcdef". Note: The expression "abc" & "def" also returns "abcdef".
TRANSLATE (string, pattern, replacement) Return the specified string, in which all occurrences of the characters in the specified pattern string are replaced by the characters at the corresponding position of the specified replacement string. TRANSLATE ("abcdef", "cd", "GH") replaces the pattern "cd" with the string "GH" and returns "abGHef".
REPLACE (string, pattern, replacement, regular expression flag) When the specified regular expression flag is true, return the specified string, which is modified by applying the regular expression that is specified as a pattern argument for this function. This expression is used to find characters that must be replaced. Characters of the specified replacement argument are used to replace characters that are found. When the specified regular expression flag is false, this function behaves like TRANSLATE. REPLACE ("+1 923 456 4971", "[^0-9]", "", true) applies a regular expression that removes all non-numeric symbols, and returns "19234564971". REPLACE ("abcdef", "cd", "GH", false) replaces the pattern "cd" with the string "GH" and returns "abGHef".
TEXT (input) Return the specified input, which is converted to a text string that is formatted according to the server locale settings of the current Finance and Operations instance. For values of the real type, the string conversion is limited to two decimal places. If the Finance and Operations instance server locale is defined as EN-US, TEXT (NOW ()) returns the current Finance and Operations session date, 12/17/2015, as the text string "12/17/2015 07:59:23 AM". TEXT (1/3) returns "0.33".
FORMAT (string 1, string 2[, string 3, ...]) Return the specified string, which is formatted by substituting any occurrences of %N with the nth argument. The arguments are strings. If an argument isn't provided for a parameter, the parameter is returned as "%N" in the string. For values of the real type, the string conversion is limited to two decimal places. In this example, the PaymentModel data source returns the list of customer records via the Customer component and the processing date value via the ProcessingDate field. PaymentModel data source In the ER format that is designed to generate an electronic file for selected customers, PaymentModel is selected as a data source and controls the process flow. An exception is thrown for end users when a selected customer is stopped for the date when the report is processed. The formula that is designed for this type of processing control can use the following resources:
  • Finance and Operations label SYS70894, which has the following text:
    • For the EN-US language: "Nothing to print"
    • For the DE language: "Nichts zu drucken"
  • Finance and Operations label SYS18389, which has the following text:
    • For the EN-US language: "Customer %1 is stopped for %2."
    • For the DE language: "Debitor '%1' wird für %2 gesperrt."
Here is the formula that can be designed: FORMAT (CONCATENATE (@"SYS70894", ". ", @"SYS18389"), model.Customer.Name, DATETIMEFORMAT (model.ProcessingDate, "d")) If a report is processed for the Litware Retail customer on December 17, 2015, in the EN-US culture and the EN-US language, this formula returns the following text, which can be presented as an exception message for the end user: "Nothing to print. Customer Litware Retail is stopped for 12/17/2015." If the same report is processed for the Litware Retail customer on December 17, 2015, in the DE culture and the DE language, this formula returns the following text, which uses a different date format: "Nichts zu drucken. Debitor 'Litware Retail' wird für 17.12.2015 gesperrt." Note: The following syntax is applied in ER formulas for labels:
  • For labels from Finance and Operations resources: @"X", where X is the label ID in the Application Object Tree (AOT)
  • For labels that reside in ER configurations: @"GER_LABEL:X", where X is the label ID in the ER configuration
NUMBERFORMAT (number, format) Return the string representation of the specified number in the specified format. (For information about the supported formats, see standard and custom.) The context that this function is run in determines the culture that is used to format numbers. For the EN-US culture, NUMBERFORMAT (0.45, "p") returns "45.00 %". NUMBERFORMAT (10.45, "#") returns "10".
NUMERALSTOTEXT (number, language, currency, print currency name flag, decimal points) Returns the number spelled out (converted) to text strings in the defined language. The language code is optional: when it is defined as empty string, the running context language code (defined for a generating folder or file) will be used instead. The currency code is optional. When it is defined as empty string, the company currency is taken. Note, the Print currency name parameter and the Decimal points parameter are analyzed for the following language codes only: CS, ET, HU, LT, LV, PL, RU. Note, the Print currency name parameter is analyzed for only Finance and Operations companies with country's context that supports currency's declension. NUMERALSTOTEXT (1234.56, "EN", "", false, 2) returns “One Thousand Two Hundred Thirty Four and 56” NUMERALSTOTEXT (120, "PL", "", false, 0) returns “Sto dwadzieścia” NUMERALSTOTEXT (120.21, "RU", "EUR", true, 2) returns “Сто двадцать евро 21 евроцент”
PADLEFT (string, length, padding chars) Returns a string of a specified length in which the beginning of the current string is padded with specified characters. PADLEFT (“1234”, 10, “ “) returns the text string “ 1234”
TRIM (string) Returns given text after truncating leading and trailing spaces, and removes multiple spaces between words. TRIM (" Sample text ") returns "Sample text".
GETENUMVALUEBYNAME (enumeration data source path, enumeration value label text) Returns a value of a specified enumeration data source by specified text of this enumeration label. The following example shows the enumeration ReportDirection introduced in a data model. Note that labels are defined for enumeration values. The following examples show:
  • Model enumeration ReportDirection inserted into a report as a data source $Direction
  • ER expression $IsArrivals designed to use model enumeration as parameter of this function. The value of this expression is TRUE

Data conversion functions

Function Description Example
TEXT (input) Return the specified input, which is converted to a text string that is formatted according to the server locale settings of the current Finance and Operations instance.
For values of the real type, the string conversion is limited to two decimal places. If the Finance and Operations instance server locale is defined as EN-US, TEXT (NOW ()), the current Finance and Operations session date, 12/17/2015, is returned as the text string "12/17/2015 07:59:23 AM".
TEXT (1/3) returns "0.33".
QRCODE (string) Returns QR code image in base64 binary format for a given string. QRCODE (“Sample text”) returns U2FtcGxlIHRleHQ=.

Data collection functions

Function Description Example
FORMATELEMENTNAME () Returns the name of the current format’s element. Returns empty string when the flag Collect output details of the current files is turned off. Refer to the ER Use data of format output for counting and summing (part of the Acquire/Develop IT service/solution components business process) Task guide to learn more about these functions' usage.
SUMIFS (key string for summing, criteria range1 string, criteria value1 string [, criteria range2 string, criteria value2 string, …]) Returns a sum of values of nodes (with name defined as a key) of XML, which has been collected during this format execution and satisfies the entered conditions (pairs of range and value). Returns zero value when the flag Collect output details of the current files is turned off.
SUMIF (key string for summing, criteria range string, criteria value string) Returns a sum of values of nodes (with name defined as a key) of XML, which has been collected during this format execution and satisfies the entered condition (range and value). Returns zero value when the flag Collect output details of the current files is turned off.
COUNTIFS (criteria range1 string, criteria value1 string [, criteria range2 string, criteria value2 string, …]) Returns number of nodes of XML, which has been collected during this format execution and satisfies the entered conditions (pairs of range and value). Returns zero value when the flag Collect output details of the current files is turned off.
COUNTIF (criteria range string, criteria value string) Returns number of nodes of XML, which has been collected during this format execution and satisfies the entered condition (range and value). Returns zero value when the flag Collect output details of the current files is turned off.
COLLECTEDLIST (criteria range1 string, criteria value1 string [, criteria range2 string, criteria value2 string, …]) Returns a list of values of nodes of XML, which has been collected during this format execution and satisfies the entered conditions (range and value). Returns an empty list when the flag Collect output details of the current files is turned off.

Other (business domain–specific) functions

Function Description Example
CONVERTCURRENCY (amount, source currency, target currency, date, company) Convert the specified monetary amount from the source currency to the target currency by using the settings of the specified Finance and Operations company on the specified date. CONVERTCURRENCY (1, "EUR", "USD", TODAY(), "DEMF") returns the equivalent of one euro in US dollars on the current session date, based on settings for the DEMF company.
ROUNDAMOUNT (number, decimals, round rule) Round the specified amount according to the specified rounding rule and the specified number of decimal places. Note: The rounding rule must be specified as a value of the Finance and Operations RoundOffType enumeration. If the model.RoundOff parameter is set to *Downward, **ROUNDAMOUNT (1000.787, 2, model.RoundOff)* returns the value 1000.78. If the model.RoundOff parameter is set to either Normal or Rounding-up, ROUNDAMOUNT (1000.787, 2, model.RoundOff) returns the value 1000.79.
CURCredRef (digits) Return a creditor reference, based on the digits of the specified invoice number. CURCredRef ("VEND-200002") returns "2200002".
MOD_97 (digits) Return a creditor reference as a MOD97 expression, based on the digits of the specified invoice number. MOD_97 ("VEND-200002") returns "20000285".
ISOCredRef (digits) Return an ISO creditor reference, based on the digits and alphabetic symbols of the specified invoice number. Note: To eliminate symbols from alphabets that aren't ISO-compliant, the input parameter must be translated before it's passed to this function. ISOCredRef ("VEND-200002") returns "RF23VEND-200002".
CN_GBT_AdditionalDimensionID (string, number) Get the additional financial dimension ID. Dimensions are represented in this string as IDs separated by commas. Numbers define the requested dimension’s sequence code in this string. CN_GBT_AdditionalDimensionID ("AA,BB,CC,DD,EE,FF,GG,HH",3) return “CC”
GetCurrentCompany () Returns text representation of a code of a legal entity (company) in to which a user currently logged. GETCURRENTCOMPANY () returns USMF for a user logged in to the Finance and Operations company Contoso Entertainment System USA.
CH_BANK_MOD_10 (digits) Returns a creditor reference as MOD10 expression based on digits of the given invoice number. CH_BANK_MOD_10 ("VEND-200002") returns 3
FA_SUM (fixed asset code, value model code, start date, end date) Returns the prepared data container of a fixed asset amounts for a period. FA_SUM ("COMP-000001", “Current”, Date1, Date2) returns the prepared data container of the fixed asset "COMP-000001" with the value model “Current” for a period from Date1 to Date2.
FA_BALANCE (fixed asset code, value model code, reporting year, reporting date) Returns the prepared data container of a fixed asset balances. Reporting year must be specified as a value of the Finance and Operations enumeration AssetYear. FA_SUM ("COMP-000001", “Current”, AxEnumAssetYear.ThisYear, SESSIONTODAY ()) returns the prepared data container of balances for the fixed asset "COMP-000001" with the value model “Current” on the current 365 for Finance and Operations session date.
TABLENAME2ID (string) Returns integer representation of a Table Id for a given Table Name. TABLENAME2ID (“Intrastat”) returns 1510.
ISVALIDCHARACTERISO7064 (string) Returns boolean TRUE when a given string represents a valid international bank account number (IBAN). Returns boolean FALSE otherwise. ISVALIDCHARACTERISO7064 ("AT61 1904 3002 3457 3201") returns TRUE. ISVALIDCHARACTERISO7064 ("AT61") returns FALSE.

Functions list extension

ER lets you extend the list of functions that are used in ER expressions. Some engineering efforts are required. For detailed information, see Extending the list of Electronic reporting functions.

See also

Electronic Reporting overview

Extend the list of Electronic reporting (ER) functions