Skip to Content

Overview

I have been reading many great blogs this week, but one blog was almost too good to be true.

In Holger Bruchelt ‘s blog, he showed a quick & simple way on how to consume an OData service in Excel in 5 minutes.

It was so cool that I had to immediately test it myself & of course in the S/4HANA Cloud context 😉

Scenario

We want to display our Business Partner data from our S/4HANA Cloud system in Microsoft Excel.

Activate OData Service in S/4HANA Cloud

First of all we need to activate our Business Partner OData API in our S/4HANA Cloud system.

Following steps are required in the S/4HANA Cloud system:

Create communication system & user

  • Go to Communication Management
  • Communication Systems
  • New
  • Provide a system ID & system name (can be anything for this scenario, for example “Excel”)
  • Create
  • Provide a hostname (can be anything for this scenario, for example “Excel”)
  • Provide a logical system (can be anything for this scenario, for example “EXCEL”)
  • Provide a business system (can be anything for this scenario, for example “Excel”)
  • Under User for Inbound Communication press on “+” & “New User”
  • Provide user name, description & password
  • Save
  • Choose your inbound user, then press OK
  • Under User for Outbound Communication press on “+”
  • As Authentication Method choose “SSL Client Certificate”
  • As Certificate Type choose “Default Client Certificate”
  • Create

Create communication arrangement

  • Go to Communication Management
  • Communication Arrangement
  • New
  • Choose scenario SAP_COM_0008 (Business Partner, Customer and Supplier Integration)
  • Create
  • Under communication system choose your created communication system
  • Disable all outbound services by unticking the service status active flag for each outbound service
  • Save
  • Note down your OData endpoint (https://my30xxxx-api.s4hana.ondemand.com/sap/opu/odata/sap/API_BUSINESS_PARTNER)

Now the service is ready to be consumed by Excel.

You can perform these steps with any OData API that can read data from the S/4HANA Cloud system (GET operation)

For more details check the API Business Hub for S/4HANA Cloud.

Consume OData Service in Microsoft Excel

Follow the steps in Holger’s blog:

  • Open Microsoft Excel
  • Under Data –> From Other Sources –> Choose From OData Feed

  • As URL type in your OData endpoint + “A_BusinessPartner” to call the business partner entity of the OData service (https://my30xxxx-api.s4hana.ondemand.com/sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner)
  • You can replace “A_BusinessPartner” with any other entity that the service provides, for more information check the API documentation
  • When prompted, type in your user & password of your communication user

  • Edit your data if required, otherwise press Load

A whole list with all business partners from your S/4HANA Cloud system is displayed.

Also the Refresh button is working, showing new business partners if they have been created in the meantime (You can check this if additional rows in Excel have been loaded).

Conclusion

This list can be very helpful when going into the process integration workshop in your S/4HANA Cloud project when defining field-to-field mapping from S/4HANA Cloud system to another system (and probably many other good use cases).

Thank you Holger for sharing this cool trick.

Many customers, partners and colleagues will benefit from this!

It definitely helped me a lot.

Check it out 🙂

 

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Andrew Saunders

    This is great Hoang, thanks alot!
    I found I had to put in some additional information in communication system and define an outbound user as well, and then was able to replicate. Cheers!

    (0) 
  2. Johannes Bacher

    Hello Hoang, great blog, really. But it does not work for me.

    In Excel I get this error:

    sorry it is in german. it says that the URL does not poin to any OData Service.

    What could be the reason? Do I Need to expose my S4HC tenant to the public Internet first? It sounds a bit weird that all the Services are just there, to be consumed by anyone who knows the URL.

     

    thank you,

    Johannes

    (0) 
    1. Hoang Vu Post author

      Hello Johannes,

      no service is out of the box available in the internet.

      You have to activate the relevant communication arrangement in your S/4HANA Cloud so that your service can be consumed.

      The service can only be consumed by the communication user defined in your communication arrangement.

      Check if you have followed all the steps under “Activate OData Service in S/4HANA Cloud“.

      Hope this helps,

      Hoang

      (0) 
      1. Johannes Bacher

        Hello Hoang, ok thank you, i though I might Need a Cloud connector or Cloud Integration for such a Service call.

        I still do not get it working. In Excel i enter the URL, but i am not prompted to enter user/Password – is this Maybe missing? Somehow the caller must authenticate himself, i would think.

        thank you,

        Johannes

        (0) 
      2. Johannes Bacher

        Hello Hoang,

        I got it working! I had to reset the data source authorizations, somehow it was set to anonymous, and then i entered the communication user an pwd, and it worked.

        Thanks a lot! this has so much potential.

         

        Johannes

        (1) 
        1. Hoang Vu Post author

          Hi Johannes,

          yes you will definitely need the username & password of your communication user here.

          Glad you got it working!

           

          Regards,

          Hoang

          (0) 
          1. Johannes Bacher

            Hi Hoang,

            I went one step further now, In API Hub i saw the “Code snippets” and there is also ABAP Code provided, so I took this Code, pasted it into an ABAP-report. The only Thing I had to add is the base64-encoded user and Password Information. And I had to Import the Cloud certificate into STRUST.

            And that’s it- I could also read the Business Partners now from an on-premise System within ABAP. Very cool.

            Best regards,

            Johannes

            (0) 
            1. Hoang Vu Post author

              Hi Johannes,

              that is very cool, so quick & easy.

              Thanks for sharing, this will be helpful for many colleagues who would like to call the OData services from an ABAP system! 🙂

              Regards,

              Hoang

              (0) 

Leave a Reply