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:
Redefine GET_EXPANDED_ENTITYSET as explained in the attached snapshot.
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.
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)
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.
Kindly share your inputs and thoughts on this.
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!
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.
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
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.
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:
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
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.
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!!
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
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
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
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 🙂
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.
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
You should be able to access MV_FILTER_STRING using IO_TECH_REQUEST_CONTEXT->MO_FILTER->MV_FILTER_STRING
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
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.
Hi Tobias,
I am facing the same issue.Could you please share a solution or work around?
Thanks,
Guru.
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
This has been really useful. A quick question though! I am getting the expand working fine and a filter onto the equivalent of AB as per your example. My question is how do I do a filter on BC as well ? I thought it would just be
/sap/opu/odata/sap/zmultilevel_exp_srv/EntitySetA?$expand=AB/BC&$filter=AB/Y eq <Filter Value> and AB/BC/X eq <Filter Value>.
But I get an error. Oh I should say the error is in CPI as I'm calling this gateway service via there!