BPC 10 MS/NW - Sending and retrieving comments in an EPM Add-in input form

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:

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:

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.

Selection Screen in Tranformation file

To import the transaction data from BI cube, best option is make use of transformation file. In transformation file if we want to filter the records based on some conditions then we have to make use of option SELECTION where we will give the parameters as there is no option to pass the data from LoadInfoProvider Package to transformation file.

Now the problem comes, if we want to update BPC cube frequently. E.g. if we are uploading the Actuals from BI cube then this activity needs to be done monthly. And if, user is doing actual upload activity then there are chances of error as he might can remove some semi-colon or delete some other parameters. Also he might not have any interest in data which is visible in INSTRUCTION sheet of transformation file.

So best solution is we can provide the selection screen in transformation file itself with all the necessary parameters by using excel functionality. Following is the procedure:

1.       In the Transformation file, add another sheet e.g. INPUT.

2.       Give the selection parameters in the added sheet.

E.g.

Company
TEST

Financial Year
2011

Fiscal Period/Month
5

3.       Now use the excel functionality (& or concatenate) in SELECTION option in the Instruction sheet of transformation file which contains all the details like options, mapping and conversion.

="SELECTION= 0COMP_CODE,"&INPUT!F6&";0FISCYEAR,"&INPUT!F7&";0LOC_CURRCY,INR;0FISCPER3,"&INPUT!F8&";"

So I am referencing the INPUT sheet data into INSTRUCTION sheet. So whenever user selects the Financial Year & Period, values will get changed automatically in INSTRUCTION sheet.

Now we can hide INSTRUCTION sheet and only INPUT sheet will be visible to user. So he just selects the required data and validates and uploads

SAP BPC Tutorials | SAP BPC Training | SAP BPC Interview Questions |SAP BPC Books