Introduction
Very frequently, while customers are opening their planning period to entity owners or managers, they would like to attach some comments, explaining their figures.
In the new EPM Add-in, if you use the standard way of inputting or retrieving comments (via the comment button), you are not able to see them directly in Excel cells, but in an Internet Explorer window instead.
In this blog, you will learn how to use the EPMCopyRange formula in order to dynamically send and retrieve comments in an EPM Add-in input form.
Create your Balance Sheet input form
Launch the EPM add-in and choose a valid Planning and Consolidation connection.
Once logged-on, click the New Report button.
Drag & Drop PRODLINE dimension in rows, TIME dimension in columns, and all other dimensions, ACCOUNT, CATEGORY, ENTITY, RPTCURRENCY, MEASURES, in page axis.
Click on PRODLINE dimension in row axis, select All Product Lines account and Member and Descendants as for the relationship, and click the OK button.
Then, click on TIME dimension in column axis, select 2012.TOTAL member and Member and Descendants as for the relationship, and click the OK button.
Finally, on your page axis, select Third Party sales member for your ACCOUNT dimension, Budget member for your CATEGORY dimension, United States member for your ENTITY dimension, LC member for your RPTCURRENCY dimension, and Periodic member for your MEASURES dimension.
Then, move the Total placements on the top right, by accessing the Sheet Options and selecting the Top radio button on the General tab. On the same tab, check the Use as Input Form box, to allow data input.
Then, on the Refresh tab, select the option Calculate parents in hierarchies (same as the SumParent option in former EVDRE reporting mode), and click OK.
Your input form should look like below.
Notice that APL member is calculated on the fly, as soon as you input a value in its children.
Building the EPMCopyRange formula in order to allow comment input in this input form.
Insert 5 new rows on the top of this sheet.
In cell P2, enter following formula:
=EPMSaveComment($O2,,$A2,$N$12,$A$6,$A$7,$A$8,$A$9,$A$10)
Then, choose a grey background in cell O2.
Then, in cell A1, type-in following formula and press Enter:
=EPMCopyRange("000",TRUE,B2:P2)
Note: “000” is the ID of your Default Report, TRUE is for rows expansion (FALSE would have been applied to the columns), and B2:P2 is the formatting range you’ve just defined for your data range.
Finally, type-in Comment input in cell O11, and Comment send in cell P11, and click the Refresh button.
At that point, type-in following comments and click the Send Data button:
- APL Comment in cell O13
- FPS Comment in cell O14
- RPG Comment in cell O15
- SPT Comment in cell O16
- STR Comment in cell O17
Result should be this one:
Finally, hide column P and rows 1 to 4 for visibility purpose.
You now have the last comment being displayed in the rows, following the defined expansion on the rows.
BONUS
You can use the same way of building the EPMCopyRange formula to retreive Work Status in an Excel cell.
In fact, by having your Entities being displayed on the row axis, you can use the EPMWorkStatus formula (instead of EPMSaveComment in the example above), as the source range for your EPMCopyRange.
Check the EPM Add-in online help for more detail about the EPMWorkStatus formula.
5 Comments