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.

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.

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  Refresh  Refresh Worksheet  or  Refresh  Refresh Workbook . 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 or collapse action when the sheet option Perform Worksheet Refresh on Expand/Collapse is selected. See example below. We recommand that you use this option if you use reports on which you perform a lot of expand or collapse 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 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  Refresh  Refresh Worksheet  or  Refresh  Refresh Workbook . 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 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  Refresh  Refresh Worksheet  or  Refresh  Refresh Workbook : 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, since a worksheet refresh is performed on the expand action, data and comments are displayed.