On the EPM - Sheet Options window, you can turn on Member Recognition by choosing the checkboxes for Activate Member Recognition and Activate Local Member Recognition.
Figure 95: Member and Local Member Recognition
Member Recognition allows you to type the Dimension Member values in your worksheet. If the system recognizes your typing, you will see a green flash in the background for a fraction of a second, then the Dimension Member will appear. If the system does not recognize your typing, the EPM - Ambiguous Name window will appear with a list of members matching your criteria.
Local Member Recognition allows you to create local members for your report. For example, you can enter a blank column and use the EPMMemberProperty function to bring back the Account Type of the Account dimension, so that you can whether it is an expense or income account.
Exercise 5: Member Recognition
Exercise Objectives
After completing this exercise, you will be able to:
• Select members based on a property.
• Use Local Member Recognition.
• Create a report using Member Recognition.
Business Example
You have created reports with the EPM add-in and the EPM Pane. Now you want to use the Member Recognition functionality.
Task 1:
In this exercise, you will select members based on a property and use Local Member Recognition.
1. Go to the Excel client for your Sales model in your BPC430_## environment.
Note: If you do not have a connection for the Sales model, use the instructions in Exercise #2 to create it.
Note: If you are ALREADY in Excel, go to Step #2.
2. Create a new blank workbook.
3. Choose the Sales - BPC430_## Connection.
4. Set your EPM Context.
5. Create a report with the P_ACCT dimension in the Row Axis and Time in the Column Axis.
6. For the P_ACCT dimension, use the properties filter to select only EXP and INC accounts.
7. Turn on Local Member Recognition.
8. Insert an empty column and place your EPM Member Property formula there.
9. Close the report without saving
Continued on next page
Task 2:
Use Member Recognition to create a report 1. Create a blank worksheet
2. Choose the Sales - BPC430_## Connection 3. Turn on Member Recognition
4. Create a report for Dogball and 2011.
5. Close the report without saving
Solution 5: Member Recognition
Task 1:
In this exercise, you will select members based on a property and use Local Member Recognition.
1. Go to the Excel client for your Sales model in your BPC430_## environment.
Note: If you do not have a connection for the Sales model, use the instructions in Exercise #2 to create it.
Note: If you are ALREADY in Excel, go to Step #2.
a) From your web client's Start Page, choose EPM Office Add-in Excel.
Excel opens and the EPM - Connection Manager appears.
b) Choose the Sales - BPC430_## connection.
c) Click OK.
d) If you are prompted, logon again with your user Id and password as shown below:
User ID Password
BPC-## training
2. Create a new blank workbook.
a) Create a blank workbook by choosing the Office button and choose New.
b) Choose OK or Create.
3. Choose the Sales - BPC430_## Connection.
a) On the EPM Pane, under Active Connection choose the Sales - BPC430_##
connection.
Continued on next page
4. Set your EPM Context.
a) Set your EPM Context to the following:
Dimension Member ID - Description CATEGORY Actual - Actual from GL
ENTITY STORE1 - Store1
PRODUCT FISH - All Fish Products
P_ACCT EXTSALES - External Sales
RPTCURRENCY LC - Local Currency
TIME 2011.TOTAL - 2011
MEASURES PERIODIC - Periodic
5. Create a report with the P_ACCT dimension in the Row Axis and Time in the Column Axis.
a) From the EPM Ribbon choose New Report.
The EPM - Report Editor window opens on the Layout tab by default.
b) Drag and drop the P_ACCT dimension to the Row Axis Dimensions.
c) Drag and drop the Time dimension to the Column Axis Dimensions.
6. For the P_ACCT dimension, use the properties filter to select only EXP and INC accounts.
a) Open the EPM - Member Selector by clicking on the P_ACCT dimension.
The EPM - Member Selector window opens.
b) Deselect the current selection under Selected Members on the right by highlighting Context (External Sales), with the Relationship Member and Childrenand move it to the left with the blue arrow pointing left.
c) Choose the yellow icon.
The Member Filtering by Properties area appears.
d) In the first row, under Property choose Account Type.
f) Under Values choose EXP.
g) In the second row, under Property choose Account Type.
h) Under Operator choose = . i) Under Values choose INC.
Figure 96: EPM - Member Selector
j) Choose the Add Dynamic Filter button which will move this filter to the right under Selected Members
k) Choose OK to close the EPM Member Selector window.
l) Choose OK to close the EPM Report Editor window.
The Report is executed with all the EXP and INC accounts.
7. Turn on Local Member Recognition.
a) From the EPM Ribbon choose Options → Sheet Options The EPM - Sheet Options window opens
b) On the General tab, select the checkbox Activate Local Member Recognition (If not already selected).
c) Choose OK.
Continued on next page
8. Insert an empty column and place your EPM Member Property formula there.
a) Highlight column B and then right-click and choose Insert.
b) In cell B2 type =EPMMEMBERPROPERTY(“”,EPMMEM-BERID(A2),“Account Type”)(no dashes)
and then hit enter.
Note: EPMMemberProperty is equivalent to EVPRO which was used in the prior releases.
The first parameter “” references the current connection name.
You could have defined it if necessary such as “SALES”.
EPMMemberID does a look up for the ID of the member, and that is critical since this function requires the ID to display property values.
You will see the formula was copied down for you and you will see either
“EXP” or “INC” as the account types.
Also, in cell B1, the EPMLocalMember function has been generated.
9. Close the report without saving
a) Choose the Office button and choose Close The Microsoft Office window opens.
b) Choose No
Task 2:
Use Member Recognition to create a report 1. Create a blank worksheet
a) From the Office button choose New → Blank Workbook → Create 2. Choose the Sales - BPC430_## Connection
a) On the EPM Pane under Active Connection choose the Sales - BPC430_##
connection.
3. Turn on Member Recognition
a) From the EPM Ribbon choose Options → Sheet Options.
The EPM - Sheet Options window opens.
b) On the General tab, select the checkbox Activate Member Recognition. (If not already selected)
c) Choose OK.
4. Create a report for Dogball and 2011.
a) Place your cursor in cell B4 and type DOGBALL.
You will see a flicker of green light, then the system will validate your entry.
b) Place your cursor in cell C3 and type 2011.
You will see a flicker of green light, then the system will validate your entry, and retrieve the transaction data.
c) Place your cursor in cell B5 and type food.
Since there are multiple products with “food”, the EPM - Ambiguous Name window appears where you can select from a list of products that have the string food in their ID or description.
d) Double-click Pet Type Consolidation, All Bird Products, Gourmet Bird Food
Gourmet Bird Food is added to your report.
5. Close the report without saving
a) Choose the Office button and then Close The Microsoft Office window opens.
b) Choose No
Lesson Summary
You should now be able to:
• Create a report using Member Recognition
• Create a report using Local Member Recognition Related Information