OData for CDS with Parameters

[vishnucta]

Hi Experts,

Let me share my experience and 'How to' while creating OData Service for CDS View with Parameters.

Disclaimer:

I am not sure whether the steps i will be explaining is the right one but i would like to share how i did it.

Step 1:

Add the Package to Eclipse Project Explorer and create a DDL Source .



Step 2:

Define a sample view with Parameter and Activate it.



Anotation OData.publish will expose the CDS View as OData Service.

Step 3:

Add the Service using the /iwfnd/miant_service.

Step 4:

Get the metadata and find out the entitySet of an entity without a sap:parameter



Step 5:

Execute the URL as shown below



voila thats the result...

Open to comments and anybody can edit appropriately.

Happy Coding

Vishnu

[sudhanva_l]

Hello Vishnu,

I have followed the steps as mentioned in the blog, but i am getting error while executing the URI as 'Resource not found for segment'.

Could you please suggest!

Thanks in advance.

Regards,

Sudhanva

[vishnucta]

Hi sudhanva_l

Provide me the request URI and the Respoionse in gateway client or from chrome browser.With that i can help u out.

Regards,

Vishnu

[rajiv_kumar20]

Hi Vishnu,

If I want to get multiple values from the service, how can I pass the parameters. In your example, only one row is retrieved. Use case: Suppose I want to retrieve all employees of a department, so I give Dept_Id as parameter, and key will be obviously employee id only. So I don't want to give empid as input parameter in service, but only the dept id. Is it possible?

Rajiv

[vishnucta]

Hi Rajiv,

Inside the CDS Definition you can custom code required queries.You can even have a Definition without parameters and in this sample Parameter is used in where clause for READ operation.

Regards,

Vishnu

[vishnucta]

After Executing this in Client you can query all the results.Hope this helps.

Regards,

Vishnu

[Sudhakargadde]

Hi Vishnu,

Which version of SAP is required to run this, we are currently using Release 740 and Service pack level 0004. when go to the SICF T-code navigate to the path my service is not there, Please suggest what should we required.

Regards

Sudhakar

[vishnucta]

According to Documentation NW 740 would suffice i suppose.

After creating your CDS and exposing it as OData you must go to T-Code /iwfnd/maint_services to add the Service.Then it would appear in the ICF.

Regards,

Vishnu

[Former Member]

Hi Vishnu,

I am trying to create an Odata services based on a CDS view with parameters on NW 740 with SPS 12. I have created the service via SEGW. But the problem is I cant access the same via the URL.

My CDS view has an input parameter p_dist which is not part of the SQL view and hence the URL keeps throwing an error.

Could you please if possible share screen shots of the service that was generated in the system , so I could look at how you have mapped the entities.

Thanks

Divya

[raina_goyal]

Hi vishnu.pankajakshan,

I have created a CDS view with input parameter and also created service via SEGW. I am following two approaches :

1. While I am testing the service in gateway client, it returns me 200 and when i execute the uri for metadata , it is working fine. But when i follow step 5 of your blog ie. when i test the service with my parameter value as depicted in your step 5, it throws me error 500. I have tried every possible way and unable to understand where am i going wrong. Should i need to code or redefine any method or what. Please guide me.

2. When I use  annotation @odata.publish:true in the CDS view it throws me warnings for the cds view as view does not exist and odata exposure is not possible which is restricting CDS view to activate also when I am checking in /iwfnd/maint_service , I am unable to find my service to add it. Or it can be said that I am confused how to find the automatically created service by using this annotation.

So please guide me and help me  to solve this problem as I am struggling a lot in this and have an urgent requirement for this. Your guidance would be helpful.

Thanks,

Best Regards,

Raina

[vishnucta]

Hi raina.goyal,

Sorry for the delay..can u provide the metadata information of ur service.... Give me a screenshot for the GW_CLIENT Exection.

Kindly letme know the system info.

Regards,

Vishnu

[vishnucta]

Give me the metadata information.

[raina_goyal]

Hi vishnu.pankajakshan

Please find the below screenshot for the metadata information of my service:

System is ECC 7.4

Thanks,

Best Regards,

Raina

[vishnucta]

Your metadata seems fine.. i think since you system is ECC and 7.4 NW you need to try the traditional method of  service creation using SEGW where u can create one using CDS Views.I have seen documents explaining those.I came to this conclusion becoz it throws for you while using OData.publish . This was introduced in NW 7.5 System i suppose.

Regards,

Vishnu

[raina_goyal]

Hi Vishnu,

As I have mentioned earlier in my first comment , I have created my service using SEGW also, the service is working fine with metadata but while I test it with particular input parameter of the CDS view as you are doing in step 5 of your blog i.e URL/zemployee_SAM_PARSET(p_empid=2,empid=2). For my case URL/SANKYSet(yfvglobcc=50290), it is giving me 500 error. So I need to know where am I going wrong.. Should i need to code or redefine any method or what?Could you please help me and guide me.

Thanks,

Best Regards,

Raina

[vishnucta]

Even though i haven't personally tried exposing CDS View to OData via SEGW let me try.For that can you try giving me the error_log details??? and since its a READ Opeation can u try debugging the GET_ENTITY Method and lemme know what u get there.. :smile:

Regards,

Vishnu

[SAP_Gopal]

Hi Vishnu,

Thanks for the blog. It was good and informative.

Hi Rajiv,

If you want to use Dept_Id as parameter and empid is your key, then you can use something as follows:
/sap/opu/odata/sap/ZEMPLOYEE_SAM_PAR_CDS/Zemployee_Sam_Par(Dept_Id='010')/Set

If you check the screen shot of $metadata in the blog by Vishnu, one entity type with name Zemployee_Sam_ParParameters is created and corresponding entity set is Zemployee_Sam_Par (which is equivalent to name of CDS).
Also, a, association from Parameter Set to CDS View Set (Zemployee_Sam_ParSet) is created having Navigation Property Name as "Set".

Best Regards,
Gopal

[vishnucta]

@raina_goyal have u tried debuggging the DPC Classes and checked for any exception.????

[former_member242586]

Hi Vishnu,

How will you pass the non-key attribute as a parameter in odata URL?

it will be helpful if you share the information.

 

Thanks & Regards,

Dikshith

[harikrishnareddy_p]

Hi Vishnu,

I have followed the same steps for creating the OData from the CDS view by using the OData.pulish:true.

Last week it was working fine, suddenly the metadata of the service got changed. Now the metadata data is loading without the parameters Entity Type and without the Entity Set of an Entity Type without sap:parameter. The same service is giving correct metadata in some other systems(performance system). I have mentioned the metadata links with screenshots below.

Links:

ER9/001 (metadata is wrong ) – https://ldai3er9.wdf.sap.corp:44300/sap/opu/odata/sap/C_TEAMUTILIZATIONRATE_CDS/$metadata

CCW/715(loading correct metadata) –    https://ccw-715.wdf.sap.corp/sap/opu/odata/sap/C_TEAMUTILIZATIONRATE_CDS/$metadata

Do you have any idea why the metadata is loading without parameters?

Thanks & Regards,

Hari

[vishnucta]

Hey Hari,

 

Could you give me the details of the NW Version of ER9 and CCW?

 

Thanks,

Vishnu P

[vishnucta]

@raina.goyal

Your datatype is Edm.String in Metadata but you are passing value as like in an integer. URL/SANKYSet(yfvglobcc='50290')

Sorry for replying very late.

 

Thanks ,

Vishnu P

[former_member538590]

Hi following your ‘How to’ I have managed to create a CDS View and expose oData. I’m able to retrieve the metadata of my service but I cannot execute service from browser request passing parameters.

Bellow is the metadata for the CDS View service

Basically what I want to do is to pass date_from and date_to properties and get the response. The other fields that are members of the key cannot really specify a single entry (and also not really define a key) so I don’t want to have the information to pass it into the request URI.

So I’m trying to call the service with request URI:

:8000/sap/opu/odata/sap/ZCDS_EMPLOYEE_SALES_CDS/ZCDS_EMPLOYEE_SALESSet(date_from=20180913,date_to=20180913)

or

:8000/sap/opu/odata/sap/ZCDS_EMPLOYEE_SALES_CDS/ZCDS_EMPLOYEE_SALESSet(date_from=’20180913′,date_to=’20180913′)

Both returns me the same error

And the error message for both from /IWFND/ERROR_LOG is

This is the definition of my parameters in the CDS View

dats is a data element of type abap.dats Already defined in the system.

Edit: Also the request URI 

:8000/sap/opu/odata/sap/ZCDS_EMPLOYEE_SALES_CDS/ZCDS_EMPLOYEE_SALES(date_from=20180913,date_to=20180914)/Set

Results in the following error:

So, basically I just need to format the date URI properly but I don’t know how. Anyone who could provide me with information or a document on URI format for each type would be welcome! 

Edit2: As mentioned by gopalakrishnan.ramachandran the correct way to pass parameters to pass the parameters into the URI, also I was needed to determine the type of argument and fix the format of the datetime so the working URI for my case was

:8000/sap/opu/odata/sap/ZCDS_EMPLOYEE_SALES_CDS/ZCDS_EMPLOYEE_SALES(date_from=datetime'2018-09-13T00:00:00',date_to=datetime'2018-09-14T00:00:00')/Set?$format=json

[vishnucta]

Hi All,

 

 

Kindly follow the below link that would help us to learn how to consume CDS in OData(Reference Datasorce).

https://help.sap.com/viewer/cc0c305d2fab47bd808adcad3ca7ee9d/7.5.9/en-US/32bc41261af445e08182c853203...

Thanks and Regards,

Vishnu P

 

 

[vishnucta]

Type is 'date time ' and you should pass using exact format.

 

Refer below url.

https://blogs.sap.com/2017/01/05/date-and-time-in-sap-gateway-foundation/

 

[kamesh_g]

Hello Vishnu

I am NW 7.4 and I am not seeing parameters in metadata . Is it only applicable on 7.5

[vishnucta]

Hey,

Yeh NW version does make difference. Try the same in 7.5.

 

[former_member603964]

Hi Vishu,

I am getting below error while I am using annotation odata.publish : true

Model 'ZBRI_VENDOR_CDS' version '0001' not found
- Service ZBRI_VENDOR_CDS does not exist
- Class ZCL_ZBRI_VENDOR does not exist
- Service ZBRI_VENDOR_CDS is not active
- Default Authorization Values are missing for Service ZBRI_VENDOR_CDS 0001
- View has generated Objects
- Entity CDS-ZBRI_VENDOR: Parameter vendor_name has no data element

[vishnucta]

Hey Nisha,

Try consuming the CDS as Reference source rather than exposing via annotation.

There we have lot of flexibility to extend and tailor the service depending on different scenarios.

And try the below document.

https://help.sap.com/viewer/cc0c305d2fab47bd808adcad3ca7ee9d/7.5.9/en-US/32bc41261af445e08182c853203...

Regards,

Vishnu P

[JaimeRodriguez]

Hi, could you fix this error?

 

I'm having the same issue

[jmalla]

This was really helpful.  It's very strange how you have to make the OData with the parameter in the URL with /Set.  I would not have guessed this.  Your comment saved a lot of time.

 

Thanks,

Jay

[former_member658834]

Hi Jaime ,

For the errors mentioned by Shani , check with your basis guy, as this might be authorization issue while exposing cds to Odata via annotation. Take screenshot of su53 from gui once you get this error in adt/hana studio and send it to basis guy.

 

Hope this will work.

 

Regards,

Sachin

[former_member182748]

Very nice blog Vishnu!

I'm struggling to run this oData service from S/4HANA Cloud:

https://my300436-api.s4hana.ondemand.com/sap/opu/odata/sap/YY1_LIQUIDITYPOSITION_CDS/$metadata

It has 3 parameters, but I cannot find a way to make it work. Could you share any insights?

Regards, Chris

[adrian_pmelchor]

Hello! How are you.

I had the same problem in one of my last projects and the request was to use the odata in a Fiori APP.

In order to not have to change the way the Fiori team was used to call the odata we solve the problem with other aproach.

The main view was a regular cds with parameter (p_lang) as we are in a 7.40 versión and the parameter  $session.system_language is not avaliable.

define view ZCP_CDS_xxxxxx with parameters p_lang : abap.lang

  as select from zcp_t_xxxx as table1 left outer join lfa1 as prov on table1.table1 = prov.lifnr 

                                            left outer join adr6 as email  on prov.adrnr      = email.addrnumber

                                            //Descripciones

                                            left outer join ZCDS_yyyyyy( p_lang : $parameters.p_lang ) as f4tipprod      on table1.tipoprod      = f4tipprod.val 

	association [1..*] to ZCP_CDS_JJJJJJ  as _table3 on $projection.key1 = _PRODCOP.key1

{



	table1.key1

} 

We have a main table "zcp_t_xxxx as table1"  and we access another table that also has the parameter language "ZCDS_yyyyyy".

Besides we have another view ZCP_CDS_JJJJJJ that also have in its definition the parameter (omited as is almost the same definition as ZCP_CDS_XXXXX)

Both views are published as entities in the service (segw) so we want to make calls like

/sap/opu/odata/sap/ZXXXXX_SRV/XXXXXXSet

Query on the main table

and

/sap/opu/odata/sap/ZXXXXX_SRV/XXXXXXSet('9012401')/JJJJJJJSet

Query on the second table using the association defined in the main table.

To solve the problem of the parameter we have to redefine the method. IF_SADL_GW_QUERY_CONTROL~SET_QUERY_OPTIONS in our DPC_EXT class.

The implementation purpose is to add programatically the parameter p_lang (sy-lang)

METHOD if_sadl_gw_query_control~set_query_options.



    DATA: lt_parameters TYPE if_sadl_public_types=>tt_entity_parameters,

          ls_parameter  LIKE LINE OF lt_parameters,

          lt_para       LIKE ls_parameter-parameters.



    "Casting in order to get the navigation properties

    DATA: lo_query TYPE REF TO cl_sadl_gw_query_options.

    lo_query ?= io_query_options.

    lo_query->get_navigation_info( IMPORTING et_navigation_path = DATA(lt_navpath)  ).





    "We add all the entities necessary to arrive to the current one

    DATA: lt_entity LIKE TABLE OF iv_entity_set.

    IF lt_navpath IS NOT INITIAL.

      lt_entity = VALUE #( FOR lw IN lt_navpath  ( CONV #( lw-source_entity_type && 'Set' ) )  ).

    ENDIF.



    "Add the current one

    APPEND iv_entity_set TO lt_entity.



	"Add the parameter p_lang to all the entities that need the parameter

    LOOP AT lt_entity INTO DATA(ls_entity).

      CASE ls_entity.

        WHEN    'xxxxxxSet'                   OR

                'jjjjjjjjjjSet'.

          APPEND VALUE #( entity_alias = ls_entity parameters = VALUE #( ( name = 'P_LANG' value = sy-langu ) ) ) TO lt_parameters.

        WHEN OTHERS.

      ENDCASE.

    ENDLOOP.

	

    IF lt_parameters IS NOT INITIAL.

      io_query_options->set_entity_parameters( lt_parameters ).

    ENDIF.



  ENDMETHOD.