Local Members - Examples

Calculate the sum of the sales performed in various periods of 2010 - Example 1

You want to calculate the sum of the sales performed for any time period displayed in the report.

In the report below, you have entered the following formula in cell E2: =SUM(B2:D2).

The Total local member sums all the time members currently displayed in the report, that is January, February and March.

	A	B	C	D	E
1		Jan. 2010	Feb. 2010	March 2010	Total
2	Sales	2134	2303	2405	6842

Note As you have specified a member range by entering =SUM(B2:D2), the selection is dynamic and if you remove February for example, the sum is updated accordingly. Also, if you add April, the sum is updated accordingly. If you had entered =B2+C2+D2, the sum would not be updated accordingly.

When you click Edit Report and select the Local Members tab of the Report Editor, you can see the following details on the local member:

the local member is attached to the Period hierarchy (the Hierarchy option is selected),
the keyword EPMSELECTEDMEMBERS is used in the local member formula =SUM(EPMSELECTEDMEMBERS(TIME))

Calculate the sum of the sales performed in various periods of 2010 - Example 2

Like in Example 1, you want to calculate the sum of the sales performed for any time period displayed in the report.

However, if other dimensions are in the same axis as the Time dimension, you need to use the keyword EPMALLMEMBERS in your local member formula: =SUM(EPMALLMEMBERS(TIME))

Calculate the difference between the forecast and the actual sales

You want to calculate the difference between the forecast sales and the actual sales.

In the report below, you have entered the following formula in cell D3: =C3-B3.

	A	B	C	D
1		Actual	Forecast	Difference
3	Sales	32510	30450	2060

When you click Edit Report and select the Local Members tab of the Report Editor, you can see the following details on the local member:

the local member is attached to the last member in the report, Forecast (the Member option is selected),
the keyword EPMMEMBER is used in the local member formula =EPMMEMBER([CATEGORY].[].[FORECAST])-EPMMEMBER([CATEGORY].[].[ACTUAL])

Note Since the local member is attached to the Forecast member, and provided that Actual is also displayed in the axis, the local member will always appear after Forecast for each occurrence of Forecast in the report (in the case of nested dimensions for example).

Calculate the percentage of changes in costs between 2005 and 2006

You want to calculate the percentage reflecting how personal costs have changed between 2005 and 2006.

In the report below, you have entered the following formula in cell F2: =E2/C2-1.

	A	B	C	D	E	F
1		Total 2005	Total 2005	Total 2006	Total 2006
2		Bonus Expenses	Personal Costs	Bonus Expenses	Personal Costs	(fn) Personal Costs
3	All Cost Centers	30000	25000	32000	28000	12%

When you click Edit Report and select the Local Members tab of the Report Editor, you can see the following details on the local member:

the local member is attached to the following combination of members (the Member Combination option is selected): Total 2006 and Personal Costs. The local member is displayed only if the members Total 2006 and Personal Costs are displayed in the same axis. If you enter this combination of members somewhere else in the report, the local member will also be displayed.
the keyword EPMTUPLE is used in the local member formula =EPMTUPLE([TIME].[PARENTH1].[2006.TOTAL];[P_ACCT].[PARENTH1].[CE0004000];)/EPMTUPLE([TIME].[PARENTH1].[2005.TOTAL];[P_ACCT].[PARENTH1].[CE0004000];)-1

Displaying the type of accounts of the members displayed in the row axis

You want to display the type account for each member displayed in the row axis of the report.

In the report below, you have entered the following EPM function in cell B2: =EPMMemberProperty("connection";A2;"Account type"). For more information on the EPMMemberProperty function, see EPMMemberProperty.

	A	B	C
1		(fn) Account	2010
2	Interest Income	INC	40
3	Tax Refund	INC	239
4	Personal Care	EXP	12
5	Vacation	EXP	202

When you click Edit Report and select the Local Members tab of the Report Editor, you can see the following details on the local member:

the local member is attached to the column axis (the Column Axis option is selected): for each member in the row axis, its type of account is displayed in a column.
the keyword EPMDIM_CURRENT_MEMBER is used in the local member formula =EPMMemberProperty(, EPMDIM_CURRENT_MEMBER(P_ACCT), "Account type")

Calculating any members that are displayed in specific rows or columns

You want to calculate the sum of the members that are displayed in the first and the second rows of the report.

In the report below, you have created a local member on the row axis by entering the following formula in the Local Members tab of the Report Editor: =EPMPOSITION(1)+EPMPOSITION(2)

The local member calculates the sum of members that are on the first and the second rows of the report.

The row axis displays context members. When you change the context, the row axis will update accordingly and the local member will continue to function.

In the context, 2007 is selected. As the row axis of the report below contains context members, 2007 periods are displayed.

A	B	C	D
1		Personal Costs	Other Costs
2	2007 S1	2345	345
3	2007 S1	5657	596
4	(fn) total	7002	941

You change the context and select 2008. The report is updated accordingly and the local member continues to function.

A	B	C	D
1		Personal Costs	Other Costs
2	2008 S1	2386	256
3	2008 S1	5231	632
4	(fn) total	7617	888

Note

If you attach this local member to a dimension/hierarchy, as explained in the first example, you can select any member of the dimension and the calculation will be performed. If you attach this local member to an axis, you display members in the axis for any dimension and the calculation will be performed.
You can also attach a local member to the row or column axis at a specific position. To do so, select the Use Position in Axis option in the Sheet Options. In our example, if this option is selected, you can see the local member details in the Local Members tab: the local member is attached to the column axis and inserted at position 3.