Custom functions in preview

The functionality and syntax of these functions may change. Therefore, we recommend that you use them only for experimental purposes.

This table shows the list of preview functions, their syntax, their results, and whether the functions support dynamic arrays:

Formula Syntax Result Dynamic array support Example
INFOR.JOINARRAYS INFOR.JOINARRAYS(use_crossjoin, first_array, second_array {, [array], ...}) Returns an array of values of any type, joined by columns. Yes =INFOR.JOINARRAYS(TRUE, A2:B4, D2:E3)

Cell range A2:B4 is Array 1. Cell range D2:E3 is Array 2. TRUE indicates that use_crossjoin is enabled.

=INFOR.JOINARRAYS(FALSE, A2:B4, D2:E3)

FALSE indicates that use_crossjoin is disabled.
INFOR.SUBARRAY INFOR.SUBARRAY([array] {,first_column, column_count, first_row, row_count}) Returns an array of values of any type. It represents sub array of a selected array. Yes =INFOR.SUBARRAY(A2:C6, 1, 1, 1, 1)

Cell range A2:C6 is the array. The formula extracts the first column and one column total, first row and one row total.

=INFOR.SUBARRAY(A2:C6, 2, 2, 2, 2)

The formula extracts two columns starting from the second column, and two rows starting from the second row.
INFOR.READ.SESE.REPORT INFOR.READ.SESE.REPORT([report_parameters], ...) Returns a report that is generated based on the JSON definition of the Self-Service report. Yes =INFOR.READ.SESE.REPORT(A9:A610)

The formula generates the Self-Service report based on the JSON report definition contained in the (A9:A610) cell range.

INFOR.GET.ELEMENT.ATTRIBUTE INFOR.GET.ELEMENT.ATTRIBUTE("data_connection", "cube", "attribute", "hierarchy", "element"{, ["context"], ...}) Returns a single attribute for a given element or multiple elements with a given context if the attribute is dynamic and is driven by other dimensions. No =INFOR.GET.ELEMENT.ATTRIBUTE("BIBestPr_BestPracticesOLAP", "ANALYSIS", "Member_Caption", "Period", "2016")

These elements of the context are used:

  • "BIBestPr_BestPracticesOLAP": The name of the data connection that is used to access the OLAP cube.
  • "ANALYSIS": The cube name.
  • "Member_Caption": The attribute to retrieve.
  • "Period": The hierarchy in which the element resides.
  • "2016": The element for which to retrieve the attribute.

The formula returns the attribute value for the "2016" element in the "Period" hierarchy. For example, "2016" or a related label is returned, depending on how the attribute is defined in your cube.
INFOR.WRITE.ELEMENT.ATTRIBUTE INFOR.WRITE.ELEMENT.ATTRIBUTE("value", "data_connection", "cube", "attribute", "hierarchy", "element"{, ["context"], ...}) Returns an attribute value if successful or an error in case of failure. It writes a value to a single attribute for a single element with a given context if the attribute is dynamic and is driven by other dimensions. No =INFOR.WRITE.ELEMENT.ATTRIBUTE("test", "BIBestPr_BestPracticesOLAP", "ANALYSIS", "ATT2_FRENCH", "[PRODUCT]", "[PRODUCT].[All Tires]")

The formula writes the "test" value to the "ATT2_FRENCH" attribute for the "[PRODUCT].[All Tires]" element in the "[PRODUCT]" hierarchy. The "ANALYSIS" cube from the "BIBestPr_BestPracticesOLAP", the name of the data connection that is used to access the OLAP cube.
INFOR.GET.AXIS INFOR.GET.AXIS("data_connection", "cube", spillByColumns, options, {, "hierarchy", [attributes], [elements], [options], [filters]},...) Returns a matrix of requested attributes for given hierarchies. It renders a whole axis of columns or rows. That is, if a user asks for various elements and attributes from multiple hierarchies, a cross join is created. Yes =INFOR.GET.AXIS(   "BIBestPr_BestPracticesOLAP",   "ANALYSIS",   TRUE,   {"NumberOfRecords", 3; "Page", 1; "ExpandToLevel", 2},   {"[Period]", {"MEMBER_CAPTION", "ATT2_GERMAN"},    {"[Period].[All Years].[2005]", "[Period].[All Years].[2006]", "[Period].[All Years].[2007]"}} )

These formula components are used to retrieve and show the axis data:

  • "BIBestPr_BestPracticesOLAP": The name of the data connection that is used to access the OLAP cube.
  • "ANALYSIS": The cube name.
  • TRUE: If the spillByColumns parameter is set to TRUE, the result spills horizontally across columns.
  • You can use these options:
    • "NumberOfRecords", 3: Limits the result to 3 records.
    • "Page", 1: Retrieves the first page of the results.
    • "ExpandToLevel", 2: Expands the Period hierarchy to level 2, which includes individual years.
  • You can use these components of the hierarchy block:
    • "Hierarchy": Period is the queried dimension.
    • "Attributes": Two attributes are requested for each element .
    • "Elements": Specific members of the hierarchy to include, for example, years 2005, 2006, and 2007.
INFOR.READ.VALUES INFOR.READ.VALUES("data_connection", "cube" {, "cell_property", [rows_element], [row_index_offset], [columns_element], [column_index_offset], [slice_element] ... }) Retrieves a matrix of values for given axes and filter elements. It is useful for maintaining performance when reading multiple values from a cube. Yes =INFOR.READ.VALUES(   "BIBestPr_BestPracticesOLAP",   "ANALYSIS",   "VALUE",   "[PRODUCT].[All Tires]",   0,   "[Period].[All Years]",   0,   "[MEASURE].[Units]",   "[POS].[All Pos]" )

The formula retrieves a single value from the cube by specifying elements from each axis. You can use these formula components:

  • "BIBestPr_BestPracticesOLAP": The name of the data connection that is used to access the OLAP cube.
  • "ANALYSIS": The cube name.
  • "VALUE": The cell property to return. In this case, the actual value.
  • "[PRODUCT].[All Tires]": The row element.
  • 0: Indicates the column index offset or no offset. Also indicates the unique name and selects which members to return from each row tuple. For example, {2,4} returns the second and fourth member from each row.
  • "[Period].[All Years]": The column element.
  • 0: Indicates the row index offset or no offset. Also indicates which column corresponds to the unique name of the hierarchy.
  • "[MEASURE].[Units]" and "[POS].[All Pos]": Additional slice elements to filter data.
INFOR.READ.SLICE INFOR.READ.SLICE("reportDefinition"{[options], [parametrizationArgument] ... }) Used in ad-hoc reports. If used with the Excel Integration user interface, it enables interactions with a sheet, such as expanding and collapsing of axis elements. Helpful for an ad-hoc analysis. With the options argument you can define filters direction, switch from expandable slice to non-expandable slice, define the number format. You can enable the dynamic styles by setting UseStyles to true in the options argument. Yes =INFOR.READ.SLICE("Point of Sales", {"ExpandableSlice", TRUE; "UseStyles", TRUE; "SlicersHorizontally", TRUE}, NumberFormat) =INFOR.READ.SLICE('Infor-dEPM-Adhoc-Definitions'!$A$3, {"ExpandableSlice", TRUE; "UseStyles", TRUE; "SlicersHorizontally", TRUE; "NumberFormat","###.0#"})

'Infor-dEPM-Adhoc-Definitions'!$A$3 is the name of the report definition.

These options are available:

  • "ExpandableSlice", TRUE: Allows the hierarchical expansion. For example, allows for showing subcategories such as All Season Tires or Summer Tires.
  • "UseStyles", TRUE: Applies formatting styles that are defined in the report.
  • "SlicersHorizontally", TRUE: Applies horizontal orientation of the slicer dimension .
  • "NumberFormat": Applies the customized number format . Also indicates the unique name and selects which members to return from each column tuple. For example, {2,4} returns the second and fourth member from each column.

You can specify multiple options with a semicolon as a separator.
INFOR.ELEMENT.ENTITY INFOR.ELEMENT.ENTITY ("data_connection", "cube", [attributes], "hierarchy", [element] {, [context], ...}) It is an entity variant of the INFOR.GET.ELEMENT. Returns a single element entity. Retrieves multiple member properties to a single cell. You can reference this formula without introducing the element unique name. No =INFOR.ELEMENT.ENTITY("BIBestPr_BestPracticesOLAP","ANALYSIS","MEMBER_CAPTION","[Period]","[Period].[All Years].[2005]")

Use these options to configure how the data is retrieved and displayed:

  • "BIBestPr_BestPracticesOLAP": The name of the data connection that is used to access the OLAP cube.
  • "ANALYSIS": The cube name.
  • "MEMBER_CAPTION": The specific property to retrieve. In this case, the display name or caption of a member.
  • "[Period]": The dimension to query.
  • "[Period].[All Years].[2005]": The unique name of the member within the dimension.

The formula returns the caption, for example, the unique name of the member "[Period].[All Years].[2005]" from the "[Period]" dimension in the specified OLAP cube.
INFOR.VALUE.ENTITY INFOR.VALUE.ENTITY ("data_connection", "cube" {, [cell_property], "number_format", cell_coordinate, ... }) It is an entity variant of the INFOR.READ.VALUE. Returns multiple cell properties to a single cell. No =INFOR.VALUE.ENTITY("BIBestPr_BestPracticesOLAP","ANALYSIS","VALUE",,"[Period].[All Years]")

These options are available:

  • "BIBestPr_BestPracticesOLAP": The name of the data connection that is used to access the OLAP cube.
  • "ANALYSIS": The cube name.
  • "VALUE": The property to retrieve. In this case, the actual data value, for example, a measure such as sales or revenue.
  • The fourth argument is a number format. When an argument is blank, the default number format is used.
  • "[Period].[All Years]": The member from the "[Period]" dimension for which the value is retrieved.

The formula returns the aggregated value, for example, the total sales for the "[Period].[All Years]" member in the "[Period]" dimension. Depending on how the cube is configured, the formula pulls multiple cell properties, such as a value and format, into a single cell.
INFOR.SLICE.ENTITY INFOR.SLICE.ENTITY("reportDefinition"{[options], [parametrizationArgument] ... }) It is an entity variant of the INFOR.READ.SLICE where parametrizationArgument is any number of matrix arguments. You can define options with these arguments. Returns dynamic array of custom data types. Supports interactive behavior. Enables actions like Move To, Keep, Zoom In/Out, and Hierarchy Level. The HierarchyLevel action allows changing the selection to a specified level within the hierarchy. With the options argument you can define filters direction, switch from expandable slice to non-expandable slice, define the number format. You can enable the dynamic styles by setting UseStyles to true in the options argument. Yes =INFOR.SLICE.ENTITY("Point of Sales", {"ExpandableSlice", TRUE; "UseStyles", TRUE})

Use these options to configure how the data is retrieved and displayed from the cube:

  • "Point of Sales": The name of the report definition that retrieves the matrix of values.
  • "ExpandableSlice", TRUE: Enables the hierarchical expansion, for example, to show subcategories, such as All Season Tires.
  • "UseStyles", TRUE: Applies formatting styles from the report definition.

You can specify multiple options with a semicolon as a separator.