Custom Measure Formulas

You use the UJA_MAINTAIN_MEASURE_FORMULA program to create and maintain custom measure formulas.

Restrictions on Maintaining Measures

  • Measures delivered with Business Planning and Consolidation cannot be deleted.

  • The measures PERIODIC on a Periodic InfoProvider and YTD on a YTD InfoProvider that are delivered with Business Planning and Consolidation cannot be changed. Other delivered measures can be changed at your own risk.

    Note
    Changes made to measures delivered with Business Planning and Consolidation are overwritten when you make any changes to that model from the Administration screen. We recommend that you create custom measures instead of changing the delivered measures.

  • Formula names cannot be duplicated.

  • Formula names must be entered in uppercase letters.

  • Parent nodes of a hierarchy cannot be used in measure formulas.

Hints for Maintaining the Formulas

  • Use the same formula name as you defined in the Formula Name field when you give the measure name in [MEASURES].[<measure name>].

  • If your formula has to refer to another measure, enter the other formula statement and new formula together in the formula statement section. For example, refer to the formula statement of Periodic and QTD measure on model with a YTD storage type.

  • If you want to use a property of dimension within Measure Formula, you must include the SAP Business Warehouse Technical name. You can find this name using transaction MDXTEST, for example. In this transaction, choose InfoProvider as the catalog and then choose your model (InfoProvider). The system displays the list of dimensions. Expand the dimension and then its properties to view the property list.

    [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/GJPDBWP ") is a syntax for using the TIMEID property of the TIME dimension.

Creating a Custom Measure Formula

  1. Go to Transaction SE38 in the ABAP layer and enter UJA_MAINTAIN_MEASURE_FORMULA as the program name.

  2. Click the Debugging button.

  3. In the Maintain Measure Formula screen, enter the model set ID, model ID, and user ID. You must have write access to the core model to save a formula.

  4. Click the Enter button on the System Function Bar.

  5. In the Maintain Measure Formula screen, do any of the following:

    • To create a measure:

      1. Click the new item button.

      2. Enter a measure formula name, description, and formula.

    • To display a measure formula, double-click it.

    • To display an existing measure, place the cursor on the measure you want to display and click the Display button.

    • To change an existing measure, place the cursor on the measure you want to change and click the Change button.

    • To delete a measure, place the cursor on the measure you want to delete and click the Delete button.

Sample - YTD on YTD Model

MEMBER [MEASURES].[YTD] AS 'IIF(([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])))' SOLVE_ORDER=3

Sample - PERIODIC on YTD Model

MEMBER [MEASURES].[YTD] AS 'IIF(([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])))' SOLVE_ORDER=3## AS 'IIF(([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

Sample - QTD on YTD Model

MEMBER [MEASURES].[YTD] AS 'IIF(([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])))' SOLVE_ORDER=3

AS 'IIF(([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

MEMBER [MEASURES].[QTD] AS 'IIF(([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP"),IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="QUARTER",[MEASURES].[PERIODIC],IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="MONTH" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="WEEK" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="DAY",SUM(PERIODSTODATE([%TIME%].CURRENTMEMBER.PARENT.LEVEL, [%TIME%].CURRENTMEMBER),[MEASURES].[PERIODIC]),NULL)),[MEASURES].[YTD])' SOLVE_ORDER = 3