Skip to Content
Personal Insights
Author's profile photo Hari Venkatesh Subramanian

SAP BW/4HANA extraction for SAP S/4HANA On-premise for Group Reporting

Introduction

The transactional data extraction views for standard extractor are available with SAP S/4HANA On-premise 2022 edition. The standard extractors are based on the table ACDOCU and does not contain the group reporting based reporting logic. Please note that the general recommendation is to use the standard extractor and you need to build the reporting logic on the target system. However, in this blog, we will discuss about the options of extracting the data that includes reporting logic from SAP S/4HANA On-premise for group reporting (source system) to SAP BW/4HANA (target system). The existing analytical stack of group reporting brings various logics such as Periodic Modes (PER/YTD), Group Derivation (or Group Structure), Restatement & Multi Group Currency, Hierarchy Elimination (Cons Unit/Segment/Profit Center) whereas the standard extractor does not include these reporting logics.

How can we extract data with reporting logic?

You can use the API: API_CNSLDTNGRPJRNLITEM. The API has the reporting logic and this acts as the full stack (without reporting item). As a pre-requisite, you need to add this service in the transaction /n/iwfnd/maint_service in your target system as shown below:

API%20Service

API Service

You can write an ABAP report by consuming the above API so that it takes the data from source system with reporting logic and put it into target system (say for e.g., in ADSO). Please follow the below steps:

a. You need to set the API URL. The filters can be provided appropriately, and the projection can be part of it. The new API link is provided at the end of this blog.

b. Create the HTTP client for the above URL.

c. Set the header fields and receive the HTTP response.

d. You need to convert the response to an internal table.

e. Pass the table to the function ‘RSDRI_DATA_WRAP‘ and also specify the ADSO in the function ‘RSDSO_WRITE_API_RFC‘. This will help you to insert the records into the ADSO. Make sure the projection fields exist as an info-object in ADSO.

You can refer to the (rough coding) sample report that illustrates the above steps:

*&---------------------------------------------------------------------*
*& Report Z_SAMPLE 
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z_SAMPLE.
DATA: lo_http_client TYPE REF TO if_http_client.
DATA: lt_header_fields TYPE tihttpnvp.
DATA: ls_header_fields TYPE ihttpnvp.
DATA: response TYPE string.

DATA(l_url) = |http://<your server:your port>/sap/opu/odata/sap/API_CNSLDTNGRPJRNLITEM/CnsldtnGrpJrnlItem(P_ConsolidationUnitHierarchy='RGCUH'| &&
              |,P_ConsolidationPrftCtrHier='$',P_ConsolidationSegmentHier='$',P_KeyDate=datetime'2018-12-31T00:00:00')| &&
              |/Results?$select=ConsolidationGroup,ConsolidationUnitForElim,FiscalYearPeriod,FinancialStatementItem,ProfitCenter,SubItem,| &&
              |SubItemCategory,ConsolidationUnit,AmountInLocalCurrency,AmountInGroupCurrency| &&
              |,CnsldtnQuantityInBaseUnit&$filter=ConsolidationVersion eq '<your value>'|.
"you can provide additional filters as per your convenience
CALL METHOD cl_http_client=>create_by_url
  EXPORTING
    url                = l_url
  IMPORTING
    client             = lo_http_client
  EXCEPTIONS
    argument_not_found = 1
    plugin_not_active  = 2
    internal_error     = 3
    OTHERS             = 4.

IF sy-subrc <> 0.
 " error
ENDIF.


lo_http_client->request->set_method('GET').

ls_header_fields-name = 'Content-Type'.
ls_header_fields-value = 'application/json'.
APPEND ls_header_fields TO lt_header_fields.
CLEAR ls_header_fields.

ls_header_fields-name = 'Accept'.
ls_header_fields-value = 'application/json'.
APPEND ls_header_fields TO lt_header_fields.
CLEAR ls_header_fields.

ls_header_fields-name = 'APIKey'.
ls_header_fields-value = '<API_KEY>'.
APPEND ls_header_fields TO lt_header_fields.
CLEAR ls_header_fields.

CALL METHOD lo_http_client->request->set_header_fields
  EXPORTING
    fields = lt_header_fields.

CALL METHOD lo_http_client->send
  EXCEPTIONS
    http_communication_failure = 1
    http_invalid_state         = 2
    http_processing_failed     = 3
    http_invalid_timeout       = 4
    OTHERS                     = 5.

IF sy-subrc = 0.
  CALL METHOD lo_http_client->receive
    EXCEPTIONS
      http_communication_failure = 1
      http_invalid_state         = 2
      http_processing_failed     = 3
      OTHERS                     = 5.
ENDIF.

IF sy-subrc <> 0.
  "error
ENDIF.

response = lo_http_client->response->get_cdata( ).

DATA(lref_result) = /ui2/cl_json=>generate( json = response ).
ASSIGN lref_result->* TO FIELD-SYMBOL(<lref_result>).

ASSIGN COMPONENT 'D' OF STRUCTURE <lref_result> TO FIELD-SYMBOL(<lv_result>).
ASSIGN <lv_result>->* TO FIELD-SYMBOL(<l_result>).

ASSIGN COMPONENT 'RESULTS' OF STRUCTURE <l_result> TO FIELD-SYMBOL(<l_adso_res>).
ASSIGN <l_adso_res>->* TO FIELD-SYMBOL(<ls_adso_res>).


TYPES:
  ty_s_dso_data TYPE /bic/azfullst1, "changes as per your adso
  ty_t_dso_data TYPE STANDARD TABLE OF /bic/azfullst1. "changes as per your adso

DATA:
  gs_dso_data  TYPE /bic/azfullst1, "changes as per your adso
  l_char type string,
  gt_dso_data  TYPE STANDARD TABLE OF /bic/azfullst1, "changes as per your adso,
  gv_numrec    TYPE i,
  gt_log_msgs  TYPE rs_t_msg,
  gs_log_msg   TYPE rs_s_msg,
  gt_log_msgs2 TYPE STANDARD TABLE OF abapsource,
  gv_log_msg2  TYPE abapsource,
  gv_idx       TYPE i,
  lt_data_250  TYPE bapi6116tda.
*

FIELD-SYMBOLS : <l_adso>,
               <l_adsoval>,
                <l_CunitElim>,
                <l_CunitElimVal>,
                <l_FSItem>,
                <l_FSItemVal>,
                <l_Subit>,
                <l_SubitVal>,
                <l_Subty>,
                <l_SubtyVal>,
                <l_cunit>,
                <l_cunitVal>,
                <l_congr>,
                <l_congrVal>,
                <l_PRcntr>,
                <l_PRcntrVal>,
                <l_Lcur>,
                <l_LcurVal>,
                <l_fiscper>,
                <l_fiscperVal>,
                <l_Qty>,
                <l_QtyVal>,
                <l_AmntLC>,
                <l_AmntLCVal>,
                <l_AmntGC>,
                <l_AmntGCVal>.


LOOP AT <ls_adso_res> ASSIGNING <l_adso>.

ASSIGN <l_adso>->* TO <l_adsoval>.

*  ASSIGN COMPONENT 'D' OF STRUCTURE <l_adso> TO FIELD-SYMBOL(<l_result>).

  ASSIGN COMPONENT 'AMOUNTINGROUPCURRENCY' OF STRUCTURE <l_adsoval> TO <l_AmntGCVal>.
  ASSIGN COMPONENT 'AMOUNTINLOCALCURRENCY' OF STRUCTURE <l_adsoval> TO <l_AmntLCVal>.
  ASSIGN COMPONENT 'CNSLDTNQUANTITYINBASEUNIT' OF STRUCTURE <l_adsoval> TO <l_QtyVal>.
  ASSIGN COMPONENT 'CONSOLIDATIONUNITFORELIM' OF STRUCTURE <l_adsoval> TO <l_CunitElimVal>.
  ASSIGN COMPONENT 'FINANCIALSTATEMENTITEM' OF STRUCTURE <l_adsoval> TO <l_FSItemVal>.
  ASSIGN COMPONENT 'FISCALYEARPERIOD' OF STRUCTURE <l_adsoval> TO <l_fiscperVal>.
  ASSIGN COMPONENT 'CONSOLIDATIONUNIT' OF STRUCTURE <l_adsoval> TO <l_cunitVal>.
  ASSIGN COMPONENT 'CONSOLIDATIONGROUP' OF STRUCTURE <l_adsoval> TO <l_congrVal>.
  ASSIGN COMPONENT 'PROFITCENTER' OF STRUCTURE <l_adsoval> TO <l_PRcntrVal>.
  ASSIGN COMPONENT 'SUBITEM' OF STRUCTURE <l_adsoval> TO <l_SubitVal>.
  ASSIGN COMPONENT 'SUBITEMCATEGORY' OF STRUCTURE <l_adsoval> TO <l_SubtyVal>.

 gs_dso_data-/bic/zksl = conv f( <l_AmntGCVal>->* )  .


 IF <l_CunitElimVal>->* is initial.
  gs_dso_data-/bic/zrbunitel = ''.
 ELSE.
  gs_dso_data-/bic/zrbunitel = <l_CunitElimVal>->*.
 ENDIF.

  gs_dso_data-/bic/zfsitem = <l_FSItemVal>->*.

  gs_dso_data-/bic/zsubit = <l_SubitVal>->*.

  gs_dso_data-/bic/zsubty = <l_SubtyVal>->*.

  gs_dso_data-/bic/zrbunit = <l_cunitVal>->*.

  gs_dso_data-/bic/zrcongr = <l_congrVal>->*.

  gs_dso_data-/bic/zprctr = <l_PRcntrVal>->*.

  gs_dso_data-/bic/zhsl = <l_AmntLCVal>->*.

  APPEND gs_dso_data TO gt_dso_data.

ENDLOOP.

CALL FUNCTION 'RSDRI_DATA_WRAP'
  EXPORTING
    i_t_data         = gt_dso_data
    i_unicode_result = rs_c_false
    i_compress       = rs_c_false
    i_result250      = rs_c_true
  IMPORTING
    e_t_outdata250   = lt_data_250.


CALL FUNCTION 'RSDSO_WRITE_API_RFC'
  EXPORTING
    i_adsonm            = '<your adso>'
    i_allow_new_sids    = rs_c_true
*   I_RFCDATA_UC        =
    it_rfcdata_250      = lt_data_250
*   I_DEBUG             = RS_C_FALSE
  IMPORTING
    e_lines_inserted    = gv_numrec
    et_msg              = gt_log_msgs
*   E_UPD_REQ_TSN       = DATA(l_req_tsn)
  EXCEPTIONS
    write_failed        = 1
    activation_failed   = 2
    datastore_not_found = 3
    OTHERS              = 4.

CASE sy-subrc.
  WHEN 0.
    WRITE: 'Number of records inserted: ', gv_numrec.
  WHEN 1.
    WRITE: 'ERR! Insert failed...'.
  WHEN 2.
    WRITE: 'ERR! ADSO not found'.
  WHEN OTHERS.
    WRITE: 'ERR! Something (what?) went wrong'.
ENDCASE.

NEW-LINE.
NEW-LINE.

LOOP AT gt_log_msgs INTO gs_log_msg.
  CALL FUNCTION 'RS_MSG_TEXT'
    EXPORTING
*     text_type          = 'L'
      msgid              = gs_log_msg-msgid                 " Message ID
      msgno              = gs_log_msg-msgno                 " Message Number
      msgv1              = gs_log_msg-msgv1
      msgv2              = gs_log_msg-msgv2
      msgv3              = gs_log_msg-msgv3
      msgv4              = gs_log_msg-msgv4
*     dialog             = abap_true
    TABLES
      msgtext            = gt_log_msgs2
    EXCEPTIONS
      message_not_exists = 1
      OTHERS             = 2.
  IF sy-subrc <> 0.
  "ERROR
  ENDIF.
ENDLOOP.

LOOP AT gt_log_msgs2 INTO gv_log_msg2.
  WRITE: / gv_log_msg2.
ENDLOOP.

Please feel free to improvise the report at your end. The video below compares the results between the source and the target system:

Is Delta Possible?

No. But there is a workaround. You can use the time stamp which is provided as a field (CreationDateTime) in the API. You can use this field as the filter in the URL of the API and can manage the data that you would like to extract from source system as per the filter value of this field.

What are the other options?

You can also use the BADI provider: RSO_BADI_PROVIDER. However, you need to have the reporting logic built at your end through the class that implements the interface IF_RSO_BADI_PROV. You can use the standard extractor and use the BADI class for building your reporting logic.

Concluding with Additional links:

Please feel free to share your thoughts by commenting on this blog. Also, we can use this blog as a communication tool for any clarification that you require on this topic. You can also follow my profile for more content on analytical application for group reporting. I would recommend posting your questions by specifying the tag: “SAP S/4HANA Finance for group reporting”

Creating BAdI Providers | SAP Help Portal

Transaction Data for Group Reporting – Read (Version 2) | SAP Help Portal

CDS View Consolidation Group Journal Entry Item for Data Extraction | SAP Help Portal

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Kiril Shagunov
      Kiril Shagunov

      Hello Hari,

      Firstly, thank you for the informative blog post, it was really helpful in regard to understanding the functionality and implementation process of the given API. Although I stumbled upon a problem when trying to implement the previous version of it (API_GRTransactionData_SRV).

      You mentioned in your post, that the service must be implemented in the target system, which you defined as a BW/4HANA. After searching for it in our internal BW/4HANA system (Release 2021), the service could not be found, but after doing some research I was able to activate it in our S/4HANA (Also Release 2021).

      My question is, did this API become available in BW/4HANA with the 2022 Release or are target and source system falsely defined?

      Thank you in advance.

      Author's profile photo Hari Venkatesh Subramanian
      Hari Venkatesh Subramanian
      Blog Post Author

      Hello Kiril,

      Thanks for your update.

      Please note OP 2021 refers to S/4HANA. Also, the API: API_CNSLDTNGRPJRNLITEM is delivered with OP 2022 as mentioned above. You need to add the service in BW/4HANA system which is the target system by establishing the connection to your S/4 system which needs to be OP 2022.

      The screenshot above of the Gateway transaction is from BW/4HANA system and has an RFC connection to S/4HANA OP system.

      Thanks,

      Hari

      Author's profile photo Phillip Laycock
      Phillip Laycock

      Thanks Hari

      Your blog has been very helpful for helping us load S4 Group Reporting data to BW4. Now that we have loaded data into BW we have a report comparable to Group Data Analysis (C_CnsldtnRpt30Q). In BW we now need to add the Reporting Items so that we can replicate  Group Data Analysis with reporting rules C_CnsldtnRpt40Q. Have you got any tips on how we could achieve this?

      Thanks

      Phillip

      Author's profile photo Hari Venkatesh Subramanian
      Hari Venkatesh Subramanian
      Blog Post Author

      Thanks, Phillip.

      You need to use the extraction view I_CnsldtnRptgRule (OP 2022 and above). This view helps you to extract the entries from run time table of reporting item hierarchy as per the reporting rule variants. After which, you can have a look at the logic in the method CL_FINCS_GRA_CUBE_OPRN_REPRULE -> GET_JOIN_CLAUSES.

      Author's profile photo Phillip Laycock
      Phillip Laycock

      Thanks Hari This has really helped clarify how the joins work! Greatly appreciated.

       

      Phillip