CDS and Parameters, adding required info to the URL, HOW?

[former_member564457]

Hello everyone,

A custom CDS has been generated by another user and it contains parameters.

When the URL is created, the required information for the parameters is omitted from the URL.

Thus, when trying to use the generated URL within Excel, using the Get Data functionality (Get Data>From Other Sources>From ODATA fee) I get an error reported by Excel stating that the request failed... with part of the error message indicating that the YY1_... source "was not found".

Searching around, I have found a SAP Knowledgebase article # 2600276 that attempts to explain possible solutions to my problem.

Trouble is, being very new to this SAP development stuff, I don't know how to really interpret the information given in the SAP Note.

The information in the note states the following:

Reproducing the Issue

1. Using your S/4HANA Cloud account, login to your Fiori Launchpad
2. Expose a custom CDS view based on the standard view provided by SAP that has few parameters.
    
   Note: If the parameters are hidden, make them unhidden and republish your CDS view
    
3. Create an OData communication arrangement scenario using the application Maintain Communication Arrangements
4. Call your OData Service using for example POSTMAN as follow https://<URL>/sap/opu/odata/sap/YY1_<ODATA-SERVICE>;
5. User gets list of EntitySets
6. Call any of the EntitySets error occurs

Cause

Calling an OData EntitySets with parameter like that, <URL>\YY1_<ODATA-SERVICE>\EntitySets is not correct. Refer to the resolution section

Resolution

To call the correct Entitysets with parameter, do the following:

1. Get the metadata first, <URL>\YY1_<ODATA-SERVICE>\$metadata
2. In the metadata locate your EntitySets and check its Entity type, it will be something like this: 
    <EntitySet Name="YY1_<NAME>" EntityType="YY1_<ODATASERVICE>.YY1_<NAME>Parameters" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:content-version="1"/>
3. Then locate the the <EntityType Name="YY1_<NAME>Parameters" as shown below
   <EntityType Name="YY1_<NAME>Parameters" sap:semantics="parameters" sap:content-version="1">
                   <Key>
                       <PropertyRef Name="P_1"/>
                       <PropertyRef Name="P_2"/>
                   </Key>
4. Under the Parameters EntityType find the following:
    <NavigationProperty Name="<RESULTSNAME>" .../>
               </EntityType>
5. Now the user can form the URL as follow https://<URL>/sap/opu/odata/sap/YY1_<ODATA-SERVICE>/YY1_<NAME>(P_1='<WHATEVER-VALUE>',P_2='<WHATEVER-VALUE>')/<RESULTNAME>

1. <RESULTNAME>: Whatever the name is from step 4
2. YY1_<NAME> is the EntitySet Name from step 2
3. P_1, P_2: Name of parameters from step 3, If the parameters list has X numbers of parameters, they need to be listed

Note: If the result is very large, we can add $top=1 for testing purposes like that:
https://<URL>/sap/opu/odata/sap/YY1_<ODATA-SERVICE>/YY1_<NAME>(P_1='<WHATEVER-VALUE>',P_2='<WHATEVER... 

No idea what they are referring to with regards to the "EntitySets" in the first part and the $metadata tag referred to in point 1 in the Resolution section.

Can anyone please offer some advice/guidance to a beginner in this area?

I have been given an ODATA feed url that I have used in another project, but that one did not contain a Parameterised CDS query.

Thanks in advance,

Brian.

[Amith_Nair · Accepted solution]

Hi Brian,

I hope you’re all well!

As I understand from the problem statement, a custom CDS views has been created that has to be exposed into a excel and you’re running into an error, right?

If this is the case, I would assume, you have satisfied the following pre-requisites:

1. A custom CDS view with API (OData) enabled is checked marked. This helps you to expose your custom CDS view outside of S4HC. Publish the Custom CDS View.

2. A custom Communication scenario is created, and it must be published as well.

3. The Custom Communication scenario created should be linked to a Communication arrangement and must be saved and activated as well.( A communication user and Communication system should be satisfied as well)

4. Now, you will have the OData link available.

Now, before you expose your custom CDS view into an excel, I would recommend you call this OData service in a browser to confirm if the data is populating. For this you can do the following:

1. Open the browser incognito mode, call the URL in the following format(sample for your reference):

https://myxxxxxxx-api.s4hana.ondemand.com/sap/opu/odata/sap/YY1_ODATA_TEST_GLACCOUNT_CDS/?saml2=disa...

enter the credentials you created under communication user(while creating communication arrangement?)

2. Now, to fetch the metadata, use the following sample as reference by appending $metadata

3. https://myxxxxxx-api.s4hana.ondemand.com/sap/opu/odata/sap/YY1_ODATA_TEST_GLACCOUNT_CDS/$metadata?sa...

please note that Metadata can only be fetched in second call.

Now, if this works successfully (if you’re able to fetch the desired result) you’re OData to good to go and we can be exposed successfully!

Now, you may try to expose this into an Excel. Here is a YouTube link from one of our colleague who has demonstrated your use case neatly with some check points that should help you to take forward.

https://www.youtube.com/watch?v=figt8pCOO0M&index=5&list=LLf65ovoh3T2MrCGu99IqQdw&t=40s

Please let me know how this goes and if we need further troubleshooting, then it must be logged as an incident so that we could further evaluate this in your S4HC tenant.

Hope this helps!

Thank you! Amith Nair