Skip to Content
Author's profile photo Kanwardeep Singh Gill

Multi-Level Expansion with GET_EXPANDED_ENTITYSET

This blog is based on the idea of expanding Entities linked with Multi-Level Navigation Properties(A->B->C->D) in a single Gate Way  framework call.

To get an idea of expanding multiple entities linked with Navigation property in a single ENTITYSET call with $EXPAND, refer to my recent blog at Expand Parent Child Entities in a Single Expansion – GET_EXPANDED_ENTITYSET.

Consider below entity relation ship ENTITY A -> ENTITY B-> ENTITY C linked with Navigation properties Entity A to Entity B = AB and Entity B to Entity C = BC. With this blog I am trying to fetch data for all the entities A, B and C in a single call of the gateway framework using below URI call where A is linked to B and B is linked to C via a navigation property. This is like an INNERJOIN on the interrelated Entities linked via navigation properties:

/sap/opu/odata/sap/zmultilevel_exp_srv/EntitySetA?$expand=AB/BC,

this would return data for entity A, B and C in a single Gateway Framework Call which would otherwise be achieved with below 2 Odata Calls:


/sap/opu/odata/sap/zmultilevel_exp_srv/EntitySetA?$expand=AB

/sap/opu/odata/sap/zmultilevel_exp_srv/EntitySetB?$expand=BC


In above entity expansion, you could always use filters based on the entities A and B, However, the way you access the various properties of entities A and B is slightly different.


Say you want to filter data result on the basis of property X of entity A, the above link would be represented as /sap/opu/odata/sap/zmultilevel_exp_srv/EntitySetA?$expand=AB/BC&$filter=X eq <Filter Value>.


However, if you want to filter result set based on property Y of entity B, the link would be represented as

/sap/opu/odata/sap/zmultilevel_exp_srv/EntitySetA?$expand=AB/BC&$filter=AB/Y eq <Filter Value>.


We will consider above situations with the help of Sales Order Data linked with entity relations SalesOrder Header -> Sales Order Items ->Sales Order Schedule Lines


Consider below Entity Structures and Navigation Properties:

Entities.PNG

SOHeader.PNG

SOItem.PNG

SO Item Schedule Lines.PNG

 

   Associations.PNG

Navigation.PNG

Item to Schedule Lines.PNG

Redefine GET_EXPANDED_ENTITYSET as explained in the attached snapshot.

Code1.PNG

Code2.PNG

The catch here is that, we have to tell the GW framework that we have expanded the Header, Item and Schedule Line entities in a single entity set call and no need to call the child entity set to fetch the the Sales Order Items and Sales Order Schedule Lines. We can do this using parameter ET_EXPANDED_TECH_CLAUSES of GET_EXPANDED_ENTITYSET. Append both the Navigation Property Name(SOHEADERTOITEM/SOITEMTOSCHDLN) in this exporting parameter, which will tell the gate way framework that the child entity has already been expanded and there is no need to call the child entity sets.

An important point to not here is that the structure of the output entity set should have fields with Name same as Navigation Property Name(SOHEADERTOITEM and SOITEMTOSCHDLN).  Check structure below.

SOHeaderItemsScheduleLines.PNG

Access Scheduel lines data along with Header and Line Items:
https://vmw4462.wdf.sap.corp:44362/sap/opu/odata/sap/ZMULTILEVEL_EXPAND_SRV/SOHeaderSet?$expand=SOHeaderToItem/SOItemToS…

With filter on base entity(SOHeader)

https://vmw4462.wdf.sap.corp:44362/sap/opu/odata/sap/ZMULTILEVEL_EXPAND_SRV/SOHeaderSet?$expand=SOHeaderToItem/SOItemToSchdLn&$filter=Vbeln%20eq%20%271%27&$format=json

FilterOnHeader_Property_Vbeln.PNG

With filter on Items Entity (SOItem): The catch here is that, you have to fetch the filter from the filter string from IO_TECH_REQUEST_CONTEXT->MO_FILTER->MV_FILTER_STRING.

https://vmw4462.wdf.sap.corp:44362/sap/opu/odata/sap/ZMULTILEVEL_EXPAND_SRV/SOHeaderSet?$expand=SOHeaderToItem/SOItemToS…

SOHeaderToItem_Filter_Vbeln.PNG

Kindly share your inputs and thoughts on this.

Assigned Tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sebastian Hellmann
      Sebastian Hellmann

      Hi Kanwardeep,

      thanky you for this explenaition as I have a similar problem to solve.

      The only thing I am confused about is the way you address the third level information

      Can you many post an example of the returned JSON/XML?

      I would have expected to be some kind of nested expand call like

      /SOHeaderSet?$expand=SOHeaderToItem$expand=SOItemToSchdln

      I know that this is syntactically not correct but it just seems not consistent to me.

      I also wonder why you did not have to create an additional custom structure for the ItemToSchdln accociation like
      ZSO_S_IETMTOSCHLDN:

           include type ZSO_S_ITEM,

           SOITEMTOSCHDLN type ZSO_TT_SCHLDN

      How can the framework map this into the data model if it does not have the same structure?

      maybe you can help me to understand this.

      Thank you in advance!

      Author's profile photo Kanwardeep Singh Gill
      Kanwardeep Singh Gill
      Blog Post Author

      Hi Sebastian,

      If you have observed the URL parameter $EXPAND, you must have noticed that the path passed expalins the hierarchy from HEADER to ITEM to SCHEDULELINE with $EXPAND=SOHeadertoItem/SOItemToSchdLn.

      This $EXPAND expression tells the framework to map multi level associated entity sets to the output result from the GET_EXPANDED_ENTITYSET method of the DPC.

      In turn, the framework maps corresponding fields in the output result with the name same as the Navigation Property of the entity sets.

      Capture.PNG

      Author's profile photo Sebastian Hellmann
      Sebastian Hellmann

      Hi Kanwardeep,

      Thank you again for this, I appreciate it!

      I just did a little research and found that nested expands are available at ODATA V4 spec which is not yet implemented in the Gateway

      asp.net web api - Querying nested navigation properties in odata - Stack Overflow

      I am just wondering why

      SOHeaderSet(vblen)?$expand=SOHeadertoItem/SOItemToSchdLn works but SOHeaderSet(vblen)/SOHeadertoItem/SOItemToSchdLn is not allowed.

      I know that the response would be slightly different (no SOHeader information in example 2) but I hope you get my point.

      Anyway that is exactly what I need and I thank you again for your post!

      br

      Sebastian

      Author's profile photo Kanwardeep Singh Gill
      Kanwardeep Singh Gill
      Blog Post Author

      Yeah, I got your point and i understand what you are trying to achieve, but sadly the later espression is not allowed.

      In example 2, as per my understanding, it is possible to have an expression  like this

      SOHeaderSet('1')/SOHeaderToItem =>This will call SOITEM_GET_ENTITYSET of the  entity SOItem which is associated to SOHeader, but the real parent child expansion does not happen here with GET_EXPANDED_ENTITYSET.

      Similarly, you can ahieve SOHeaderSet('1')/SOHeaderToItem(VBELN='1',Posnr='000001')/SOItemToSchdLn => This will call SOITEMSSCHDLN_GET_ENTITYSET method of the entity SOItemsSchdLn which is associated to SOItem entity, which is further associated to SOHeader entity.

      In both the scenarios, what you get is not an expanded result set, however, the result set of the child entity.

      Author's profile photo Sebastian Hellmann
      Sebastian Hellmann

      Hi Kanwardeep,

      I now tried to implement it the way you described, but I don't get any output.

      The only difference in my code is that I created a local structure for the mapping of the expand path:

          data: begin of ls_cfg_chars_values.
                  include type /sie/cl_ad_iset_con_02_mpc=>ts_configuration.
          data: tocharacterisitcs type standard table of /sie/cl_ad_iset_con_02_mpc=>ts_characteristic with default key,
                tovalues          type standard table of /sie/cl_ad_iset_con_02_mpc=>ts_value with default key,
                end of ls_cfg_chars_values.

      my navigation attributes looks as follows:

      2015-06-15 11_50_29-SAP NetWeaver Gateway Service Builder.png

      2015-06-15 11_50_14-SAP NetWeaver Gateway Service Builder.png

      The structure is correctly filled and passed to  er_entity by:

            copy_data_to_ref(
              exporting
              is_data = ls_cfg_chars_values
              changing
              cr_data = er_entity ).

      but still my output shows me now values for both expanded entities.

      Do you have any idea what could be the problem?

      Appreciate you help here!

      Thx Sebastian

      Author's profile photo Simon Perstorfer
      Simon Perstorfer

      Hi Kanwardeep,


      do you know how to convert the filter string to select options?


      I'm working with NetWeaver 7.31 SPS 06 and GW_CORE 200 SPS 06.


      Author's profile photo Kanwardeep Singh Gill
      Kanwardeep Singh Gill
      Blog Post Author

      Hi Simon,

      I achieved it by splitting the filter string.

      i know it is not a clean way to achieve it, however, i could not not find any other option to convert the filter string into SO[].

      Thanks!!

      Author's profile photo Former Member
      Former Member

      All the gateway operations are explained in detailed with screenshots on below page. Hope it can be a help.

      click4interview: SAP Netweaver Gateway Basics

      click4interview: Working Example for SAP Netweaver Gateway Beginners

      Author's profile photo Former Member
      Former Member

      Hi,

      I implemented the above scenario, but I have an issue

      My cr_entity has the multideep value, but in the output, only first entity is visible, the second entity is blank

      Please help

      Author's profile photo subhant banerjee
      subhant banerjee

      Hello Guru ,

      In your blog the link is not completely showing

      https://vmw4462.wdf.sap.corp:44362/sap/opu/odata/sap/ZMULTILEVEL_EXPAND_SRV/SOHeaderSet?$expand=SOHeaderToItem/SOItemToS…https://vmw4462.wdf.sap.corp:44362/sap/opu/odata/sap/ZMULTILEVEL_EXPAND_SRV/SOHeaderSet?$expand=SOHeaderToItem/SOItemToS…

      Actually am facing some issue while using the URI to filter based on navigation just like u showed. Can you please tell me the full URI ?

       

      Regards,

      Subh

      Author's profile photo Former Member
      Former Member

      Hi,

      Can you please tell how to fetch IO_TECH_REQUEST_CONTEXT->MO_FILTER->MV_FILTER_STRING using code based scenario?


      Thanks in advance.

      Parth 🙂

      Author's profile photo Kanwardeep Singh Gill
      Kanwardeep Singh Gill
      Blog Post Author

      You just have to split the filter string at space and then delete unnecessary table rows which contain "(",")","=" etc. Loop over the internal table, first record is the filter name and subsequent is the filter value.

      Author's profile photo Former Member
      Former Member

      Hi Kanwardeep,

      Thanks for you prompt reply. I am trying to fetch the filter value in GET_EXPANDED_ENTITY method. I am not getting my hands on how to reach till MV_FILTER_STRING using object IO_TECH_REQUEST_CONTEXT. In debugger I am able to see but how to code for the same. Please help.

      Best Regards

      Parth

      Author's profile photo Kanwardeep Singh Gill
      Kanwardeep Singh Gill
      Blog Post Author

      You should be able to access MV_FILTER_STRING using IO_TECH_REQUEST_CONTEXT->MO_FILTER->MV_FILTER_STRING

      Author's profile photo Former Member
      Former Member

      I am trying to access as the same you have mentioned, but it is not showing me method MO_FILTER for object IO_TECH_REQUEST_CONTEXT in GET_EXPANDED_ENTITY method.

      Is there anything else that I am missing?

      Regards

      Parth

      Author's profile photo Tobias Spott
      Tobias Spott

      Hi,

      first of all many thanks for your great post. I really liked reading it coz you described it as simple as possible.

      In my case everthing works fine except the filtering ($filter) on a property of an expanded entity (not the root-node). If i try to filter on property Y of entity B, i get an gateway error which says:

      “Linksseitiger Ausdruck des Elementzugriffsoperators mit falsche Kardinalität (zu viele nicht zulässig)”

      It seems like there is something wrong with the cardinalities between the entities, but i set it up like in your example:

      Entity A (principal entity) has cardinality N

      Entity B (dependent entity) has cardinality M

       

      If i filter directly on EntitySet B the filter works correctly.

      Do you know this kind of issue? Do you have any clue what i’m doing wrong?

      Many thanks in advance !

       

      Cheers t.

      Author's profile photo Guru Prasad Yepuri
      Guru Prasad Yepuri

      Hi Tobias,

       

      I am facing the same issue.Could you please share a solution or work around?

       

      Thanks,

      Guru.

      Author's profile photo Tobias Spott
      Tobias Spott

      Hei Guru,

       

      sorry for my late response. I did not get a notification or something that someone was replying on my comment here.

      Unfortunately i did not find a solution for this kind of issue.

      What about you ? Did you find a solution in order to solve this issue?

       

      Tobias