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.

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