Skip to Content

Passing filter values as parameters to CDS view from ODATA service frontend

We have a main CDS view that is referenced by an ODATA service. Say the main CDS view shows order header information. The main CDS has an association to materials (e.g. order can have multiple materials). All this is fine and works.

The requirement is to add a filter on Material number. If user enters a list of articles then the CDS should only return orders that contain the entered articles. As the main CDS does not hold the material number, this will be a filter on the association to the material CDS.

I understand that a CDS can have input parameters but can it handle filter values from an ODATA service? So the user could enter filter conditions like {contains *12345*}, {equal to 12345}, {ends with *12345} or a combination like {contains *12345*} OR {equal to 11111} OR {equal to 22222} etc etc.

All the parameter examples I have seen use the equals "=" operator in the CDS and non use "IN" operator. For example:

where material = :in_material

Can we use:

where material IN :in_material

If the above is not possible how could we filer the association based on a filter range?

Your thoughts and advice welcomed.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Aug 05, 2020 at 09:14 AM

    Hi Che,

    for OData V2 I would see the option to create a function import, such as

    GetSalesOrderByMaterial?filter=*1234*

    In OData V4 (this however requires at least SAP_GWFND 751 SP09) you will find the following example

    /sap/opu/odata4/iwbep/tea/default/iwbep/tea_busi/0001/Departments?$filter=DEPARTMENT_2_TEAMS/any(d:d/Budget gt 4000 and d/BudgetCurrency eq 'JPY')&sap-statistics=true

    This however requires a code based implementation of the "any" operator which is quite tricky.

    Best Regards,

    Andre

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Andre,

      Thanks for your suggestions we are on SAP_GWFND 750 SP14.

      With the current solution there is no way to use a code based implementation. The gateway passes the filter values entered on the frontend directly to the backend CDS. The filtering works fine for fields that are available in the top CDS (e.g. order number) but it does not work for fields that are only available in the lower CDS's (via association) e.g. material number.

      I did find a blog post which explained how to use CDS virtual elements to apply filtering on associations. When I implemented the solution it seemed to work but I did not pursue it further as it does not work with analytical CDS. In our CDS we have aggregation annotation which makes it analytical and hence the use of virtual elements is not allowed.

      My other solution is to "somehow" in the gateway, if the user enters a filter element which is not in the top CDS (e.g. material number), then to catch that filter and call GET ENTITY to determine list of Order Numbers for the given materials and then fill the Order Number filter instead. Then to "somehow" let the original process continue to call the CDS, if that is even possible. If this solution is possible then my only worry is what if 10k+ order numbers are determined, will it cause an issue trying to pass a filter with 10k+ order numbers? will the gateway choke? or is there no such limit and no need to worry.

      Kind Regards

      Che

  • Posted on Aug 05, 2020 at 10:48 AM

    Why does " the gateway passes the filter values entered on the frontend directly to the backend CDS."?

    Are you using OData.publish : true?

    If you would create a reference data source project in SEGW you could add a function import to your service.

    Best Regards,

    Andre

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Andre,

      We did originally use OData.publish but as things got more complicated we created a reference in SEGW instead. So currently the gateway fetches the data from the CDS automatically, we don't have to worry about paging or aggregation its all done for you.

      I am not sure I understand what you are suggesting. Are you saying to manually code a function import that will do the data selection? If so will we not loose all the automatic features and have to code them ourselves? Like paging and aggregation?

      Or are you suggestion another way to use function import?

      Thanks

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.