How To… Create,Transport and Reuse Custom Measures...

Former Member · ‎2014 Mar 04

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:

We created a User defined Dimension FSGAAP similar to ACCOUNT Dimension.

The Property GAT is similar to ACC Type property of ACCOUNT Dimension.

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.

Step by Step Picture Guide:
1. Go to SE38 Transaction and execute the Program UJA_MAINTAIN_MEASURE_FORMULA
2. Provide the Environment, Model & User details for which you wish to generate custom measure
3. Write the Formula for the desired custom property – Average for Quarter (Refer Appendix A)
4. Write the Formula for the desired custom property – Average for Year (Refer Appendix A)

In order to view the Custom measures generated, go to SE16 transaction and display the contents of UJA_FORMULA_APP table.
1. Provide the Appset ID, Application ID details and execute to view the table contents
2. The newly generated formulae are successfully updated in UJA_FORMULA_APP table.

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

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
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

How to Execute this Transaction?

Go to SAP BW system ( Quality / Production) for BPC 10.0, run the transaction ZTBPC_FORMULAPP

Enter the Environment to which the custom measures need to be checked and updated.

After selecting the required Environment, Press F8 to execute

After execution you will see a successful message.

Go to SE16 transaction, Check the Contents of Table for Environment – ENVIRONMENTSHELL (desired environment)

Give the desired Environment

Enter the formula name and execute

Once executed, check the FORMULA_STAT field for changes

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 RSTABLE-VARKEY.

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 RSTABLE-TABNAME 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 SCREEN-NAME = 'P_APPSET-LOW'(005).
          SCREEN-REQUIRED = GC_2.
          MODIFY SCREEN.
     ENDIF.                             " IF SCREEN-NAME = 'P_APPSET-LOW'.
ENDLOOP.
*----------------------------------------------------------------------*
*       SELECTION SCREEN VALIDATION
*----------------------------------------------------------------------*
AT SELECTION-SCREEN ON P_APPSET.

IF SY-UCOMM 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 SY-SUBRC <> 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 SY-SUBRC EQ 0.
          READ TABLE GT_RET INDEX 1.
P_APPSET = GT_RET-FIELDVAL.
     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 SY-SUBRC = 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 SY-SUBRC = 0.
GV_OBJNM = GS_OBJNM-IOBJNM.
                    ENDIF.
          ELSEIF <GV_FORMULA_NAME> CS GV_AVGYEAR.
               READ TABLE GT_OBJNM INTO GS_OBJNM WITH KEY TXTLG = GC_AVGYEAR.
                    IF SY-SUBRC = 0.
GV_OBJNM = GS_OBJNM-IOBJNM.
                    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 = SY-MANDT.

* 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 SY-SUBRC = 0.
* MODIFY USING THE INTERNAL TABLE GT_FORMULA
MODIFY UJA_FORMULA_APP FROM TABLE GT_APPENDTAB.
IF SY-SUBRC = 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 ( SY-SUBRC = 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