Converting Multivalue Enumerations to Names

This function converts a token-separated list of enumerated value IDs into a separated and sorted list of localized names.
Tip: Many objects include a _LIST data item that you can use instead of this function.

Report authors can use this function to turn a multivalue enumeration ID into a single string. This function eliminates the need to use custom SQL statements or additional query subjects.

Important: For Oracle, the maximum length of the string that the function can return is 4000 bytes.

Syntax

This function has the following syntax:

"OP_RPS_AUX.CONVERT_EVIDLIST_TO_NAME" (P1, P2, P3, P4, P5)

Where:

Table 1. Function Parameters
Parameter Description
P1 Required. The variable name of the multivalue enumerated variable to be converted to a string.
P2 Required. The locale ID (a numeric identifier). Used to automatically translate the text of enumerated strings to the locale of the user running the report.
Note: A value of NULL (without quotes) can be used; however, providing the locale session parameter or locale ID provides the best processing performance. Using NULL will force the function to use the system-wide default locale ID.

To return the session parameter that gives the value of the locale of the user running the report, use the following expression:

#$account.parameters.openPagesLocaleId#

Default: NULL (without quotation marks and not case-sensitive)
P3 Optional.

Specifies the delimiter character to be used as a separator for items in selection lists with multiple values (multi-valued enumerations).

Typically this value may be a comma (,), semi-colon (;), or pipe (|) character.

Default: ','
P4 Optional.

Specifies the column on which the selection list values (multi-valued enumerations) will be sorted.

Possible values:
'EV_DISPLAY_ORDER'
Use to sort the list values by the order of their enumerated value display as they appear in the application user interface (default).
'EV_NAME'
Use to sort the list values by the localized enumerated value name.
P5 Optional.

Specifies the sort direction for items in selection lists with multiple values (multi-valued enumerations).

Possible values:
'ASC'
This sorts the list values in ascending order (default).
'DESC'
This sorts the list values in descending order.

Usage Example

The following figure shows an example of the report output. In this example, the Domain field from the RT_PROCESS table is converted to a text string list.

Figure 1. Convert EVIDLIST to Name Output
The figure shows a table with three columns: Process Name, Domain IDs, and Convert EVIDLIST. The Domain IDs column shows numeric values (such as 228) while the Convert EVIDLIST column shows text (such as Internal Audit).