Skip to Content
Technical Articles

Get the best out of OData in Microsoft Excel

Hi all,

OData is a great protocol for creating powerful queries. As is it used via HTTP, the queries can be used literally anywhere: In your own plain JavaScript / Java / Python app, in SAPUI5, as a SAP Fiori Elements app or in Microsoft Excel.

This blog post targets the last mentioned option, how to use your OData Service in Microsoft Excel and enhance it with extra logic, making your use-case not just a boring GET and TRANSFORM capability šŸ˜‰

Introduction

I think we can all agree that there are cooler ways of representing data, coming from an online resource like OData, than publishing them via an Excel spreadsheet.

Nevertheless, some customers do require the data to be stored in an excel file, instead of an app.

In one of my recent projects I learned, that all of the transmission system operators (the big companies providing you and your whole country with energy) like to stick with Excel. Instead of building Fiori apps for their reporting requirement (building a tool which allows them to monitor different kinds of energy and electricity data), they requested it to be in Excel, as they managed the data before the automation played a part in this process.

In former times, a SAP tool namedĀ SAP Gateway for Microsoft: Exel Add-In provided some very nice capabilities like customized prompts when opening Excel. Unfortunately, this tool is no longer under maintenance. Therefore, this was not an option.

An out-of-the-box approach was needed, which is illustrated in the following blog post. I hope this is a good alternative for you, too!

 

The idea of extra logic in an OData Get Request

As I was talking about the prompt, SAP Gateway for Microsoft: Exel Add-In provided, the parameterized OData option might come to your mind. To keep it short and simple: This kind of OData Service does not work in Excel. You will receive an error and that’s it. No support for this approach.

But what we’ve got is the filter option. Building our project in the SAP Gateway Service BuilderĀ (transaction SEGW), (option 2 and 3 from my previous blog post) allows us to apply all kinds of custom logic to the filter input.

And this is exactly what we discuss in this blog post: How to manipulate the OData provided filter option in Microsoft Excel, using the SAP Gateway Service Builder.

 

Prerequisites

In the following part, I will stick to option 3 from my previous blog post as this is, at least in my opinion, the most convenient way of getting the required OData Service up and running.

The mandatory prerequisites are mostly the same as if you just publish an OData Service:

  • Creating an ABAP CDS View
  • Reference the created ABAP CDS View in your SAP Gateway Service Builder project as our data model
  • Creating the Runtime Artifacts

First of all, we need to redefine theĀ GET_ENTITYSET method, which was automatically created by creating the Runtime Artifacts in the SAP Gateway Service Builder project.

You can do this by opening the Runtime Artifacts folder ā†’ identify your Data Provider Class Extension (your artifact ending with _DPC_EXT) ā†’ right click and “Go to ABAP Workbench” ā†’ Switch into the Change Mode ā†’ Click on the method namedĀ [your_entity_name]_GET_ENTITYSET ā†’Ā Click on “Redefine method”

 

By now, we basically removed the out-of-the-box capability which was provided by using the ABAP CDS View for reference. Therefore, we need to add theĀ SELECT functionality again:

CLASS zcl_c_reporting_dpc_ext IMPLEMENTATION.

  METHOD zdemo_reporting_get_entityset.

    SELECT * FROM zc_reporting INTO CORRESPONDING FIELDS OF TABLE @et_entityset.

  ENDMETHOD.
ENDCLASS.

 

Now, it’s time to create our custom logic and use it in the target Excel spreadsheet.

 

Getting started: The codebase

As described, we want to use the handed-in filter input and use it not really as just “filtering the data”. This could be done automatically without our manual effort included. But we want some more logic in, as seen in the following code snippet:

CLASS zcl_c_reporting_dpc_ext IMPLEMENTATION.

  METHOD zdemo_reporting_get_entityset.

   DATA(lr_util) = NEW zcl_select_util( ).  
   DATA(lv_report_reference) = ''.

   " Section 1: Get filter parameter data, already transformed as a WHERE clause
  DATA(lt_filter_so) = io_tech_request_context->get_filter( )->get_filter_select_options( ).
 
  IF NOT line_exists( lt_filter_so[ property = 'Reportreference' ] ). " Filterparameter Reportreference was not provided
   " exception handling
  ENDIF.

  DATA(lv_report_reference) = lt_filter_so[ property = 'Reportreference' ]-select_options[ 1 ]-low.

   IF lv_report_reference IS NOT INITIAL.
      " Section 2: do something extraordinary, like:
      lr_util->save_reported_data( lv_report_reference ).
   ENDIF.

   " Section 3: Get timestamp from the latest save for reported data
   DATA(lv_last_report_time) = lr_util->get_reported_data().

   " Section 4: Select data from original ABAP CDS View plus custom logic in WHERE clause 
    SELECT * FROM zc_reporting WHERE current_timestamp > @lv_last_report_time
      INTO CORRESPONDING FIELDS OF TABLE @et_entityset.

  ENDMETHOD.
ENDCLASS.

So, what is the code about? Before that, let me explain this small use-case. The customers reporting requirement says that data (loaded from OData Source into the Excel spreadsheet) can be viewed as often as one likes.

But there needs to be a possibility to “close” the reporting data, causing it to not be shown again in the Excel spreadsheet (imagine something like a list, where data which is processed should not be displayed anymore, letting the end-user focus on more recent and especially new data).

We fulfill this requirement by adding a filter parameter namedĀ Reportreference. The end-user is able to provide some kind of comment (like his/her name plus the current date), causing the report to be closed and not displayed anymore.

After we clarified the use-case, we can care about the code mechanic itself..

InĀ Section 1Ā we just extracted the filter parameter having the nameĀ Reportreference. Make sure to handle the situation in which the user did not provide the parameter properly (e.g. show all/none/prefiltered data)!

Section 2 shows an idea of how to process the input parameter further. We check if it was not empty (meaning the end-user does not want the report to be closed!), and close the report afterward. From the technical side, this could be realized by an ABAP database table, containing a unique ID for the report, the current timestamp of closure and the reference comment from the end-user.

In Section 3 and Section 4 we check whether this exact report has been closed before and adjust our selection by the timestamp of closure.

One piece is still missing. Our report data probably won’t have a Reportreference column, as the closure of the report has nothing to do with the data model. Therefore, we simply introduce a new column to our previously created ABAP CDS View:

@VDM.viewType: #CONSUMPTION
define view ZC_REPORTING
  as select from zdemotable
{       
  key report_id as id,
      current_timestamp,
      // all your required columns

      // custom parameter: Reportreference
      cast ( '' as char30 ) as Reportreference
}

Do not forget to cast it to a longer char since ‘ ‘ is only a one-character field! In this example, we provide a 30 character field as comment-field.

 

That’s not really rocket science, isn’t it? And that’s why it is so handy and super clean to do.

Let’s continue with the Excel-side.

 

Getting started: The Excel spreadsheet

After adding the OData Service to an Excel spreadsheet using your backend credentials you will see all required columns plus the empty Reportreference one.

In case you do not see the Queries & Connections pane, go to the ribbon “Data” ā†’ “Queries & Connections” and double click the single query. The Excel Power Query Editor will appear.

To provide your customer with the maximum support, create a text filter by expanding the Reportreference using the dropdown indicator next to the column.

Feel free to rename the whole query as seen in the previous figure.

For now, ignore the upcoming Filter RowsĀ pop-up and press OK.

Under the applied steps, you will notice a new entry below “Source” which is your recently created filter on Reportreference. Right-click it and rename it, so your customers will understand:
All you need to teach your customers is, that for every new report they want to close, they have to double-click this applied step to come to the single relevant pop-up besides the Excel spreadsheet itself.
An exemplary closure of a report with the reference commentĀ PW 2020-08-14 Report OKĀ is shown here:
Please remember that we defined the additional column in the ABAP CDS View as CHAR30, therefore we should not exceed the comment with 30 characters. If we do, the backend will just cut the report reference after 30 characters strict. But you can use another data type, allowing the customer to enter larger strings, numbers or dates.
Since we provided input to the filter Reportreference, this report data will be closed and will never appear in the Excel spreadsheet again (this was our custom requirement!).

 

Conclusion

Just because a customer needs his/her data in Excel does not mean that these are easy-to-realize requirements.

It is your task to provide the best experience and capabilities despite the used technology or process. This blog post shows a way to enhance simple and straight-forward GET requests from OData Services in Excel.

Furthermore, I am absolutely sure, that there are way more “workarounds” like this, since the GET_ENTITYSET method does not only has filter parameter as importing parameter. There is a whole bunch of incoming information like objects, structures, tables and variables. Additionally, OData is a very powerful protocol and it’s smart, too.

I would love to hear about your ways to extend the OData in Excel functionality with such workarounds!

Maybe this blog post inspires you to try out new things, making your customer even more happy and amazed about what is possible in the very well known Microsoft Excel. They will be curious to try out new things.

Believe me, your customer will be stunned about your new features!

 

Thanks, Peter

5 Comments
You must be Logged on to comment or reply to a post.
  • Nice post.

    But please do not use the import parameters starting with IT_… from theĀ  signature of the GET_ENTITYSET method.

    These are deprecated and do not support things such as conversion exits in a proper way.

    You should rather use the io_tech_request_context object that lets you retrieve all necessary data.

     

    See this code that would use the $filter statement converted into an ABAP SQL statement including conversion of filter values for your convenience.

    Best Regards,

    Andre

     

     data: lv_osql_where_clause type string,
              lv_top               type i,
              lv_skip              type i,
              lv_max_index         type i,
              n                    type i.
    *- get number of records requested
        lv_top = io_tech_request_context->get_top( ).
    *- get number of lines that should be skipped
        lv_skip = io_tech_request_context->get_skip( ).
    *- value for maxrows must only be calculated if the request also contains a $top
        if lv_top is not initial.
          lv_max_index = lv_top + lv_skip.
        endif.
        lv_osql_where_clause = io_tech_request_context->get_osql_where_clause__convert( ).
    
        select * from sepm_i_salesorder_e
          into corresponding fields of table @et_entityset
          up to @lv_max_index rows
          where (lv_osql_where_clause).
    *- skipping entries specified by $skip
        if lv_skip is not initial.
          delete et_entityset to lv_skip.
        endif.
    *-  Inlinecount - get the total numbers of entries that fit to the where clause
        if io_tech_request_context->has_inlinecount( ) = abap_true.
          select count(*)  from   sepm_i_salesorder_e where (lv_osql_where_clause) .
          es_response_context-inlinecount = sy-dbcnt.
        else.
          clear es_response_context-inlinecount.
        endif.
    

     

    • Hi Andre,

      thanks a lot for the hint! I updated the code snippet to use io_tech_request_context to extract the different pieces of the filter (without the transformation to a full WHERE clause), required in the example.

      – Peter