How to prepare OData as a Data Source in Excel
- Ana Chua
This article demonstrates how to prepare an OData Service in SAP and use it as a Data Source in Excel.
Procedure
In this example, we will create an OData Service based on an existing application called IQX_IM_CER.
Step 1 | In the backend system, create a Structure in transaction SE11 based on the fields that you would like to display from the application’s Data Model.  |
Step 2 | In the backend system, create a Project in transaction SEGW.  |
Step 3 | Create an Entity Type by right-clicking on Entity Types > Create |
Step 4 | Define the ABAP Structure Type Name of the Entity Set using the structure created in Step 1. |
Step 5 | Import the fields from the structure by right-clicking on the name of the Entity Type > Import > Properties In the Wizard, select all the fields that you would like to import and define the Key. |
Step 6 | Click the button Generate Runtime Objects to generate the Implementation Class.
Take note the names of the Data Provider Extension Class, Method Name for the GetEntitySet (Query), and the Registered Service. |
Step 7 | Redefine the Method in transaction SE24 or SE80. Sample code: METHOD requestset_get_entityset.
DATA:
lv_formname TYPE /iqx/form_name,
lv_where TYPE string,
lt_inst TYPE STANDARD TABLE OF /iqx/formhd_inst,
ls_inst LIKE LINE OF lt_inst,
lt_data TYPE /iqx/forms_instance_data_tt,
lt_related_data TYPE /iqx/forms_related_data_tt,
ls_data TYPE zcl_iqx_im_cer=>t_data,
lo_data TYPE REF TO data,
lr_data TYPE REF TO zcl_iqx_im_cer=>t_data,
lo_assist TYPE REF TO /iqx/cl_forms_assist.
FIELD-SYMBOLS <ls_data> TYPE any.
lv_formname = 'IQX_IM_CER'.
SELECT * FROM /iqx/formhd_inst INTO TABLE lt_inst WHERE zform_name = lv_formname AND deleted = space.
LOOP AT lt_inst INTO ls_inst.
CLEAR: lo_assist, ls_data.
CREATE OBJECT lo_assist.
CALL METHOD lo_assist->read_form_data
EXPORTING
iv_form_name = lv_formname
iv_instance = ls_inst-zinstance
IMPORTING
er_data = lo_data
EXCEPTIONS
no_authorization = 1
no_instance_data = 2
OTHERS = 3.
CHECK sy-subrc = 0.
ASSIGN lo_data->* TO <ls_data>.
MOVE-CORRESPONDING <ls_data> TO ls_data .
APPEND INITIAL LINE TO et_entityset ASSIGNING FIELD-SYMBOL(<ls_entity>).
<ls_entity>-req_id = ls_data-zdocumentnumber.
<ls_entity>-current_status = ls_data-currentstatus.
<ls_entity>-proj_name = ls_data-summary-projectname.
<ls_entity>-grand_total = ls_data-header-grandtotal.
<ls_entity>-company_code_currency = ls_data-summary-companycodecurrency.
<ls_entity>-inv_reason = ls_data-summary-investmentreason.
<ls_entity>-inv_reason_desc = ls_data-summary-investmentreasondescription.
<ls_entity>-asset_cat = ls_data-summary-assetcategory.
<ls_entity>-asset_cat_desc = ls_data-summary-assetcategorydescription.
<ls_entity>-fund_cat_desc = ls_data-summary-fundingcategorydescription.
<ls_entity>-total_budget_used = ls_data-summary-totalbudgetused.
<ls_entity>-expected_start = ls_data-summary-expectedstart.
<ls_entity>-expected_capitalization = ls_data-summary-expectedcapitalization.
<ls_entity>-fiscal_year = ls_data-summary-fiscalyear.
ENDLOOP.
SORT et_entityset BY expected_start DESCENDING.
ENDMETHOD. |