# Introduction

If there is a business requirement to store data in average figures, it does not make sense to sum averages on parent nodes.

In this document it will be shown how to implement a BADI in UJQ_SHARED_QUERY to average data on parent nodes for reporting.

# Weighted Average vs Arithmetic mean

The provided BADI in this document implements weighted average rather then arithmetic mean. It will average all base members for a specific parent in the report.

An example of weighted average and arithmetic mean is in the table below. See that at World member weighted average equals to 67, when it is 60 as arithmetic mean. Arithmetic mean = (95+25)/2. Weighted average = (150+50+85+30+20)/5.

 Weighted Average Arithmetic mean 2013 Mar 2013 Mar World 67 60 Europe 95 95 Russia 150 150 Germany 50 50 UK 85 85 Asia 25 25 Japan 30 30 China 20 20 India

# Create a BADI implementation.

I will skip this section. There is a number of documents on SCN that have clear step by step procedure how to do this.

Please follow the the steps in the document http://scn.sap.com/docs/DOC-4217. Section 4.1 till step 27. (In my example ABAP class is named ZCL_UJ_QUERY_AVG).

Copy and paste the code from Appendix of this document and activate it.

# Define hierarchy and properties.

In this example we will use ACCOUNT dimension. We will create an account PARENT_AVG with 3 children. For them we will post and report average figures.

To enable reporting on parent nodes with average figures create ISAVG dimension property. It will take Y if a member should be averaged. Length of the property is 1. “ISAVG” is hardcoded in BADI.

Create members and define heirarchy for them. Set ISAVG property to Y for the required members. In this example there is a parent PARENT_AVG with 3 children C001, C002, C003. Process the dimension.

Please take the following into consideration.

• Mixed hierarchy is not supported by the BADI. If the member which is marked as to be averaged is a child of a member which is not marked, reporting on the parent will not take into account that any children have ISAVG set to Y. It will show a sum of base members.
• Multi-selection of mixed members is not supported. Multi-selection on page axis of average members and other members will not be interpreted correctly.

# Examples of the reports.

Below are some examples.

Simple report

Report with a hierarchy on other dimension.

Report with the parent member in Page axis

Report with the parent member in context

# Appendix.

Since there is no yet ABAP syntax highlighting, I will copy/paste the code in a table.

ZCL_UJ_QUERY_AVG

METHOD IF_UJQ_SQE_POST_PROCESS~POST_PROCESS.

TYPEPOOLS: ABAP.

TYPESBEGIN OF TY_MEMBER_BASE_MEMBER,
DIMENSION
TYPE UJ_DIM_NAME,
MEMBER
TYPE UJ_DIM_MEMBER,
IS_AVG
TYPE BOOLEAN,
IS_PARENT
TYPE BOOLEAN,
BASE_MEMBERS
TYPE UJQ_T_DIM_MEMBER,
END OF TY_MEMBER_BASE_MEMBER.

DATA: LS_BASE_MEMBERS         TYPE          TY_MEMBER_BASE_MEMBER.
DATA: LS_MEMBERS              TYPE          TY_MEMBER_BASE_MEMBER.
DATA: LT_MEMBERS              TYPE TABLE OF TY_MEMBER_BASE_MEMBER.
DATA: IS_MEMBERS              TYPE          UJQ_S_DIM_MEMBER.
DATA: LD_AVG_FLG              TYPE          BOOLEAN.
DATA: LO_MODEL                TYPE REF TO   IF_UJ_MODEL.
DATA: LO_DATAREF              TYPE REF TO   DATA.
DATA: LO_QUERY                TYPE REF TO IF_UJO_QUERY.
DATA: LT_DIM_NAME             TYPE UJA_T_DIM_LIST.
DATA: LS_DIM_NAME             TYPE UJ_DIM_NAME.
DATA: LT_RANGE                TYPE UJ0_T_SEL.
DATA: LS_RANGE                TYPE UJ0_S_SEL.
DATA: LT_SL                   TYPE UJQ_T_QUERY_DIM.
DATA: LS_SL                   TYPE UJQ_S_QUERY_DIM.
DATA: LV_MEMBER               TYPE UJQ_S_DIM_MEMBER.
DATA: LT_AXIS                 TYPE UJO_T_QUERY_DIM_EXT.
DATA: LT_MEMBER               TYPE UJO_T_MEMBER_EXT.
DATA: LS_MEMBER               TYPE UJO_S_MEMBER_EXT.
DATA: LT_MESSAGE              TYPE UJ0_T_MESSAGE.
DATA: LO_DIM                  TYPE REF TO CL_UJA_DIM.
DATA: LR_DIM_DATA             TYPE REF TO IF_UJA_DIM_DATA.
DATA: LR_DATA                 TYPE REF TO DATA.
DATA: LS_EMP                  TYPE REF TO DATA.
DATA: LT_ATTR_NAME            TYPE UJA_T_ATTR_NAME.
DATA: LT_SEL                  TYPE UJ0_T_SEL.
DATA: LS_SEL                  TYPE UJ0_S_SEL.
DATA: LT_BASE_EN              TYPE UJA_T_DIM_MEMBER.
DATA: LS_BASE_EN              TYPE UJ_DIM_MEMBER.
DATA: L_AVG_REPORT_FLG        TYPE BOOLEAN.
DATA: L_AVG_PAGE_CONTEXT_FLG  TYPE BOOLEAN.
DATA: LD_ROWCOUNT             TYPE I. “CHECK NUMBER OF ROWS IN A TABLE WITH A MEMBER’S CHILDREN

FIELDSYMBOLS: <LT_EMP>       TYPE ANY TABLE,
<LS_EMP>
TYPE ANY,
<LT_TX_DATA>
TYPE STANDARD TABLE.

CONSTANTS:      LC_ID_FIELD(2TYPE C       VALUE ‘ID’,
LD_SIGN
(10)     TYPE C       VALUE ‘ NE ‘,
LD_COND
(10)     TYPE C       VALUE ‘ AND ‘.

*——————————————————–
* CHECK IF ANY AXIS EXPANSION HAS A MEMBER IS WITH ISAVG = Y.
* PREPARE LT_DIM_NAME AND LT_RANGE PARAMETER
*——————————————————–

*  INITIALIZE FLAGS TO FALSE
L_AVG_REPORT_FLG
= ABAP_FALSE.
L_AVG_PAGE_CONTEXT_FLG
= ABAP_FALSE.
CLEAR LT_DIM_NAME.
LT_SL
= IT_SLICER.
LOOP AT LT_SL INTO LS_SL. “CHECK PAGE AXIS AND CONTEXT FIRST
APPEND LS_SLDIMENSION TO LT_DIM_NAME.
IF LS_SLDIMENSION EQ ‘MEASURES’.
CONTINUE. “DO NOTHING IF IT IS MEASURES DIMENSION
ENDIF.
*    CHECK IF MEMBERS PROVIDED IN PAGE AXIS
*    AND CONTEXT ARE PARENTS OR BASE MEMBERS
*    AND IF ANY OF THEM HAS ISAVG SET TO Y
REFRESH: LT_ATTR_NAME, LT_SEL.
CLEAR: LS_SEL.
TRY .
CREATE OBJECT LO_DIM
EXPORTING
I_APPSET_ID
= I_APPSET_ID
*           I_DIMENSION = ‘ACCOUNT’.
I_DIMENSION
= LS_SLDIMENSION.
ENDTRY.
LR_DIM_DATA
= LO_DIM.
” APPEND THE LIST OF ATTRIBUTE(S) FOR WHICH THE MASTER DATA IS GENERATED
APPEND: ‘ID’ TO LT_ATTR_NAME.
LS_SEL
DIMENSION = LS_SLDIMENSION.
LS_SEL
ATTRIBUTE = ‘ISAVG’. “ISAVG NAME IS HARDCODED
LS_SEL
SIGN = ‘I’.
LS_SEL
OPTION = ‘EQ’.
LS_SEL
LOW = ‘Y’.
APPEND LS_SEL TO LT_SEL.
” GET DIMENSION MEMBERS
TRY.
CLEAR LR_DATA.
EXPORTING
IT_ATTR_LIST
= LT_ATTR_NAME    “ATTRIBUTE LIST
IT_SEL
= LT_SEL          “CONDITION DATA
IMPORTING
ER_DATA
= LR_DATA.        “REFERENCE OF MASTER DATA TABLE
ENDTRY.

TRY.
CREATE OBJECT LO_DIM
EXPORTING
I_APPSET_ID
= I_APPSET_ID
I_DIMENSION
= LS_SLDIMENSION.
ENDTRY.

LR_DIM_DATA = LO_DIM.

LOOP AT LS_SLMEMBER_LIST INTO LV_MEMBER. “LOOP MEMBERS IN PAGE AXIS AND CONTEXT
CLEAR LT_BASE_EN.

LD_AVG_FLG = ABAP_FALSE.
IF LR_DATA IS NOT INITIAL.
ASSIGN LR_DATA->* TO <LT_EMP>.
READ TABLE <LT_EMP> WITH KEY (LC_ID_FIELD) = LV_MEMBERMEMBER_NAME TRANSPORTING NO FIELDS.
IF SYSUBRC EQ 0. “SELECTION RETURNED A RECORD.
L_AVG_REPORT_FLG
= ABAP_TRUE. “THERE IS AVG MEMBER IN THE REPORT
L_AVG_PAGE_CONTEXT_FLG
= ABAP_TRUE. “IT IS IN PAGE AXIS
LD_AVG_FLG
= ABAP_TRUE. “IT IS AVG
ENDIF.
ENDIF.

*  CHECK IF THE MEMBER HAS CHILDREN
*  IF IT HAS, WE NEED TO STORE BASE MEMBER IN LT_RANGE FOR RSDRI QUERY

CALL METHOD LR_DIM_DATA->GET_CHILDREN_MBR
EXPORTING
I_PARENT_MBR
= LV_MEMBERMEMBER_NAME
IF_ONLY_BASE_MBR
= ‘X’
IMPORTING
ET_MEMBER
= LT_BASE_EN.

DESCRIBE TABLE LT_BASE_EN LINES LD_ROWCOUNT. “CHECK IF HAS CHILDREN

IF LD_ROWCOUNT NE 0. “THE PROCESSED MEMBER IS PARENT
LOOP AT LT_BASE_EN INTO LS_BASE_EN.
“CREATE AND FILL LT_RANGE FOR FURTHER USAGE IN RSDRI QUERY
LS_RANGE
DIMENSION = LS_SLDIMENSION.
LS_RANGE
ATTRIBUTE = ‘ID’.
LS_RANGE
SIGN = ‘I’.
LS_RANGE
OPTION = ‘EQ’.
LS_RANGE
LOW = LS_BASE_EN.
APPEND LS_RANGE TO LT_RANGE.
ENDLOOP.
ELSE.             “THE PROCESSED MEMBER IS BASEMEMBER
“CREATE AND FILL LT_RANGE FOR FURTHER USAGE IN RSDRI QUERY
LS_RANGE
DIMENSION = LS_SLDIMENSION.
LS_RANGE
ATTRIBUTE = ‘ID’.
LS_RANGE
SIGN = ‘I’.
LS_RANGE
OPTION = ‘EQ’.
LS_RANGE
LOW = LV_MEMBERMEMBER_NAME.
APPEND LS_RANGE TO LT_RANGE.
ENDIF.
ENDLOOP.
ENDLOOP.

*   NOW GO THROUGH ROW AND COLUMN AXIS AND SEE IF THERE IS A MEMBER WITH ISAVG SET TO Y
LT_AXIS
= IT_AXIS.
LOOP AT LT_AXIS INTO LT_MEMBER.
LOOP AT LT_MEMBER INTO LS_MEMBER.
IF SYTABIX EQ 1.

READ TABLE LT_DIM_NAME WITH KEY TABLE_LINE = LS_MEMBERDIMENSION TRANSPORTING NO FIELDS.

IF SYSUBRC NE 0.
APPEND LS_MEMBERDIMENSION TO LT_DIM_NAME.
ENDIF.

REFRESH: LT_ATTR_NAME, LT_SEL.
CLEAR: LS_SEL.
TRY .
CREATE OBJECT LO_DIM
EXPORTING
I_APPSET_ID
= I_APPSET_ID
I_DIMENSION
= LS_MEMBERDIMENSION.
ENDTRY.

LR_DIM_DATA = LO_DIM.
” APPEND THE LIST OF ATTRIBUTE(S) FOR WHICH THE MASTER DATA IS GENERATED
APPEND: ‘ID’ TO LT_ATTR_NAME.
LS_SEL
DIMENSION = LS_MEMBERDIMENSION.
LS_SEL
ATTRIBUTE = ‘ISAVG’.
LS_SEL
SIGN = ‘I’.
LS_SEL
OPTION = ‘EQ’.
LS_SEL
LOW = ‘Y’.
APPEND LS_SEL TO LT_SEL.
” GET DIMENSION MEMBERS
TRY.
CLEAR LR_DATA.
EXPORTING
IT_ATTR_LIST
= LT_ATTR_NAME    “ATTRIBUTE LIST
IT_SEL
= LT_SEL          “CONDITION DATA
IMPORTING
ER_DATA
= LR_DATA.        “REFERENCE OF MASTER DATA TABLE
ENDTRY.
ENDIF.
LD_AVG_FLG
= ABAP_FALSE.
IF LR_DATA IS NOT INITIAL.
ASSIGN LR_DATA->* TO <LT_EMP>.
READ TABLE <LT_EMP> WITH KEY (LC_ID_FIELD) = LS_MEMBERMEMBER TRANSPORTING NO FIELDS.
IF SYSUBRC EQ 0. “SELECTION RETURNED A RECORD.
L_AVG_REPORT_FLG
= ABAP_TRUE.
LD_AVG_FLG
= ABAP_TRUE.
ENDIF.
ENDIF.

CLEAR LS_MEMBERS.
CLEAR LS_BASE_MEMBERS.
IF LS_MEMBERIS_BASE EQ ABAP_FALSE.
TRY.
CREATE OBJECT LO_DIM
EXPORTING
I_APPSET_ID
= I_APPSET_ID
I_DIMENSION
= LS_MEMBERDIMENSION.
ENDTRY.

LR_DIM_DATA = LO_DIM.

CALL METHOD LR_DIM_DATA->GET_CHILDREN_MBR
EXPORTING
I_PARENT_MBR
= LS_MEMBERMEMBER
IF_ONLY_BASE_MBR
= ‘X’
IMPORTING
ET_MEMBER
= LT_BASE_EN.

LS_MEMBERSDIMENSION = LS_MEMBERDIMENSION.
LS_MEMBERS
MEMBER = LS_MEMBERMEMBER.
LS_MEMBERS
IS_AVG = LD_AVG_FLG.
LOOP AT LT_BASE_EN INTO LS_BASE_EN.
IS_MEMBERS
DIMENSION = LS_MEMBERDIMENSION.
IS_MEMBERS
MEMBER_NAME = LS_BASE_EN.
APPEND IS_MEMBERS TO LS_MEMBERSBASE_MEMBERS.
ENDLOOP.
LS_MEMBERS
IS_PARENT = ABAP_TRUE.
APPEND LS_MEMBERS TO LT_MEMBERS.
ELSEIF LD_AVG_FLG EQ ABAP_TRUE.
LS_MEMBERS
DIMENSION = LS_MEMBERDIMENSION.
LS_MEMBERS
MEMBER = LS_MEMBERMEMBER.
LS_MEMBERS
IS_AVG = LD_AVG_FLG.
APPEND LS_MEMBERS TO LT_MEMBERS.
ENDIF.
ENDLOOP.
ENDLOOP.
*——————————————————–
* END CHECK IF ANY AXIS EXPANSION HAS A MEMBER IS WITH ISAVG = Y.
* END PREPARE LT_DIM_NAME AND LT_RANGE PARAMETER
*——————————————————–
IF L_AVG_REPORT_FLG NE ABAP_TRUE.
RETURN. “STOP BADI IF THERE IS NO AVG MEMBERS.
ENDIF.

TRY.
LO_MODEL
= CL_UJ_MODEL=>GET_MODEL( I_APPSET_ID ).
LO_MODEL
->CREATE_TX_DATA_REF(
EXPORTING
I_APPL_NAME
= I_APPL_ID
I_TYPE
= ‘T’
IT_DIM_NAME
= LT_DIM_NAME
IF_TECH_NAME
= SPACE
IMPORTING
ER_DATA
= LO_DATAREF ).
CATCH CX_UJ_STATIC_CHECK.
ENDTRY.
* ASSIGNING THE STRUCTURE TO TABLE
ASSIGN LO_DATAREF->* TO <LT_TX_DATA>.

DATA: LO_DATA TYPE REF TO DATA.
DATA: LS_LINE TYPE REF TO DATA.

FIELDSYMBOLS: <LT_LO_DATA> TYPE STANDARD TABLE.
FIELDSYMBOLS: <FS_WA>      TYPE ANY.

CREATE DATA LS_LINE LIKE LINE OF <LT_TX_DATA>.
ASSIGN LS_LINE->* TO <FS_WA>.
CREATE DATA LO_DATA LIKE TABLE OF <FS_WA>.
ASSIGN LO_DATA->* TO <LT_LO_DATA>.

* RUN RDRDI QUERY TO FETCH THE DATA ON BASE MEMBERS OF THE REPORT
TRY.
LO_QUERY
I_APPSET_ID
= I_APPSET_ID
I_APPL_ID
= I_APPL_ID
).
LO_QUERY
->RUN_RSDRI_QUERY(
EXPORTING
IT_DIM_NAME
=   LT_DIM_NAME ” BPC: DIMENSION LIST
IT_RANGE
=   LT_RANGE      ” BPC: SELECTION CONDITION
IF_CHECK_SECURITY
=   ABAP_FALSE  ” BPC: GENERIC INDICATOR
=   ABAP_FALSE  ” BPC: DO NOT CALL BADI
IMPORTING
ET_DATA
= <LT_TX_DATA>
ET_MESSAGE
= LT_MESSAGE    ” BPC: MESSAGES
).
ENDTRY.
* END RUN RDRDI QUERY TO FETCH THE DATA ON BASE MEMBERS OF THE REPORT

DATA: GO_TAB_DESCR            TYPE REF TO CL_ABAP_TABLEDESCR,
GO_STRUC_DESCR
TYPE REF TO CL_ABAP_STRUCTDESCR,
WA_COMP
TYPE ABAP_COMPDESCR.

FIELDSYMBOLS: <WA>       TYPE ANY“FS TO STORE WORK AREA VALUE
FIELDSYMBOLS: <VAL>      TYPE ANY“FS TO STORE FIELD VALUE
FIELDSYMBOLS: <VAL1>     TYPE ANY“FS TO STORE FIELD VALUE

* DESCRIBE STRUCTURE OF CT_ARRAY TO LOOP THROUGH IT LATER
GO_TAB_DESCR ?= CL_ABAP_TABLEDESCR
=>DESCRIBE_BY_DATA( CT_ARRAY ).
GO_STRUC_DESCR ?= GO_TAB_DESCR
->GET_TABLE_LINE_TYPE( ).

DATA: LS_SELECTION          TYPE           TY_MEMBER_BASE_MEMBER.
DATA: LT_SELECTION          TYPE TABLE OF  TY_MEMBER_BASE_MEMBER.
DATA: L_AVG_MBR_ROW         TYPE BOOLEAN.
DATA: LD_CONDITION          TYPE STRING.
DATA: LT_CONDITION          LIKE TABLE OF LD_CONDITION.
DATA: LT_RANGE_COPY         TYPE UJ0_T_SEL.
DATA: LT_COMPONENTS         TYPE ABAP_COMPDESCR_TAB.

* MAIN LOOP THROUGH CT_ARRAY
LOOP AT CT_ARRAY ASSIGNING <WA>.

L_AVG_MBR_ROW = ABAP_FALSE.
CLEAR LT_SELECTION.
CLEAR LT_CONDITION.
CLEAR <LT_LO_DATA>.
APPEND LINES OF <LT_TX_DATA> TO <LT_LO_DATA>.
*   IN THIS LOOP TABLE LT_SELECTION WILL BE PREPARED
LOOP AT GO_STRUC_DESCR->COMPONENTS INTO WA_COMP.
CLEAR LS_SELECTION.
CLEAR LD_CONDITION.
ASSIGN COMPONENT WA_COMPNAME OF STRUCTURE <WA> TO <VAL>.

IF WA_COMPNAME EQ ‘SIGNEDDATA’.
CONTINUE.
ENDIF.

LS_SELECTIONMEMBER = <VAL>.
LS_SELECTION
DIMENSION = WA_COMPNAME.
CLEAR LS_MEMBERS.
READ TABLE LT_MEMBERS INTO LS_MEMBERS WITH KEY DIMENSION = WA_COMPNAME MEMBER = <VAL>.
IF LS_MEMBERS IS NOT INITIAL.
IF LS_MEMBERSIS_AVG = ABAP_TRUE.
L_AVG_MBR_ROW
= ABAP_TRUE.
ENDIF.
LS_SELECTION
= LS_MEMBERS.
APPEND LS_SELECTION TO LT_SELECTION.
ELSE.
LS_SELECTION
MEMBER = <VAL>.
LS_SELECTION
DIMENSION = WA_COMPNAME.
APPEND LS_SELECTION TO LT_SELECTION.
ENDIF.

ENDLOOP.
*   IF THERE IS A MEMBER IN CURRENT ROW OF CT_ARRAY WITH ISAVG Y
*   OR THE MEMBER IS IN PAGE AXIS OR CONTEXT THE CALCULATION AVERAGE STARTS HERE
IF L_AVG_MBR_ROW EQ ABAP_TRUE OR L_AVG_PAGE_CONTEXT_FLG EQ ABAP_TRUE.
LT_RANGE_COPY
= LT_RANGE.

LOOP AT LT_SELECTION INTO LS_SELECTION.

IF LS_SELECTIONIS_PARENT EQ ABAP_TRUE.
CLEAR LD_CONDITION.
DATA: LD_LINES TYPE I.
DESCRIBE TABLE LS_SELECTIONBASE_MEMBERS LINES LD_LINES.
LOOP AT LS_SELECTIONBASE_MEMBERS INTO IS_MEMBERS.
IF SYTABIX = 1.
CONCATENATE IS_MEMBERSDIMENSION LD_SIGN ”” INTO LD_CONDITION SEPARATED BY SPACE.
CONCATENATE LD_CONDITION IS_MEMBERSMEMBER_NAME ”” INTO LD_CONDITION.
ELSE.
CONCATENATE LD_CONDITION LD_COND IS_MEMBERSDIMENSION LD_SIGN ”” INTO LD_CONDITION SEPARATED BY SPACE.
CONCATENATE LD_CONDITION IS_MEMBERSMEMBER_NAME ”” INTO LD_CONDITION.
ENDIF.
ENDLOOP.
ELSE.
CONCATENATE LS_SELECTIONDIMENSION LD_SIGN ”” INTO LD_CONDITION SEPARATED BY SPACE.
CONCATENATE LD_CONDITION LS_SELECTIONMEMBER ”” INTO LD_CONDITION.
ENDIF.

DELETE <LT_LO_DATA> WHERE (LD_CONDITION).

ENDLOOP.

DESCRIBE TABLE <LT_LO_DATA> LINES LD_LINES.
IF LD_LINES NE 0.
ASSIGN COMPONENT ‘SIGNEDDATA’ OF STRUCTURE <WA> TO <VAL1>.
<VAL1> <VAL1> / LD_LINES.
ENDIF.

ENDIF.
ENDLOOP.

ENDMETHOD.

### Assigned Tags

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

Hi Kirill,

I am trying to find a business case to use the proposed approach, but... In case of some amount the sum is also required (like sales per region...). Then we use normal sum on nodes and can have some custom measures or dimension member formulas to have average on nodes.

In case of the values that can't be summed like discount percent, price etc. - we need a weighted average based on reverse calculation like (for price):

Entered:

Price

Quantity

Calculated and stored in the script or BADI:

GrossSales=Price*Quantity

Shown in the reports:

PriceCalc with a dimension formula: =GrossSales/Quantity