Consume a custom CDS with parameters exposed as an OData API

[pascalrenet]

I recently, for a demonstration purpose needed to create a custom CDS in SAP S/4HANA Cloud Public Edition and expose it as an external API. At this point I am thinking, I got this.

The content that was going to be exposed was neither here nor there, so I pretty much randomly picked a CDS to expose, selected a few fields (elements) to return some data, and did not really pay attention to the Parameters tab of the CDS (where parameters had been automatically added), before saving it.

To make sure, we are aligned on what I mean by parameters, I included a screenshot below (screenshot taken in the Custom CDS app):

To make sure data will be served when I call the API, I quickly hit the 'Data Browser' button, a familiar popup came up asking me to enter values for the CDS parameters, which I did as below.

Whilst data was served, there was a moment there where I thought, mmhhh. Is that (parameters) going to be a problem? Has not been one so far, so let's plough on. I finish exposing it as an External API by creating a custom communication scenario and communication arrangement for it. So far so good, I am on fire!

 I then confidently went into my browser and pasted the Service URL generated in the communication arrangement, appended with the name of my custom CDS, which in this case came out to be:

h ttps://myXXXXXX-api.lab.s4hana.cloud.sap/sap/opu/odata/sap/YY1_EXTERNALSALESDATAAPS_CDS /YY1_ExternalSalesDataAPS

..and proudly hit enter, entered my authentication details expecting data to appear magically...only to be greeted with an error message! As shown below:

Huh? Error? Parameter entity set?

So this error message already gives you a hint with respect to the source of the issue : Parameters. So the next question of course is going to be, how do I pass them on to my OData service to successfully call my API? Like probably anyone having to troubleshoot such an issue, I went to SAP Support and Google, and came up with two very helpful results.

The first one was a blog written by my colleague Amith Nair, from 2022 named 'Custom CDS view with Mandatory Parameters as an OData Service in S4HANA Cloud '. The second was this SAP Support note 2600276 - Exposing a CDS view with parameters as OData error code LCX_MISSING_PARAMETER. 

Putting these two results together gave me the blueprint to resolve my issue.

As per the SAP Support note 2600276 above, The first thing I needed to do was to dive in the metadata of my service, which, to reach it is essentially the service URL (as above) appended with the word $metadata, which in this case was:

h ttps://myXXXXXX-api.lab.s4hana.cloud.sap/sap/opu/odata/sap/YY1_EXTERNALSALESDATAAPS_CDS/$metadata

Step 2 of the SAP Support Note is to find what is essentially the name of the Custom CDS that you have exposed as an external API. You can therefore get this from the Custom CDS app or you can also get it from the metadata since you are already in it. Following the instruction of the SAP support note, find this string in the metadata.

Parameters" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:content-version="1"/> , and note down the corresponding EntitySet Name. In my case, I have this:

Step 3 is finding the parameters used by the service.

Looking for an EntityType with Parameters as semantics, I found the below:

If you are here then you have covered step 1, 2 and 3 from the above SAP support note. 

Still in the Metadata of the service, we move on to step 4...and this is where maybe some clarification is required. The note says, to search for the following string of text:

<NavigationProperty Name="<RESULTSNAME>" 

If you try search for this exact string, the result will most likely be NULL! To clarify, what it is asking you to do, is to search for the string <NavigationProperty Name=  ...and to replace "<RESULTSNAME>" with what is there! In my case it was "Results", as shown in the picture below.

Step 5 is essentially making an inventory of all the pieces we have looked for which were:

1. The Parameters of the CDS, which are P_ExchangeRateType and P_DisplayCurrency. In the URL, when we pass the parameters, they must be enclosed in brackets, in the right order, comma separated, and the format must be NameOfTheParameter='ValueOfTheParameter'. So, in our case we will have (P_ExchangeRateType='M',P_DisplayCurrency='EUR')
2. The name of the Custom CDS (EntitySet Name above), which in our case is YY1_ExternalSalesDataAPS
3. The name of the Navigation Property Name, which was Results

Putting it all together, the right URL to successfully call our custom API with parameters is:

h ttps://myXXXXXX-api.lab.s4hana.cloud.sap/sap/opu/odata/sap/YY1_EXTERNALSALESDATAAPS_CDS/ YY1_ExternalSalesDataAPS(P_ExchangeRateType='M',P_DisplayCurrency='EUR')/Results?$top=5

Once again we anxiously hit enter and ....we get data!

 So hopefully this blog will be helpful to you, if you are ever caught in a rut trying to call a custom CDS with mandatory parameters exposed as an external API! On a side note, I would also maybe say that it is worthwhile spending a little extra time trying to find a CDS that does not require the use of parameters, if you don't need them! For example in my haste, I chose the CDS I_SalesOrderItemCube (which is a Cube - with parameters) when I could have chosen the CDS I_SalesOrderItem (which is a Dimension - without parameters) - really depends on the use case that you are trying to serve.