Local 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.
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: You can also create a grouping local member in the Member Selector, when grouping members by property.
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 .
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.
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
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.
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.
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
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).
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
The rows and columns that are used as separation (shift) between the data grid and the row or column axis are ignored.
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
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.
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.
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.
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.