Skip to Content

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.

 

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.

 

Report Editor

 

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.

 

Member Selector

 

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.

 

Member Selector

 

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.

 

Sheet Options

 

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.

 

Sheet Options

 

Your input form should look like below.

 

Input Form layout

 

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.

 

 O2 and P2 cells

 

 Then, in cell A1, type-in following formula and press Enter:

=EPMCopyRange(“000”,TRUE,B2:P2)

 

EPMCopyRange Formula

 

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.

 

Input Form Layout

 

At that point, type-in following comments and click the Send Data button:

 

  1. APL Comment in cell O13
  2. FPS Comment in cell O14
  3. RPG Comment in cell O15
  4. SPT Comment in cell O16
  5. STR Comment in cell O17

 

Result should be this one:

 

Result

 

Finally, hide column P and rows 1 to 4 for visibility purpose.

 

Final Result

 

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. 

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

    1. Patrick Favre Post author
      Hi Andrea,

      Yes, this EPM Add-in is available for download from the SAP Service Market Place.

      The EPM add-in is designed to give access to SAP
      Business Objects EPM Solutions’ product data (FC, BW cubes, SSM, BPC MS, and BPC NW).

      But please notice that Business Planning and Consolidation 10 for both Microsoft and Netweaver plateform are still in ramp-up (plateform used for this weblog).

      Kind Regards,

      Patrick

      (0) 
  1. Former Member

    Hi,

    Firstly thank you for sharing this with us. I tried to do same as what you did. But at the end, i could not save my comments.With pop-up message it is written that “There is no data to save”, although  i wrote new comments in the cells.

    I couldn’t find where the problem is.

    PS: I use BPC 10.0 for Netweaver, SP 8.

    Thanks in advance..

    Cigdem

    (0) 
  2. Former Member

    Hi Patrick,

    Thanks for the guide, it was useful to have a walk through.

    Do you know how you would apply formatting such as bold for calculated accounts, for columns that are populated using the EPMCopyRange() function?

    I have a topic here that is discussing how to apply advanced formatting when using the EPMCopyRange():

    http://scn.sap.com/thread/3232743

    Any help you can give would be much appreciated.

    Many Thanks!

    Nick

    (0) 
  3. Former Member

    I got a new online most entertaining fun zone. You can enjoyed free online games 3456789 learn motivational books and make funny cartoon really this is mind blowing fun zone. You can search any thing for fun. Thank you so much.

    (0) 

Leave a Reply