How to exclude multiple values in OData call?

4
votes

I am creating a SAPUI5 application. This application is connected to a backend SAP system via OData. In the SAPUI5 application I use a smart chart control. Out of the box the smart chart lets the user create filters for the underlying data. This works fine - except if you try to use multiple 'not equals' for one property. Is there a way to accomplish this?

I found out that all properties within an 'and_expression' (including nested or_expressions) must have unique name.

The reason why two parameters with the same property don't get parsed into the select options:

/IWCOR/CL_ODATA_EXPR_UTILS=>GET_FILTER_SELECT_OPTIONS takes the expression you pass and parses it into a table of select options.

The select option table returned is of type /IWCOR/IF_ODATA_TYPES=>EDM_SELECT_OPTION_T which is a HASHED TABLE .. WITH UNIQUE KEY property.

From: https://archive.sap.com/discussions/thread/3170195

The problem is that you cannot combine NE terms with OR. Because both parameters after the NE should not be shown in the result set.

So at the end the it_filter_select_options is empty and only the iv_filter_string is filled.

Is there a manual way of facing this problem (evaluation of the iv_filter_string) to handle multiple NE terms?

This would be an example request:

XYZ/SmartChartSet?$filter=(Category%20ne%20%27Smartphone%27%20and%20Category%20ne%20%27Notebook%27)%20and%20Purchaser%20eq%20%27CompanyABC%27%20and%20BuyDate%20eq%20datetime%272018-10-12T02%3a00%3a00%27&$inlinecount=allpages

Normally I want this to exclude items with the category 'Notebook' and 'Smartphone' from my result set that I retrieve from the backend.

2
odatasapui5abap
Can you share the client side piece of code that builds the filter? – Dorad
The separation of the request into the filters (it_filter_select_options) is not modified by me. It is a standard sap gw functionality (methods) - not sure where this is done. Later you can access the filter table inside the DPC EXT methods. – ChristianR
OK. You said "the smart chart let the user create filters". How is this done? – Dorad
link - when using the smart chart, out of the box you have a settings button. If you press it you are able to do some sortings and filter stuff. What is sortable and filterable comes from the metadata. The metadata is created out of the service that was created in the transaction SEGW. – ChristianR
Also, You said "you cannot combine 'NE' terms with 'OR'". The query is: (Category ne 'Smartphone' and Category ne 'Notebook') and Purchaser eq 'CompanyABC' and BuyDate eq datetime'2018-10-12T02:00:00'. Also the variable names you mentioned are confusing. /iwcor/cl_odata_expr_utils=>get_filter_select_options receives iv_expression and returns rt_select_options. I am posting a possible answer now. fix me if i didn't get you. – Dorad

2 Answers

1
votes

If there is a bug inside /iwcor/cl_odata_expr_utils=>get_filter_select_options which makes it unable to treat multiple NE filters of the same component, and you cannot wait for an OSS. I would suggest to wrap it inside a new static method that will make the following logic (if you will be stuck with the ABAP implementation i would try to at least partially implement it when i get time):

  1. Get all instances of <COMPONENT> ne '<VALUE>' inside a () (using REGEX).
  2. Replace each <COMPONENT> with <COMPONENT>_<i> so there will be ( <COMPONENT>_1 ne '<VALUE_1>' and <COMPONENT>_2 ne '<VALUE_2>' and... <COMPONENT>_<n> ne '<VALUE_n>' ).
  3. Call /iwcor/cl_odata_expr_utils=>get_filter_select_options with the modified query.
  4. Modify the rt_select_options result by changing COMPONENT_<i> to <COMPONENT> again.
1
votes

I can't find the source but I recall that multiple "ne" isn't supported. Isn't that the same thing that happens when you do multiple negatives in SE16, some warning is displayed?

I found this extract for Business ByDesign:

Excluding two values using the OR operator (for example: $filter=CACCDOCTYPE ne ‘1000’ or CACCDOCTYPE ne ‘4000’) is not possible.

The workaround I see is to select the Categories you actively want, not the ones you don't in the UI5 app.

I can also confirm that my code snippet I've used a long time for filtering also has the same problem...

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MGW_ABS_DATA->FILTERING
* +-------------------------------------------------------------------------------------------------+
* | [--->] IO_TECH_REQUEST_CONTEXT        TYPE REF TO /IWBEP/IF_MGW_REQ_ENTITYSET
* | [<-->] CR_ENTITYSET                   TYPE REF TO DATA
* | [!CX!] /IWBEP/CX_MGW_BUSI_EXCEPTION
* | [!CX!] /IWBEP/CX_MGW_TECH_EXCEPTION
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD FILTERING.

  FIELD-SYMBOLS <lt_entityset> TYPE STANDARD TABLE.
  ASSIGN cr_entityset->* TO <lt_entityset>.

  CHECK: cr_entityset   IS BOUND,
         <lt_entityset> IS ASSIGNED.

  DATA(lo_filter) = io_tech_request_context->get_filter( ).

  /iwbep/cl_mgw_data_util=>filtering(
    exporting it_select_options = lo_filter->get_filter_select_options( )
    changing  ct_data           = <lt_entityset> ).

ENDMETHOD.