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
Usefull links
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.
TYPE-POOLS: ABAP.
TYPES: BEGIN 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
FIELD-SYMBOLS: <LT_EMP> TYPE ANY TABLE,
<LS_EMP> TYPE ANY,
<LT_TX_DATA> TYPE STANDARD TABLE.
CONSTANTS: LC_ID_FIELD(2) TYPE 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_SL-DIMENSION TO LT_DIM_NAME.
IF LS_SL-DIMENSION 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_SL-DIMENSION.
CATCH CX_UJA_ADMIN_ERROR .
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_SL-DIMENSION.
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.
CALL METHOD LR_DIM_DATA->READ_MBR_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
CATCH CX_UJA_ADMIN_ERROR .
ENDTRY.
TRY.
CREATE OBJECT LO_DIM
EXPORTING
I_APPSET_ID = I_APPSET_ID
I_DIMENSION = LS_SL-DIMENSION.
CATCH CX_UJA_ADMIN_ERROR.
ENDTRY.
LR_DIM_DATA = LO_DIM.
LOOP AT LS_SL-MEMBER_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_MEMBER-MEMBER_NAME TRANSPORTING NO FIELDS.
IF SY-SUBRC 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_MEMBER-MEMBER_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_SL-DIMENSION.
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_SL-DIMENSION.
LS_RANGE-ATTRIBUTE = 'ID'.
LS_RANGE-SIGN = 'I'.
LS_RANGE-OPTION = 'EQ'.
LS_RANGE-LOW = LV_MEMBER-MEMBER_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 SY-TABIX EQ 1.
READ TABLE LT_DIM_NAME WITH KEY TABLE_LINE = LS_MEMBER-DIMENSION TRANSPORTING NO FIELDS.
IF SY-SUBRC NE 0.
APPEND LS_MEMBER-DIMENSION 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_MEMBER-DIMENSION.
CATCH CX_UJA_ADMIN_ERROR .
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_MEMBER-DIMENSION.
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.
CALL METHOD LR_DIM_DATA->READ_MBR_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
CATCH CX_UJA_ADMIN_ERROR .
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_MEMBER-MEMBER TRANSPORTING NO FIELDS.
IF SY-SUBRC 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_MEMBER-IS_BASE EQ ABAP_FALSE.
TRY.
CREATE OBJECT LO_DIM
EXPORTING
I_APPSET_ID = I_APPSET_ID
I_DIMENSION = LS_MEMBER-DIMENSION.
CATCH CX_UJA_ADMIN_ERROR.
ENDTRY.
LR_DIM_DATA = LO_DIM.
CALL METHOD LR_DIM_DATA->GET_CHILDREN_MBR
EXPORTING
I_PARENT_MBR = LS_MEMBER-MEMBER
IF_ONLY_BASE_MBR = 'X'
IMPORTING
ET_MEMBER = LT_BASE_EN.
LS_MEMBERS-DIMENSION = LS_MEMBER-DIMENSION.
LS_MEMBERS-MEMBER = LS_MEMBER-MEMBER.
LS_MEMBERS-IS_AVG = LD_AVG_FLG.
LOOP AT LT_BASE_EN INTO LS_BASE_EN.
IS_MEMBERS-DIMENSION = LS_MEMBER-DIMENSION.
IS_MEMBERS-MEMBER_NAME = LS_BASE_EN.
APPEND IS_MEMBERS TO LS_MEMBERS-BASE_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_MEMBER-DIMENSION.
LS_MEMBERS-MEMBER = LS_MEMBER-MEMBER.
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.
FIELD-SYMBOLS: <LT_LO_DATA> TYPE STANDARD TABLE.
FIELD-SYMBOLS: <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 = CL_UJO_QUERY_FACTORY=>GET_QUERY_ADAPTER(
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
I_CALL_BADI = ABAP_FALSE " BPC: DO NOT CALL BADI
IMPORTING
ET_DATA = <LT_TX_DATA>
ET_MESSAGE = LT_MESSAGE " BPC: MESSAGES
).
CATCH CX_UJO_READ. " EXCEPTION OF COMMON READ
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.
FIELD-SYMBOLS: <WA> TYPE ANY. "FS TO STORE WORK AREA VALUE
FIELD-SYMBOLS: <VAL> TYPE ANY. "FS TO STORE FIELD VALUE
FIELD-SYMBOLS: <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_COMP-NAME OF STRUCTURE <WA> TO <VAL>.
IF WA_COMP-NAME EQ 'SIGNEDDATA'.
CONTINUE.
ENDIF.
LS_SELECTION-MEMBER = <VAL>.
LS_SELECTION-DIMENSION = WA_COMP-NAME.
CLEAR LS_MEMBERS.
READ TABLE LT_MEMBERS INTO LS_MEMBERS WITH KEY DIMENSION = WA_COMP-NAME MEMBER = <VAL>.
IF LS_MEMBERS IS NOT INITIAL.
IF LS_MEMBERS-IS_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_COMP-NAME.
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_SELECTION-IS_PARENT EQ ABAP_TRUE.
CLEAR LD_CONDITION.
DATA: LD_LINES TYPE I.
DESCRIBE TABLE LS_SELECTION-BASE_MEMBERS LINES LD_LINES.
LOOP AT LS_SELECTION-BASE_MEMBERS INTO IS_MEMBERS.
IF SY-TABIX = 1.
CONCATENATE IS_MEMBERS-DIMENSION LD_SIGN '''' INTO LD_CONDITION SEPARATED BY SPACE.
CONCATENATE LD_CONDITION IS_MEMBERS-MEMBER_NAME '''' INTO LD_CONDITION.
ELSE.
CONCATENATE LD_CONDITION LD_COND IS_MEMBERS-DIMENSION LD_SIGN '''' INTO LD_CONDITION SEPARATED BY SPACE.
CONCATENATE LD_CONDITION IS_MEMBERS-MEMBER_NAME '''' INTO LD_CONDITION.
ENDIF.
ENDLOOP.
ELSE.
CONCATENATE LS_SELECTION-DIMENSION LD_SIGN '''' INTO LD_CONDITION SEPARATED BY SPACE.
CONCATENATE LD_CONDITION LS_SELECTION-MEMBER '''' 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. |
