S/4HANA Cloud Integration | Microsoft Excel

hoang_vu · ‎08-17-2018

Overview

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

In hobruche '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.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 🙂

former_member131481 · ‎08-20-2018

Excel: The Swiss Army Knife of Data Processing. Thanks for sharing!

Andrew · ‎08-20-2018

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!

hoang_vu · ‎08-20-2018

Yes you are correct, I have just updated the blog with the relevant steps 🙂

Johnny_B_ · ‎08-27-2018

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

hoang_vu · ‎08-27-2018

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

Johnny_B_ · ‎08-28-2018

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

Johnny_B_ · ‎08-28-2018

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

hoang_vu · ‎08-28-2018

Hi Johannes,

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

Glad you got it working!

Regards,

Hoang

Johnny_B_ · ‎08-28-2018

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

hoang_vu · ‎08-29-2018

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

Anna_Rafter · ‎10-06-2020

Hello Hoang,

Thank you for your blog.

We are having some issues depending on the excel version.

When we get data from OData feed using EXCEL Microsoft MSO (16.0.13029.20342) 64 bits, we get information with no errors.

When using Microsoft Excel 2016 MSO (16.0.4266.1001) 32 bits we get the following error message:

DataSource.Error: "OData: Request failed. The underlying connection was closed. Could not establish trust relationship for the SSL/TLS secure channel."
Detalles:
DataSourceKind=OData
DataSourcePath=https://myxxxx-api.s4hana.ondemand.com/sap/opu/odata/sap/YY1xxx

Is there some additional configuration to be done depending on the version?

Thank you in advance.

Kind Regards,

Anna

hoang_vu · ‎10-07-2020

Hi Ana,

unfortunately as I do not have the other version, I have not encountered this issue.

Maybe a functionality missing in the OData feed option in Excel? But just a wild guess.

Regards,

Hoang

Anna_Rafter · ‎10-07-2020

Hello Hoang,

I’ll let you know. We have tested different options, but could not find a solution.

Thank you for such a quick answer.

Kind Regards,

Anna

Update: We deleted microsoft excel 2016, and downloaded microsoft 365. It is now working. 🙂

Kind Regards,

Anna

selvakumar_mohan · ‎03-02-2022

Hi Johannes,

Could you please elaborate on the same, we have similar requirement, I would need to explore my on prem odata services to be consumed in Excel.