This documents helps us in knowing how to create custom measures in one system and retain them in usable format with our any changes when we transport them to another system  (say development system to Quality and or production) i.e, reuse the same in other system without creating / changing them again and again, helping us reduce manual effort. For this we came up with a Custom program approach which will update the measure formulae to usable format in current system.



How To…

Create,Transport and Reuse Custom Measures across different environments


Introduction:

In addition to the standard and user defined dimensions, BPC also requires a dimension called Measures. This dimension is included automatically in all application sets. It is not listed as a dimension in the Manage Dimensions task, but is displayed on the Current View bar in BPC for Excel. The Measures dimension allows you to change the view of your data between Periodic, Quarter-to-date (QTD), Year-to-date (YTD)).


Business Scenario: When do we go for Custom Measures?


BPC system provides pre-defined measures like Periodic, YTD and QTD. Whenever there is a requirement to have the custom calculation which is different from the provided measures we need to go for custom measures. For example, there might be a case where the Quarterly Value of a dimension member is an Average of the monthly values instead of addition. We would need to create custom measures for similar cases.

Now let’s say we are using Custom measures for a certain dimension based on some custom property which we have created; We need to specify the Technical Name of the property in the Custom measure formula.


Need for Custom Program:


During Transport from one system to another system, the custom measures are not updated as per the current system. (This happens because of the fact that we are using custom property for calculating the measure) i.e., in BPC, the technical names of custom made properties change when transported from one system to another.

So, manual intervention is required.


Proposed Solution:


To overcome this issue every time when a transport is made, we developed a Custom Transaction ZTBPC_FORMULAPP, which helps us in updating these custom measures. (i.e., overhead to maintain Custom Measures MANUALLY (Manual approach is not suggested) in Quality or Production Systems).

Why to run this T-Code?


To update Custom Measures for Environment after transport in target system

When to Use this T-Code?


We should run this transaction whenever there is a modification in the custom measure(s).

Advantage


Custom measures are updated automatically and no manual process is required apart from triggering the ABAP Program.


Step-by-Step Solution:

  1. We created a User defined Dimension FSGAAP similar to ACCOUNT Dimension.
  2. The Property GAT is similar to ACC Type property of ACCOUNT Dimension.
  3. We are reporting on FSGAAP, so we created Custom Measures AVGQTR and AVGYEAR. The Requirement being, if GAT is SKF (statistical account) then the quarterly value should be the average of the Monthly values otherwise, it will behave like normal ACCOUNT. Sshot01.jpg
  4. Step by Step Picture Guide:
    1. Go to SE38 Transaction and execute the Program UJA_MAINTAIN_MEASURE_FORMULASshot02.jpg
    2. Provide the Environment, Model & User details for which you wish to generate custom measure Sshot03.jpg
    3. Write the Formula for the desired custom property – Average for Quarter (Refer Appendix A) Sshot04.jpg
    4. Write the Formula for the desired custom property – Average for Year (Refer Appendix A) Sshot05.jpg
  5. In order to view the Custom measures generated, go to SE16 transaction and display the contents of UJA_FORMULA_APP table. Sshot06.jpg
    1. Provide the Appset ID, Application ID details and execute to view the table contents  Sshot07.jpg
    2. The newly generated formulae are successfully updated in UJA_FORMULA_APP table. Sshot08.jpg
  6. Once we have created the custom measure in the development system and we want to run a transport to Quality or Production system. The issue we might face is that the technical name of the custom property would be different in the new system. That would mean that we need to manually change the Custom measure formula every time we run the transport
    1. Note: How to collect Custom Measures & Transport o Target System – Check Appendix C
  7. To overcome this issue, we have developed a custom ABAP program which needs to be executed after the transport to update the Custom Measures formula in such a way that it would refer to the correct Custom Property Technical name as per the current system.
    1. Go to SE38, and create a program                                       Sshot09.jpg
    2. Write the code to change the Custom measures (Refer code is present in Appendix B.)
    3. Go to SE93 and create a Transaction Code using this program  Sshot010.jpg


How to Execute this Transaction?

  1. Go to SAP BW system ( Quality / Production) for BPC 10.0, run the transaction ZTBPC_FORMULAPP Sshot011.jpg
  2. Enter the Environment to which the custom measures need to be checked and updated. Sshot012.jpg
  3. After selecting the required Environment, Press F8 to execute Sshot013.jpg
  4. After execution you will see a successful message. Sshot014.jpg
  5. Go to SE16 transaction, Check the Contents of Table for Environment – ENVIRONMENTSHELL (desired environment) Sshot015.jpg
  6. Give the desired Environment                                                                      Sshot016.jpg
  7. Enter the formula name and execute                                     Sshot017.jpg
  8. Once executed, check the FORMULA_STAT  field for changes                         Sshot018.jpg

Appendix


Appendix A

Custom Measure Formulae

Average for Quarter

MEMBER [MEASURES].[AVGQTR] AS ‘IIF([%FSGAAP%].CURRENTMEMBER.PROPERTIES (“2/CPMB/EPP8TJE”)=”INC”,-[MEASURES].[/CPMB/SDATA],IIF([%FSGAAP%].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”SKF” AND [%TIME%].CURRENTMEMBER.PROPERTIES(“2/CPMB/TILEVEL”)=”QUARTER”,AVG([%TIME%].CURRENTMEMBER.CHILDREN,COALESCEEMPTY([MEASURES].[/CPMB/SDATA],0)),IIF([%FSGAAP %].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”EXP”,[MEASURES].[/CPMB/SDATA],IIF ([%FSGAAP%].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”AST”,([MEASURES]. [/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%FSGAAP%].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”LEQ”,-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES]. [/CPMB/SDATA])))))’;SOLVE_ORDER=3

Average for Year

MEMBER [MEASURES].[AVGYEAR] AS ‘IIF([%FSGAAP%].CURRENTMEMBER.PROPERTIES (“2/CPMB/EPP8TJE”)=”INC”,-[MEASURES].[/CPMB/SDATA],IIF([%FSGAAP%].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”SKF” AND [%TIME%].CURRENTMEMBER.PROPERTIES(“2/CPMB/TILEVEL”)=”YEAR”,AVG([%TIME%].CURRENTMEMBER.CHILDREN,COALESCEEMPTY([MEASURES].[/CPMB/SDATA],0))/3,IIF([%FSGAAP %].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”EXP”,[MEASURES].[/CPMB/SDATA],IIF ([%FSGAAP%].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”AST”,([MEASURES]. [/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%FSGAAP%].CURRENTMEMBER.PROPERTIES(“2/CPMB/EPP8TJE”)=”LEQ”,-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES]. [/CPMB/SDATA])))))’;SOLVE_ORDER=3

Appendix B

CUSTOM PROGRAM ABAP CODE:


***********************************************************************
* LOGIC : THIS PROGRAM IS USED TO MODIFY CUSTOM MEASURES
*              IN UJA_FORMULA_APP TABLE SAY WITH FORMULA NAMES

*              CONTAIN AVGQTR / AVGYEAR KEYWORDS (OR ANY OTHER KEYWORDS)
*              SELECTION CONDITION: USER PROVIDES APPSET_ID
***********************************************************************

REPORT ZBPC_ACTIVATE_FORMULAPP.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME.
PARAMETERS : P_APPSET TYPE UJ_APPSET_ID”SAP BUSINESSOBJECTS – APPSET ID
SELECTION-SCREEN END OF BLOCK B1.

*—————————————————————————-
*        DATA DECLARATION
*—————————————————————————-

DATA: L_VARKEY LIKE RSTABLEVARKEY.


DATA: GV_OBJNM   TYPE RSIOBJNM,    ” INFOOBJECT
            GV_APPSET 
TYPE UJ_APPSET_ID, ” APPSET ID
            GV_FORMULA
TYPE STRING,
            GV_NEW         
TYPE STRING,
            GV_OLD          
TYPE STRING,
            GV_LATER      
TYPE STRING,
            GV_FORMER   
TYPE STRING,
            GV_FLAG        
TYPE I.        ” CHANGE INDICATOR FLAG

CONSTANTS: GC_AVGQTR       TYPE RSTXTLG VALUE ‘AVERAGEFORQUARTER’,
                       GC_AVGYEAR     
TYPE RSTXTLG VALUE ‘AVERAGEFORYEAR’,
                       GC_E   
TYPE CHAR1   VALUE ‘E’,
                       GC_A   
TYPE CHAR1   VALUE ‘A’,
                       GC_S   
TYPE CHAR1   VALUE ‘S’,
                       GC_2   
TYPE CHAR1   VALUE ‘2’,
                       GC_3   
TYPE CHAR1   VALUE ‘3’,
                       GC_I   
TYPE CHAR1   VALUE ‘I’,
                       GC_TABNAME
TYPE RSTABLETABNAME VALUE ‘UJA_FORMULA_APP’.

FIELD-SYMBOLS: <GV_FORMULA> TYPE ANY,
                              <GS_FORMULA>
TYPE ANY,
                              <GV_FORMULA_NAME>
TYPE ANY.

DATA: GT_FORMULA   TYPE STANDARD TABLE OF UJA_FORMULA_APP,
            GT_APPENDTAB
TYPE STANDARD TABLE OF UJA_FORMULA_APP, ” ITAB
            GO_FORMULA  
TYPE REF TO DATA.


* CREATING WORK AREA FOR ITAB
           CREATE DATA GO_FORMULA LIKE LINE OF GT_FORMULA.
           ASSIGN GO_FORMULA->* TO <GS_FORMULA>.

TYPES: BEGIN OF T_OBJNM,
                     IOBJNM
TYPE RSIOBJNM” INFOOBJECT
                     TXTLG 
TYPE RSTXTLG,   ” LONG DESCRIPTION
             END OF T_OBJNM.

DATA: GT_OBJNM TYPE TABLE OF T_OBJNM,
            GS_OBJNM
TYPE T_OBJNM,
            GV_AVGQTR 
TYPE UJ_FORMULA_NAME VALUE ‘%AVGQTR%’,
            GV_AVGYEAR 
TYPE UJ_FORMULA_NAME VALUE ‘%AVGYEAR%’,
            GV_LEN  
TYPE I.


*———————————————————————-*
*   AT SELECTION-SCREEN OUTPUT
*———————————————————————-*
AT SELECTION-SCREEN OUTPUT.
LOOP AT SCREEN.
     IF SCREENNAME = ‘P_APPSET-LOW’(005).
          SCREENREQUIRED = GC_2.
          MODIFY SCREEN.
     ENDIF.                             ” IF SCREEN-NAME = ‘P_APPSET-LOW’.
ENDLOOP.
*———————————————————————-*
*       SELECTION SCREEN VALIDATION
*———————————————————————-*
AT SELECTION-SCREEN ON P_APPSET.

IF  SYUCOMM EQ ‘ONLI’(006).
     IF P_APPSET IS INITIAL. ” CHECK IF APPSET IS INITIAL
          MESSAGE ‘ENTER APPSET ID’(001) TYPE GC_E.
     ELSE.
          IF P_APPSET IS NOT INITIAL.
               SELECT SINGLE  APPSET_ID FROM UJA_APPSET_INFO
                                                              INTO GV_APPSET
                                                              WHERE APPSET_ID = P_APPSET.
               IF SYSUBRC <> 0.
                    MESSAGE ‘INVALID APPSET ID’(002) TYPE GC_E.
                    LEAVE LIST-PROCESSING.
               ENDIF.
          ENDIF.
     ENDIF.
ENDIF.
*———————————————————————*
*       F4 HELP
*———————————————————————*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_APPSET.

TYPES: BEGIN OF L_TAB,
  APPSET_ID
TYPE UJ_APPSET_ID,
END OF L_TAB.


DATA: GT_TAB TYPE STANDARD TABLE OF L_TAB. “DECLARE THE INTERNAL TABLE AS U LIKE
DATA: GT_RET TYPE DDSHRETVAL OCCURS 0 WITH HEADER LINE.

SELECT APPSET_ID FROM UJA_APPSET_INFO

                                   INTO TABLE GT_TAB. ” SELECTING AVAILABLE APPSETS


IF GT_TAB IS NOT INITIAL.
     CALL FUNCTION ‘F4IF_INT_TABLE_VALUE_REQUEST’  ” FUCNTION MODULE TO
          EXPORTING
                RETFIELD  
= ‘APPSET_ID’(007) “FIELD NAME IN YOUR VALUE TAB I.E IT_TAB
                VALUE_ORG 
= GC_S
          TABLES
                VALUE_TAB 
= GT_TAB[]
                RETURN_TAB
= GT_RET[].

     IF SYSUBRC EQ 0.
          READ TABLE GT_RET INDEX 1.
           P_APPSET
= GT_RETFIELDVAL.
     ENDIF.
ENDIF.
*——————————————————————–*
*     START-OF-SELECTION.
*——————————————————————–*
START-OF-SELECTION.

* GET THE SYSTEM MAINTAINED INFOROBJECT NAME FOR ACCOUNTTYPEFOR AVGQTR REPORT
*** SELCT FROM RSDIOBJT TABLE FOR INFOTYPE
SELECT IOBJNM TXTLG  ” INFOOBJECT DATA FROM RSDIOBJT
                                                                             INTO TABLE GT_OBJNM
                                                                           WHERE OBJVERS = GC_A
                                                                                AND   ( TXTLG = GC_AVGQTR ) OR ( TXTLG = GC_AVGYEAR ) .
IF SYSUBRC = 0.


*   GET ALL ITEMS WITH *AVGQTR* IN FORMULA NAME FOR THE SELECTED APPSET
     SORT GT_OBJNM BY IOBJNM.
     DELETE ADJACENT DUPLICATES FROM GT_OBJNM. ” DELETING REPEATED ENTRIES

*    FETCHING FORMULA DATA
     SELECT FROM UJA_FORMULA_APP
                         INTO TABLE GT_FORMULA
                         WHERE APPSET_ID = P_APPSET
                         AND ( FORMULA_NAME LIKE GV_ AVGQTR ) OR ( FORMULA_NAME LIKE GV_ AVGYEAR ).
ENDIF.

IF GT_FORMULA IS NOT INITIAL.
      GV_AVGQTR
= ‘AVGQTR’.
      GV_AVGYEAR
= ‘AVGYEAR’.
     LOOP AT GT_FORMULA ASSIGNING <GS_FORMULA>.
          CLEAR GV_OBJNM.
          ASSIGN COMPONENT ‘FORMULA_STAT’ OF STRUCTURE <GS_FORMULA> TO <GV_FORMULA>.
*      CHECK THE LAST FOUR CHARACTERS OF FORMULA_NAME AND THEN DECIDE THE INFOOBJECT VALUE
          ASSIGN COMPONENT ‘FORMULA_NAME’ OF STRUCTURE <GS_FORMULA> TO <GV_FORMULA_NAME>.

          IF <GV_FORMULA_NAME> CS GV_AVGQTR.
               READ TABLE GT_OBJNM INTO GS_OBJNM WITH KEY TXTLG = GC_AVGQTR.
                    IF SYSUBRC = 0.
                          GV_OBJNM
= GS_OBJNMIOBJNM.
                    ENDIF.
          ELSEIF <GV_FORMULA_NAME> CS GV_AVGYEAR.
               READ TABLE GT_OBJNM INTO GS_OBJNM WITH KEY TXTLG = GC_AVGYEAR.
                    IF SYSUBRC = 0.
                          GV_OBJNM
= GS_OBJNMIOBJNM.
                    ENDIF.
           ENDIF.

          IF GV_OBJNM IS NOT INITIAL.
*   ASSIGN AND CHANGE THE INFOOBJECT NAME IN THE FORMULA
               SPLIT <GV_FORMULA> AT GC_2 INTO GV_FORMER GV_LATER.
               SPLIT GV_LATER AT ‘”‘ INTO GV_OLD GV_LATER.
               IF GV_OLD NE GV_OBJNM ” COMPARE EXISTING AND NEW FORMULA
                     GV_FLAG
= 1. ” FLAG IS RAISED IF GV_NEW AND GV_OBJNM DIFFER
                    CLEAR : GV_NEW, <GV_FORMULA>.
                     MOVE GV_OBJNM TO GV_NEW.
                     CONCATENATE GV_FORMER GC_2 GV_NEW ‘”‘ GV_LATER INTO <GV_FORMULA>.
                     REPLACE ALL OCCURRENCES OF GV_OLD IN <GV_FORMULA> WITH GV_NEW.
                     APPEND <GS_FORMULA> TO GT_APPENDTAB.
               ENDIF. ”GV_OLD NE GV_OBJNM

           ENDIF”GV_OBJNM

     ENDLOOP.

ENDIF.

IF GV_FLAG IS NOT INITIAL.


      L_VARKEY
= SYMANDT.


* START MODIFYING THE DB TABLE


CALL FUNCTION ‘ENQUEUE_E_TABLE’
     EXPORTING
           MODE_RSTABLE  
= GC_E
           TABNAME       
= GC_TABNAME
           VARKEY        
= L_VARKEY
           X_TABNAME     
= ‘ ‘
           X_VARKEY      
= ‘ ‘
           _SCOPE        
= GC_2
           _WAIT         
= ‘ ‘
           _COLLECT      
= ‘ ‘
     EXCEPTIONS
           FOREIGN_LOCK  
= 1
           SYSTEM_FAILURE
= 2
          OTHERS         = 3.


IF SYSUBRC = 0.
* MODIFY USING THE INTERNAL TABLE GT_FORMULA
     MODIFY UJA_FORMULA_APP FROM TABLE GT_APPENDTAB.
IF SYSUBRC = 0.
      GV_FLAG
= 2.
ENDIF.


* END MODIFYING DB TABLE


CALL FUNCTION ‘DEQUEUE_E_TABLE’
     EXPORTING
           MODE_RSTABLE
= GC_E
           TABNAME     
= GC_TABNAME
           VARKEY      
= L_VARKEY
           X_TABNAME   
= ‘ ‘
           X_VARKEY    
= ‘ ‘
           _SCOPE      
= GC_3
           _SYNCHRON   
= ‘ ‘
           _COLLECT    
= ‘ ‘.
IF ( SYSUBRC = 0 ) AND ( GV_FLAG = 2 ).
      GV_FLAG
= 3.
ENDIF.

ENDIF.
ELSE.
MESSAGE ‘CUSTOM MEASURED ARE UP TO DATE’(008) TYPE GC_I.
LEAVE LIST-PROCESSING.
ENDIF.

IF GV_FLAG = 3.
     MESSAGE ‘SUCCESSFULLY MAINTAINED CUSTOM MEASURES IN CURRENT SYSTEMS’(009) TYPE GC_S.
ELSE.
     MESSAGE ‘UNABLE TO EXUCUTE, CHECK LOCK ON DATABASE TABLE UJA_FORMULA_APP’(010) TYPE GC_E.
ENDIF.

Appendix C

Custom Measures Transport

Refer BPC 10.0 Step-by-Step guide for Custom Measures Formulae Transport document

http://scn.sap.com/docs/DOC-48843


References

https://help.sap.com

To report this post you need to login first.

2 Comments

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

    1. SAP BPC

      Hello Ravi,

      Thanks for such important doc.

      But I am facing very different issue, when I am changing the logic for measure as per my requirement, I am not getting desired results.

      Even, I don’t know whether the changes are really getting reflecting in the model, as after many changes I am getting the same results as before.

      One thing I observed in your doc, when we run the UJA_MAINTAIN_MEASURE_FORMULA , it asks for Environment , Model and User  Id,   but in my case it is asking only Environment and Model.

      Can you please tell me,  is it patch issue, and if so, can you please tell me what patch level I should apply.

      My current BPC :

      BPC 10 NW with Release 801 and SP-Level  0003

      Thanks in advance.

      Reagards,

      SAP BPC

      (0) 

Leave a Reply