Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member587340
Discoverer
10,810
We have seen several blogs on how we can easily expose the results of a calculation view as XSOData service.

We can even define navigation properties in the XSOData between the entities. By defining the navigation - we can make use of the existing Smart templates in WebIDE to develop Master Detail kind of UIs.

Here is a screenshot from a blog that explains navigation and association in OData:



Here are some blogs and tutorials on calculation view, exposing views using XSOData and association and navigation between OData entities:

  1. SAP HANA Calculation View

  2. Exposing & Consuming the SAP HANA MDC data using XSOData

  3. Let’s code association/navigation and data provider expand in OData service!


We can find many such blogs as shown above, however in case of complex calculation views that require input parameters defining the association and navigation is a challenge.

Unfortunately, the SAP help documentation and other blogs have not explained this scenario in detail.

In this blog, I will explain how a calculation view that requires input parameters can be linked to other entities and how a navigation property is defined in the XSOData.

We will divide this blog into two steps:

  1. Creating Calculation Views and exposing them using XSOData

  2. Adding association and navigation to OData entities using XSOData


We will take a very simple example consisting of entities Customer, Sales Order Header and Sales Order Items. In the 1st step we will create calculation views for our entities and expose them. So the experts can directly dive to 2nd step where we will add association and navigation for our entities.

Step 1: Creating Calculation Views and exposing them using XSOData




As we can see from the above image, our entities have the following attributes:

Customer

  • ID

  • CustomerName

  • CustomerLocation

  • Count


Sales Order Header




  • ID

  • SalesOrderDate

  • CustomerID

  • CustomerName

  • CustomerLocation

  • NetAmount


Sales Order Item

  • ID

  • SalesOrderItemHeader

  • ItemName

  • ItemPrice

  • ItemQuantity


I'll share the screenshots of the calculation views and XSOData code below, for more information you can follow SAP HANA developer's guide

Customer Calculation View



The above calculation view CustomerView is exposed as Customer in XSOData as follows:
service{
"demo.blog1::CustomerView" as "Customer"
keys("ID");
}

To access Customer entity, the URL for my OData entity Customer will be as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer



https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(1)



Sales Order Header Calculation View



Note: We have defined input parameters START_TS and END_TS to output SalesOrderHeaders which have SalesOrderDate between START_TS and END_TS.

As we have input parameters we will define XSOData such that parameter values are passed in the URL for our entity along with the key. Therefore the calculation view SalesOrderHeaderView is exposed as SalesOrderHeader in XSOData as follows:
service{
"demo.blog1::SalesOrderHeaderView" as "SalesOrderHeader"
keys("ID")
parameters via key and entity;
}

Note: If you face error such as Unsupported Parameter while activating the above service, you can refer this discussion.

To access SalesOrderHeader entity, the URL for my OData entity SalesOrderHeader will be as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/SalesOrderHeaderParam...



https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/SalesOrderHeader(STAR...



Sales Order Item Calculation View



The above calculation view SalesOrderItemView is exposed as SalesOrderItem in XSOData as follows:
service{
"demo.blog1::SalesOrderItemView" as "SalesOrderItem"
keys("ID");
}

To access SalesOrderItem entity, the URL for my OData entity SalesOrderItem will be as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/SalesOrderItem



https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/SalesOrderItem(1)



The above services are consolidated into a single XSOData file. Here is the code snippet for the consolidated XSOData:
service{
"demo.blog1::CustomerView" as "Customer"
keys("ID");

"demo.blog1::SalesOrderHeaderView" as "SalesOrderHeader"
keys("ID")
parameters via key and entity;

"demo.blog1::SalesOrderItemView" as "SalesOrderItem"
keys("ID");
}

annotations {
enable OData4SAP;
}

Step 2: Adding association and navigation to OData entities using XSOData


In the previous step we created Customer, Sales Order Header and Sales Order Item calculation views and exposed them using XSOData. Now we will add association between the entities and define navigation between them.

If you have followed from Step 1, you will notice that calculation view Customer has no input parameters and Sales Order Header has 2 input parameters START_TS and END_TS.

In order to navigate from Customer to SalesOrderHeader entity in XSOData it requires START_TS and END_TS input parameters to be passed from Customer to SalesOrderHeader entity. Hence we will add a dummy START_TS and END_TS input parameter to Customer calculation view which will have only one purpose of passing data from Customer to SalesOrderHeader entity while navigation.

This is how our entities will look after association and navigation is added:

The change required in Customer Calculation View is marked in red in below image:



To add association and navigation we will make changes to our XSOData file. The changes made are highlighted using different colored rectangles and the explanation for the highlighted changes are given below:



  1. The change reflected in yellow rectangle is because of the changes made to Customer calculation view. It will enable Customer entity to accept START_TS and END_TS as input parameter along with its key.

  2. In red rectangle we can see the association defined between the entities Customer and SalesOrderHeader. Note that we have written input parameters before the key in principal and dependent declaration for the corresponding association. Input parameters should always be written before the keys.

  3. In green rectangle we can see the association defined between the entities SalesOrderHeader and SalesOrderItem.

  4. Lastly we have blue rectangle where we update our entities to navigate to the corresponding associations.


This is how we define association and navigation between OData entities using XSOData. Here is the code snippet of the XSOData file after adding association and navigation:
service{
"demo.blog1::CustomerView" as "Customer"
keys("ID")
navigates ("CustomerSalesOrdersAssociation" as "CustomerSalesOrders")
parameters via key and entity;

"demo.blog1::SalesOrderHeaderView" as "SalesOrderHeader"
keys("ID")
navigates ("SalesOrderHeaderItemsAssociation" as "SalesOrderHeaderItems")
parameters via key and entity;

"demo.blog1::SalesOrderItemView" as "SalesOrderItem"
keys("ID");

association via parameters "CustomerSalesOrdersAssociation"
principal "Customer"("START_TS","END_TS","ID") multiplicity "1"
dependent "SalesOrderHeader"("START_TS","END_TS","CustomerID") multiplicity "*";

association via parameters "SalesOrderHeaderItemsAssociation"
principal "SalesOrderHeader"("ID") multiplicity "1"
dependent "SalesOrderItem"("SalesOrderItemHeader") multiplicity "*";
}

annotations {
enable OData4SAP;
}

Now lets see how to access the OData entities and their navigation properties through URL.

To access Customer when navigation not required, we can pass some dummy timestamp as START_TS and END_TS since these parameters don't affect Customer Calculation View and are used only for passing it to Sales Order Header Calculation View.

Here are some examples:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/CustomerParameters(ST...



https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(START_TS=dat...



In cases where we require navigation from Customer to SalesOrderHeader we will pass valid timestamp as START_TS and END_TS input parameters to fetch sales orders for customers between the given time range.

Here are some examples:

1.To get customer with ID=1, the URL will be as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(START_TS=dat...




2. To get all the Sales Orders for the Customer with ID=1 between the given time range, the URL is as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(START_TS=dat...



3. To get the sales order with ID=2 from the previous result, the URL is as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(START_TS=dat...



4.To get all sales order items belonging to the Sales Order from the previous result, the URL is as follows:

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(START_TS=dat...



Hope this blog helped you. Please feel free to give any sort of feedback and comment.

Happy Learning.
2 Comments