SAP HANA Tutorial, Material and Certification Guide

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.

SAP HANA Certification, SAP HANA Guides, SAP HANA Tutorial and Materials

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.

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/SalesOrderHeaderParameters(START_TS=datetime’2015-07-24T00:00:00.0000000′,END_TS=datetime’2018-12-22T23:59:59.0000000′)/Results

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/SalesOrderHeader(START_TS=datetime’2015-07-24T00:00:00.0000000′,END_TS=datetime’2018-10-22T23:59:59.0000000′,ID=1)

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(START_TS=datetime”,END_TS=datetime”)/Results

https://blogdemoi347902sapdev.int.sap.hana.ondemand.com/demo/blog1/api.xsodata/Customer(START_TS=datetime”,END_TS=datetime”,ID=1)

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=datetime’2015-07-24T00:00:00.0000000′,END_TS=datetime’2018-11-22T23:59:59.0000000′,ID=1)

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

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=datetime’2015-07-24T00:00:00.0000000′,END_TS=datetime’2018-11-22T23:59:59.0000000′,ID=1)/CustomerSalesOrders(START_TS=datetime’2015-07-24T00:00:00.0000000′,END_TS=datetime’2018-11-22T23:59:59.0000000′,ID=2)