EPM Functions

The EPM functions are formulas that enable you to retrieve specific information or data and to make a report behave in a certain way, providing power and flexibility to reports.

You can enter an EPM function directly in the formula bar.
To make it easier to create and edit formulas and minimize typing and syntax errors, use Microsoft Office Excel Formula AutoComplete. After you type =epm (or =EPM, as it is not case-sensitive), a dynamic dropdown list of all the EPM functions is displayed below the cell.
You can also use the Microsoft Office Excel standard Insert Function dialog box by clicking the Insert Function button from the EPM tab of the ribbon.
The EPM dedicated functions are gathered under the EPMFunctions category.

Note You should deactivate the Member Recognition and the Local Member Recognition functions when using the EPM functions.

Note General notes:

The text entered in formulas is not case-sensitive.
The string parameters must be entered between the "" characters. For example: "account".
The separator character used to separate the parameters of a function depends on the "list separator" defined in your local settings.

An EPM function is created by default on the active connection of the sheet. The default connection is displayed in the Active Connection dropdown list of the EPM pane. Using this dropdown list, you can directly select another connection or you can select the command Select Another Connection, then select another connection in the Connection Manager that opens.

Note For technical reasons, the functions that are not supported on a specific data source are not greyed out or hidden in the EPMFunctions category. All the functions are available for selection, regardless of the data source.

Note The FPMXLClient.TechnicalCategory category is only used internally and must not be used by other users.

Note If you work with reports that have been created with previous versions of Planning and Consolidation (before version 10), some functions beginning with Ev continue to work but their names are not automatically converted into EPM. From version 10, the names of the functions begin with EPM. All the functions beginning with Ev are grouped under the EVFunctions (Deprecated) category. For more information on the former names and the corresponding new names, see the SAP BusinessObjects EPM Solutions, add-in for Microsoft Office What's New guide.

Note This note applies to Planning and Consolidation Local connections. However, when using both add-ins, Planning and Consolidation Interface for Excel 7.5 (on a 7.5 Planning and Consolidation model) and EPM add-in 10.0 (on a 10.0 Planning and Consolidation model), uncheck the Load EV Functions option in the User Options of the EPM tab. The EVFunctions (Deprecated) category does not appear. This prevents conflicts between EV function names.

Note

The following Ev functions are not supported in the EPM add-in: EvALK, EvASV, EvBLK, EvCLK, EvDLK, EvENE, EvEXP, EvINP, EvLIK, EvLST, EvMEM, EvMSG, EvNXP, EvPLK, EvPOV, EvPXR, EvSEN, EvSET, EvSLK, OsAMT.
EvMNU name is still supported but its former parameters are not recognized by the EPM add-in. For more information about the new parameters, EPMExecuteAPI.

EPM Function Refresh

When creating or modifying EPM functions, they are executed immediately, with the exception of several functions.

The EPM functions that need to retrieve information from the server are executed only when you refresh the worksheet or the workbook by selecting Refresh or Start of the navigation path

Refresh

Refresh Worksheet End of the navigation path

Refresh

Refresh Workbook End of the navigation path

. A simple refresh of the report does not execute the EPM function, and EPM function data is not displayed in the report until you perform a refresh of the worksheet or workbook. See the example below.

Note The following actions automatically refresh the selected report (not the whole worksheet): Expand, Collapse, Keep Member, Exclude Member, changes in the Report Editor, changes in the page axis.

However, the whole worksheet is automatically refreshed on an expand, a collapse or a member insertion action when the sheet option Perform Worksheet Refresh on Expand/Collapse and Inserted Members is selected. See example below. We recommand that you use this option if you use reports on which you perform a lot of expand, collapseor member insertion actions; or if you use reports that contain local members with EPM functions that retrieve data or comments.

Note When the sheet option Perform Worksheet Refresh on Expand/Collapse and Inserted Members is selected, the refresh time can be longer since the whole worksheet is refreshed.

Example You want to retrieve the comments for the accounts that are placed in the rows of the report. You create a local member that contains the EPMCommentPartialContext function.

Click Refresh or Start of the navigation path

Refresh

Refresh Worksheet End of the navigation path

Refresh

Refresh Workbook End of the navigation path

. The data and comments are displayed from the server.

	2011 Total	fn (local member with EPMCommentPartialContext)
Personnel Costs	2356	ok

You expand "Personnel Costs".

By default - provided that the sheet option Perform Worksheet Refresh on Expand/Collapse and Inserted Members is not selected, the expand action automatically performs a refresh of the current report and not of the current worksheet. As the EPMCommentPartialContext retrieves comments from the server, the comments are not displayed at this stage. It is the same for the data. #RFR is displayed instead in the cells. Therefore, your report contains a mix of data, comments and #RFR: this is the default behavior. To display all data and comments, you need to perform a refresh of the whole worksheet or workbook.

	2011 Total	fn (local member with EPMCommentPartialContext)
Personnel Costs	2356	ok
Bonus Expense	#RFR	#RFR
Wages and Salaries	#RFR	#RFR
Social Contributions	#RFR	#RFR

Click Refresh or Start of the navigation path

Refresh

Refresh Worksheet End of the navigation path

Refresh

Refresh Workbook End of the navigation path

: data and comments are displayed and occurrences of #RFR are removed from the cells.

If you select the sheet option Perform Worksheet Refresh on Expand/Collapse and Inserted Members, since a worksheet refresh is performed on the expand action, data and comments are displayed.

Referencing an EPMOlapMemberO

In an EPM function formula, when you reference a cell that contains a member placed in one of the three axes of the report (meaning a member identified by =EPMOlapMemberO), you must use the EPMMemberID function in the two following cases:

if the ID and the description of the EPMOlapMemberO are displayed in the report,
if only the description or the ID of the EPMOlapMemberO is displayed in the report and if the description or the ID is not unique in the data source.

In other cases, it is still recommended that you use the EPMMemberID function.

Example example with the EPMMemberProperty function

Do not define the function as follows: =EPMMemberProperty("connection1"; B4; "HLEVEL")

Define the function as follows: =EPMMemberProperty("connection1"; EPMMemberID(B4); "HLEVEL")

Note You can also specify that for each EPM function formula that you create, when you reference a cell that contains an EPMOlapMemberO member, the "EPMMemberID" function is automatically added. To do so, select the Use EPMMemberID in EPM formulas option in the User Options. For example, if you reference cell A6, when this option is selected, A6 is automatically replaced by EPMMemberID(A6) if A6 contains an EPMOlapMemberO function.

For all the EPM functions that have been created when the option was not selected, you can scan all the formulas of the current worksheet. For any cell references containing members, the EPMMemberID function is added. To scan all the formulas, select Start of the navigation path EPM Next navigation step More Insert EPMMemberID in Formulas End of the navigation path .

Note The Use EPMMemberID in EPM formulas option and the Insert EPMMemberID in Formulas command apply to both EPM functions and local members.

Note However, note that selecting this option may lower performances.

Retrieved Members

The EPM add-in searches for members according to the following order: Full unique name, ID, Description.

Example the ID of member M equals the description of member W. When you enter the description of member M as the parameter of an EPM function, member W will be retrieved, as the system searches for IDs first. The member retrieved is the one for which you have entered the ID.

For more information about the names of members, see Display Name.

Use of Volatile Functions

As mentioned in the Microsoft Excel documentation, the use of too many volatile functions can lower the recalculation performances.

As a reminder, the following Microsoft Excel functions are volatile: NOW, TODAY, RAND, OFFSET, INDIRECT, INFO (depending on its arguments), CELL (depending on its arguments).