How to prepare OData as a Data Source in Excel

Owned by Ana Chua
Last updated: Jan 19, 2021
5 min read

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.