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.

BPC Script logic for Dummies? (Part 2)

I explained basic 3 parts of script logic in the last post. It was Scoping, Calculation and writing.

We will find out more advanced features for scoping In this post.

1. Scope using member property

      We found how to use *XDIM_MEMBERSET last time.

      *XDIM_MEMBERSET is for scoping based on the member ID.
      What if user wants to scope members based on a specific property value?
      For example, a user wants to filter Account dimension members those are Asset.
      To achieve this, we need to use ACCTYPE property which has the type value of account.
      AST is the value for ASSET account. (Note: Value is based on the APSHELL of BPC.)
      The command is *XDIM_FILTER.
      The usage is

*XDIM_FILTER <DIMENSIONNAME> = [DIMENSIONName].Properties("Property name") = "Property value"

      So above example can be written as below.
      *XDIM_FILTER ACCOUNT = [account].properties(“ACCTYPE”)=”AST”

      Let's say Account dimension has 3 members as below.

         ID           ACCTYPE
      Extsales          INC
      CASH              AST
      TAXES             EXP
      NETINCOME    INC

      Then about *XDIM_FILTER_ACCOUNT will select CASH member only.

      Let's assume If you already used multiple *XDIM_MEMBERSET command

      and below are selected data from the fact tables.

      *XDIM_MEMBERSET TIME = 2011.JAN
      *XDIM_MEMBERSET CATEGORY = BUDGET

     <Result>
     EXTSALES , 2011.JAN, BUDGET, 9000
     CASH     , 2011.JAN, BUDGET, 3000
     TAXES    , 2011.JAN, BUDGET,  800
     NETINCOME, 2011.JAN, BUDGET, 1500

     Now if you add *XDIM_FILTER against ACCOUNT dimension.

     *XDIM_MEMBERSET TIME = 2011.JAN
     *XDIM_MEMBERSET CATEGORY = BUDGET
     *XDIM_FILTER ACCOUNT = [account].properties(“ACCTYPE”)=”AST”

     Only one record will be selected from above result because CASH is

     the only account member that has 'AST' value of ACCTYPE property.

     <Result>
     CASH     , 2011.JAN, BUDGET, 3000

2. Scope using member value
      We just figured out how to scope the source data based on the property.
      Then someone might ask this question.
      "Can we scope based on the value?

      For example, can we select all data that an account value greater than 100?

      Of course, we can do it.
      The command is *XDIM_GETMEMBERSET. Unlike other command,

      it needs  *ENDXDIM command to specify data.

      Here is the grammar of XDIM_GETMEMBERSET and an example.

      *XDIM_GETMEMBERSET {dimension} [={member set}]
           [*APP={application}] //optional
           [*XDIM_MEMBERSET {dimension} [={member set}] //as many of these as needed
           [*QUERY_TYPE= 0 | 1 | 2] //optional
           *CRITERIA {expression} //required
      *ENDXDIM

      *XDIM_GETMEMBERSET P_CC=[P_CC].[H1].[AAPJ].CHILDREN
           *APP=PLANNING
           *XDIM_MEMBERSET P_DataSrc=INPUT
           *CRITERIA [P_ACCT].[H1].[CE0001000]>1000 
      *ENDXDIM  

      It will get the data and those are..

           a. Children member of AAPJ in the P_CC dimension.  AND
           b. from the PLANNING application  AND
           c. INPUT member of P_Datasrc dimension AND
           d. CE0001000 member's value of the P_ACCT dimension should be greater than 100000

      Let's Assume Fact table has below records.

      CE0001000, 2011.JAN, ACTUAL, INPUT, KOREA , 2500
      CE0002000, 2011.JAN, ACTUAL, INPUT, CHINA , 5000
      CE0001000, 2011.JAN, ACTUAL, ADJ  , CHINA , 3000
      CE0002000, 2011.JAN, ACTUAL, INPUT, JAPAN , 1999
      CE0003000, 2011.JAN, ACTUAL, INPUT, JAPAN , 2222
      CE0001000, 2011.FEB, BUDGET, ADJ  , KOREA ,  345
      CE0001000, 2011.FEB, BUDGET, INPUT, TURKEY, 1999
      CE0003000, 2011.JAN, ACTUAL, INPUT, TURKEY, 1100
      CE0001000, 2011.FEB, BUDGET, INPUT, CHINA , 1050
      CE0001000, 2011.FEB, BUDGET, INPUT, JAPAN ,  450

      Which records will be selected?

      The answer is         
      CE0001000, 2011.JAN, ACTUAL, INPUT, KOREA, 2500
      CE0001000, 2011.FEB, BUDGET, INPUT, CHINA, 1050

      Below records will not be selected even though P_ACCT is CE0001000
      because its value is less than 1000
      or Datasrc is not INPUT
      or it is not the child member of AAPJ (Asia Pacific)
      CE0001000, 2011.JAN, ACTUAL, ADJ  , CHINA , 3000  (datasrc is not input)
      CE0001000, 2011.FEB, BUDGET, ADJ  , KOREA ,  345  (datasrc is not input)
      CE0001000, 2011.FEB, BUDGET, INPUT, TURKEY, 1999  (Turkey is not child member of AAPJ)
      CE0001000, 2011.FEB, BUDGET, INPUT, JAPAN ,  450  (Value is less than 1000)

      Here are some important Notes for using this command.

      Note 1: This command works only for BPC MS.
      Note 2: if you don't specify each dimension's scope, it will be performed in the

           corresponding members of the pre-selected region which is defined with

           DIMMEMBERSET of previous line or Passed by Data Manager.

      Note 3: This command will generate MDX statement so it takes more time to execute.
                 if your dataset has only base members, you can use

                      *XDIM_GETINPUTSET. (please refer help file)

3. When user wants to add more members on top of current scoped data.

      Let's say a user wants to add USASales entity on top of predefined memberset.
      In that case user defines as below.

      *XDIM_ADDMEMBERSET Entity = USASales

      The main reason why we need this is
          a. Sometimes XDIMMEMBERSET doesn't work with some specific functions like BAS(parent).
             For example, IN BPC NW,  *XDIM_MEMBERSET = BAS(US),CANADA will not work.
             Therefore, we should use *XDIM_MEMBERSET and *XDIM_ADDMEMBERSET.

             *Note: In BPC MS, BAS() will not work with XDIM_MEMBERSET.

          b. if user always wants to run a specific memberset whenever logic runs,

              should use *XDIM_ADDMEMBERSET

4. Dynamic Scope and saving it to a variable.

      Sometimes we need to save our scoped data into a script logic variable.
      But... what if your dimension members are updated frequently?
      As I know, almost every customer updates their dimension at least once a month.

      If customer changes their dimension members, what will happen in your script logic?
      You can use *Filter but sometimes it may not work all the time.

      Then we can use *SELECT and *MEMBERSET command as a dynamic scope tool. 

      Like other script engine, Logic script also supports Variable to save some data.
      The Variable is defined using % symbol.Here are some examples, %MYTIME% , %CUR% etc.

      So how can we save some data into the variable and when it can be used?

      Usually, the variable can be filled using *SELECT command and *MEMBERSET command.
      Both of them is scope command but *SELECT will be faster because it will create SQL statement.
      Here is the grammar of both commands.
          *SELECT ({variable}, {What}, {From}, {Where})
          *MEMBERSET({variable}, {member set in MDX format})

      Let's see how to use *SELECT command.

           *SELECT(%REPORTING_CURRENCIES%, “ID”, “CURRENCY”, “[GROUP] = 'REP'”)

      This command will get the 'member ID(what)' from the 'currency dimension(From)' that the

      GROUP property has the value 'REP' (where).

      Actually, it will create a SQL statement as below

SELECT ID from mbrCurrency where [GROUP] = 'REP'

      After it executes above SQL command, all result will be saved into

      %REPORTING_CURRENCIES% variable.

      Here is an example of *MEMBERSET which will make same result but execute MDX statement

      instead of SQL.
           *MEMBERSET(%REPORTING_CURRENCIES%, “filter{[CURRENCY].members,

           [currency].properties(“GROUP”)=”REP””)   
      The variable can be used anywhere in the logic, like in this example:
                        *XDIM_MEMBER_SET CURRENCY=%REPORTING_CURRENCIES%

      Let's assume Currency dimension has below members.
          ID           GROUP

         USD            REP
         EUR            REP
         KRW           
         JPY

      Then above statement will be converted as
                        *XDIM_MEMBER_SET CURRENCY = USD,EUR   

      When you define and fill in data using *SELECT and *MEMBERSET,

      please remember this as 'MEMBERSET Variable'

      Note: MEMBERSET command is only supported by MS version.

We reviewed key command of scoping today.

We will review advanced calculation command and control command like *IF or *FOR - *NEXT in the next post.

If you have questions or want to know something about it, please leave a comment.

Thank you.

When to use the Write-back badi in BPC7.x NW

SAP Business Objects Planning and Consolidation version for Netweaver effectively leverages the Netweaver infrastructure and we can use the Business Add-In functionality (http://help.sap.com/saphelp_nw04/helpdata/en/e6/d54d3c596f0b26e10000000a11402f/content.htm). In this blog we will try to explore the features of write-back badi and discuss when we can use it to our advantage in the implementation projects.

You are in the best position to use the write back badi if you are on SAP Business Objects Planning and Consolidation 7.5 version for Netweaver. At present, the latest support pack for 7.5NW is SP4 but even if you are on an earlier SP level, you can still use the write-back badi. If you are on BPC7.0NW though, you should be at least on SP4 or higher to take advantage of the write-back badi.

One may ask, is it mandatory for us to use a write-back badi to write any records using BPC? The answer is ‘no'. It is not mandatory for us to use write-back badi if we don't want to.  Even without a write back badi, several BPC customers have been entering data through input schedules, data manager packages, comments etc. Write-back badi is generally of use when we want to write-back records in a fashion not supported by the regular write back operations.

For example, let us consider a simple case of budgeting by cost centers. Let us consider that our Entity type dimension is Cost_Center and it has a hierarchical structure with Worldwide as the top node, followed by Europe, Asia and North America as lower level nodes. Under each of these nodes, let us assume that we have individual countries. So North America may have USA, Canada and Mexico; whereas Europe may have UK, Germany, France and Asia may have Singapore, India, China etc. Hence, in this dimension, the individual countries are the lowest level nodes and typically we can do planning ONLY at the lowest level nodes. So in a normal situation, we can plan at the level of India, USA, Canada, Germany etc but we may not be able to plan at the level of Europe, North America, and Asia. This is good if we want to do bottom-up planning. If we have to do top-down planning where it may become important to enter the data at the higher level nodes, then the normal write back operation may not allow us to do so. In such cases, write-back badi can be effectively used. With the write-back badi we can enter the plan data at a higher level node and the data will get stored at the base level dimension members by means of the disaggregation mechanism defined in the write-back badi. The disaggregation mechanism can be defined in the badi implementation. This may include different types of distributions, including even distribution, based on a percentage value which is maintained in a property, or distribution based on previous year's data, etc.  Some type of ABAP program could also be developed to allow maintenance of such distribution rules for parent level entries per Appset/Application/Parent Member.

Another example where write back badi may become necessary is where we have matrix security and the data entry has to be secured based on the matrix security. Let us take an example to clarify this scenario.

Let us consider that a member access profile (say MAP1) is set up as follows:

Category =       Plan -               Read/Write

Time =             2009.OCT -     Read/write

Time =             [ALL] -            Read only

The idea here is that the user should be able to read the plan values for all time periods but write to only 2009. OCT month plan values.

Now consider that there is another member access profile (say MAP2) that is set up as follows:

Category =       Forecast -                    Read/Write

Time =             [ALL] -                        Read/Write

The idea here is that the user should be able to write forecast values to all periods.

Now, if these profiles are assigned to two different users there is no issue. However if they are assigned to the same user, then there is a conflict. Clearly, the administrator of the security intends to RESTRICT writing of values to Plan category for time other than 2009.OCT. However what happens is that if the user who has both these member access profiles assigned, enters a Plan value for say 2009.SEP, it gets saved according to the rule of least restrictions by dimension - in this case the time dimension. This is not what the administrator might have intended.

In this case also the write-back badi can come to our rescue. With the write-back badi we can very effectively handle the situations like this.

The write back badi is a ‘pre-process' badi. This means that it is called before any other checks are processed. Since the write-back badi is called before the standard security checks, validation checks, work status checks etc, it can accomplish the tasks that are generally not possible with the standard write-back operations. The Enhancement Spot for the write-back badi is  ‘UJR_WRITE_BACK' and it includes filters for AppSet ID, Application ID, and Module ID.

Appset and Application IDs are self explanatory. The module can be either Manual planning, journals, data manager, comments or document modifications. This enables us to use a PRE-PROCESS method where the logic for pre-processing for the write back can be written. BAdI Implementation UJR_BADI_SAMPLE_DISAGGREGATE is shipped as an sample implementation for disaggregation.

There is also a how-to guide available for the step-by-step instruction for the write-back badi implementation for disaggregation. (http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f0309226-814f-2d10-61a6-ef8da29e3727?QuickLink=index&overridelayout=true)

Thus write-back badi can b

Technorati Tags: SAP BPC Best Practices sap bpc benchmarking sap bpc tutorial sap bpc examples outlooksoft best practices sap bps best practices sap bpc tips sap bpc standards sap bpc guidelines

e a very useful utility for us to leverage on out BPC NW implementation projects

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