Show TOC

Function documentationLocal Members

 

You can create members mainly for calculations on the current report. These members are called local members. Local members have the same behavior as any other member, but they contain standard Microsoft Office Excel formula or EPM functions.

Features

There are two ways of creating local members:

  • Enter a Microsoft Office Excel formula

    When the Local Member Recognition is activated, all Microsoft Office Excel formulas are automatically converted into local members. You can then view and modify the local member on the Local Members tab page of the Report Editor.

  • Create a local member directly on the Local Members tab page of the Report Editor.

    Note Note

    Note: You can also create a grouping local member in the Member Selector, when grouping members by property.

    End of the note.

Local members are specific to the current report. If you want to reuse a local member in other reports for the same sheet and connection couple, you can convert the local member into a custom member. To convert a local member into a custom member, right-click on the local member in the report and select Start of the navigation path EPM Next navigation step Convert to Custom Member End of the navigation path.

In case of conflicts between local members on row and column axis, local members on the columns axis override the ones on the row axis by default. You can change the precedence by selecting the Apply to rows as a priority on the Local Members tab page of the Report Editor.

You can apply specific formatting to local members.

Keywords and Attachment

A local member is identified by a formula using one of the keywords below:

  • EPMMEMBER, to attach the local member to a specific member

  • EPMTUPLE, to attach the local member to a combination of members belonging to nested dimensions

  • EPMSELECTEDMEMBERS, to perform a calculation (such as sum or average) on all the members displayed in the report for a specific dimension

    The axis in which the dimension is placed must contain only one dimension

  • EPMALLMEMBERS, to perform a calculation (such as sum or average) on all the members displayed in a row or column axis, whether the axis contains one or several dimensions

  • EPMDIM_CURRENT_MEMBER, to attach the local member to the current axis

  • EPMPOSITION(n), to reference a row or column of the report, not a member

A local member is attached to one of the following items:

  • Member combination

    The axis to which the formula applies contains more than one dimension, and you have entered a formula that references two different members in the outer dimension

  • Row or column axis

    A formula that gives information about the members in the opposite axis.

  • A member

    The local member is displayed after the member to which it is attached.

  • A position

    The local member is attached to a position in the report, not to a specific member.

  • Another local member

    The local member is displayed after the local member to which it is attached.

Alternatively, you can ask that any formula is converted into a local member that is attached to the row or column axis at a specific position, meaning the local member is automatically detected as an EPMPOSITION type of local member.

Note Note

When a local member references the current report and another cell outside of the report, the member is automatically detected as an EPMPOSITION type of local member, even though the Use Position in Axis option is not selected in the Sheet Options dialog box.

End of the note.
Local Member Automatically Detected

You can insert a row or a column, enter text as the header of the row or column, then enter a formula. When you enter in a report a Microsoft Office Excel formula, provided that the Activate Local Member Recognition option is selected in the Sheet Options dialog box, the following happen:

  • The formula is automatically converted into a local member using a specific keyword

  • The local member is automatically attached to a specific item

Once a formula has automatically been converted into a local member, you can edit the local member on the Local Members tab page of the Report Editor. For example, you can see the keyword used in its formula and the item to which the local member is attached.

All Microsoft Office Excel formulas are converted into local members.

If you do not enter a text to describe the local member, a default name is generated, beginning with (fn).

If a member referred to in a local member is deleted, the local member becomes invalid

The local member position depends on the Totals Placement options selected in the Sheet Options dialog box.

A local member cannot be attached to a blank member.

References to more than 100 cells are ignored.

Local Member Creation

On the Local Members tab page of the Report Editor dialog box, you can modify the automatically converted local member or you can create a new local member.

To create a local member that answers your analysis needs, you must use appropriate keywords in the formula and you also need to attach the local member to the appropriate item. You can perform the following actions in the Local Members tab:

  • Modify or select the item to which the formula is attached.

  • Name the local member. The name will appear in the list of local members on the left side.

  • Enter a description. The description appears in the report.

  • You can enter a suffix, a prefix and also include the text of the outer member by selecting Outer Member in the dropdown list.

    Note Note

    When you have entered a formula in a cell and you have not entered a text to describe the local member, a default name is generated, beginning with (fn).

    End of the note.
  • Change the position of the local member, after or before the item to which the formula is attached. If the item to which the local member is attached is Dimension or Hierarchy (depending on the connection you use), Column Axis or Row Axis, you can also specify a position using the Insert at Position dropdown list.

    Note Note

    The rows and columns that are used as separation (shift) between the data grid and the row or column axis are ignored.

    End of the note.
  • Enable or disable a local member by checking the Enable option. When a local member is enabled, it is taken into account in the current report.

  • When you have finished defining a local member, click Add.

  • When you have modified an existing local member, click Save to save your changes.

    Note Note

    You can also create a local member that inserts a blank row or column to a specific member for example. On the Local Members tab page, simply enter a name for the local member, select the position where you want to insert the row or column and select the member to which you want to attach the blank row or column, using the ellipsis button. As this local member is attached to another member, it will move along with the member. Note that you can also insert a blank member that behaves like any other member and is not attached to a specific member.

    End of the note.

    Note Note

    You can reference another worksheet in a local member formula, provided that the name of the worksheet does not contain any numbers. Restriction: You cannot create a local member in a child report.

    End of the note.
Local Member Creation Using Excel Cell References

You can create local members whose formula use Microsoft Excel cell references and not EPM keywords (see above for keywords). Select the Use Excel Cell References option on the Local Members tab page of the Report Editor. The formulas of local members that are repeated will adjust and behave according to the Microsoft Excel relative references feature.

Local Member Attached to Another Local Member

You can attach a local member to another local member. To do so, create the local member, then select the Local Member option and select an existing local member in the Local Members list.