Examples

These examples describe how you can use custom functions.

INFOR.GET.ATTRIBUTES formula

This example retrieves captions and unique names of all attributes for the BIBestPr_BestPracticesOLAP data connection, the Analysis cube, product dimension and product hierarchy. The values are spilled down.

=INFOR.GET.ATTRIBUTES(FALSE,"BIBestPr_BestPracticesOLAP","Analysis","[PRODUCT]","[PRODUCT]")

INFOR.GET.CUBES formula

This example retrieves captions, unique names, descriptions, unique names of the measure dimension, unique names of the time dimension and data connection unique names for all cubes of all available data connections. The values are spilled down.

=INFOR.GET.CUBES()

This example retrieves captions and unique names of cubes specific to the BIBestPr_BestPracticesOLAPdata connection. The values are spilled down.

=INFOR.GET.CUBES(,"BIBestPr_BestPracticesOLAP",{"caption","unique_name"})

Note: The syntax for third argument: {"caption","unique_name"} is an array argument and it is a new feature of Microsoft Excel that allows you to provide multiple values as a single argument.

INFOR.GET.DATACONNECTIONS formula

This example retrieves both captions and unique names for all data connections spilling down.

=INFOR.GET.DATACONNECTIONS()

This example retrieves unique names for all Infor OLAP data connections. The values are spilled to the right.

=INFOR.GET.DATACONNECTIONS(TRUE,"unique_name", "inforolap")

INFOR.GET.DIMENSIONS formula

This example retrieves all properties for all dimensions on the BIBestPr_BestPracticesOLAP data connection and the Analysis cube. The values are spilled to the right.

=INFOR.GET.DIMENSIONS(TRUE,"BIBestPr_BestPracticesOLAP","Analysis")

This example retrieves captions and unique names for all dimensions on the BIBestPr_BestPracticesOLAP data connection and the Analysis cube.

=INFOR.GET.DIMENSIONS(TRUE,"BIBestPr_BestPracticesOLAP","Analysis",{"caption","unique_name"})

INFOR.GET.HIERARCHIES formula

This example retrieves captions and unique names for all hierarchies and all dimensions of the BIBestPr_BestPracticesOLAP data connection and the Analysis cube. The values are spilled down.

=INFOR.GET.HIERARCHIES(FALSE,"BIBestPr_BestPracticesOLAP","Analysis",,{"caption","unique_name"})

To retrieve caption and unique name for a the product dimension, provide the dimension unique name as the fourth argument.

=INFOR.GET.HIERARCHIES(FALSE,"BIBestPr_BestPracticesOLAP","Analysis","[PRODUCT]",{"caption","unique_name"})

INFOR.GET.LEVELS formula

This example retrieves captions and unique names for all levels of the BIBestPr_BestPracticesOLAP data connection, the Analysis cube, the product dimension and the product hierarchy. The values are spilled down.

=INFOR.GET.LEVELS(FALSE,"BIBestPr_BestPracticesOLAP","Analysis","[PRODUCT]","[PRODUCT]",{"caption","unique_name"})

INFOR.GET.ELEMENT formula

This example shows how INFOR.GET.ELEMENT retrieves caption for the 2015 element from the Period hierarchy.

=INFOR.GET.ELEMENT("BIBESTPR_BESTPRACTICESOLAP";"ANALYSIS";"MEMBER_CAPTION";"[PERIOD]";"[PERIOD].[ALL YEARS].[2015]")

INFOR.GET.ELEMENTS formula

This example shows how INFOR.GET.ELEMENTS retrieves caption for the 2015 element and its siblings from the Period hierarchy. The values are spilled down.

=INFOR.GET.ELEMENTS(FALSE,"BIBESTPR_BESTPRACTICESOLAP","ANALYSIS","MEMBER_CAPTION","[PERIOD]",,,,,"[PERIOD].[ALL YEARS].[2015].Siblings")

You can retrieve more attributes at once using the matrix argument. To retrieve caption and German translation, modify the previous example with a matrix argument {"MEMBER_CAPTION","Att2_GERMAN"} for the attribute. The elements are spilled down showing one more column for the "Att2_GERMAN" attribute.

=INFOR.GET.ELEMENTS(FALSE,"BIBESTPR_BESTPRACTICESOLAP","ANALYSIS{"MEMBER_CAPTION","Att2_GERMAN"},"[PERIOD]",,,,,"[PERIOD].[ALL YEARS].[2015].Siblings")

You can also define paging and indention. This example retrieves all members from the PERIOD hierarchy with paging enabled, showing only 10 records per page, showing second page and having indention enabled (showing "--" per level) for German translations.

=INFOR.GET.ELEMENTS(FALSE,"BIBESTPR_BESTPRACTICESOLAP","ANALYSIS",{"MEMBER_CAPTION","Att2_GERMAN"},"[PERIOD]",10,2,"--",2,"[PERIOD].AllMembers")

INFOR.GET.VALUE formula

This example shows how INFOR.GET.VALUE retrieves value from the BIBestPr_BestPracticesOLAP data connection and the Analysis cube. The cell coordinates are Units from the Measure dimension, Q1 2015 from the Period hierarchy, Body Shops from the POS hierarchy, AS600/175 from the Product hierarchy, California from the Region hierarchy, Direct Sales from the Channel hierarchy, and Actual from the VALTYPE hierarchy.

=INFOR.GET.VALUE("BIBESTPR_BESTPRACTICESOLAP";"ANALYSIS";"VALUE";"[MEASURE].[UNITS]";"[PERIOD].[ALL YEARS].[2015].[2015_Q1]";"[POS].[ALL POS].[BODYSHOPS]";"[PRODUCT].[ALL TIRES].[CAR TIRES ALL SEASON].[AS600 AQUACHRONO].[AS600/175]";"[REGION].[ALL REGIONS].[NORTH AMERICA].[USA].[CALIFORNIA]";"[CHANNEL].[ALL CHANNELS].[DIRECT SALES]";"[VALTYPE].[VARIANCE].[ACTUAL]")

INFOR.READ.VALUE formula

This example shows how INFOR.READ.VALUE reads value from a cell in the Analysis cube from the database of the Samples application. The cell coordinates are Units from the Measure dimension, Q1 2015 from the Period hierarchy, Body Shops from the POS hierarchy, AS600/175 from the Product hierarchy, California from the Region hierarchy, Direct Sales from the Channel hierarchy, and Actual from the VALTYPE hierarchy.

=INFOR.READ.VALUE("BIBESTPR_BESTPRACTICESOLAP";"ANALYSIS";"VALUE";"[MEASURE].[UNITS]";"[PERIOD].[ALL YEARS].[2015].[2015_Q1]";"[POS].[ALL POS].[BODYSHOPS]";"[PRODUCT].[ALL TIRES].[CAR TIRES ALL SEASON].[AS600 AQUACHRONO].[AS600/175]";"[REGION].[ALL REGIONS].[NORTH AMERICA].[USA].[CALIFORNIA]";"[CHANNEL].[ALL CHANNELS].[DIRECT SALES]";"[VALTYPE].[VARIANCE].[ACTUAL]")

Note: Samples can be enabled only in on-premises environments. If you do not have access to Samples, a summary description of the structure of the database is given for your reference.

INFOR.WRITE.VALUE formula

This example shows how you can use INFOR.WRITE.VALUE to write back a value into the cell in the specified coordinates for specific data connection and cube. In this example it writes back 1000 and as this target cell is not a base cell, the value is splashed using the default splashing operation, Allocation, WeightedOrEqual distribution.

=INFOR.WRITE.VALUE(1000,"BIBESTPR_BESTPRACTICESOLAP","ANALYSIS","VALUE","[MEASURE].[UNITS]","[PERIOD].[ALL YEARS].[2015].[2015_Q1]","[POS].[ALL POS].[BODYSHOPS]","[PRODUCT].[ALL TIRES].[CAR TIRES ALL SEASON].[AS600 AQUACHRONO].[AS600/175]","[REGION].[ALL REGIONS].[NORTH AMERICA].[USA].[CALIFORNIA]","[CHANNEL].[ALL CHANNELS].[DIRECT SALES]","[VALTYPE].[VARIANCE].[ACTUAL]")

You can also use a writeback command, in this case allocation with equal distribution, to write back a value of 100 with this formula:

=INFOR.WRITE.VALUE("#E100","BIBESTPR_BESTPRACTICESOLAP","ANALYSIS","VALUE","[MEASURE].[UNITS]","[PERIOD].[ALL YEARS].[2015].[2015_Q1]","[POS].[ALL POS].[BODYSHOPS]","[PRODUCT].[ALL TIRES].[CAR TIRES ALL SEASON].[AS600 AQUACHRONO].[AS600/175]","[REGION].[ALL REGIONS].[NORTH AMERICA].[USA].[CALIFORNIA]","[CHANNEL].[ALL CHANNELS].[DIRECT SALES]","[VALTYPE].[VARIANCE].[ACTUAL]")