SAPUI5 OData: How to implement Filter, Sort, Expand and Group

We will create a simple SAPUI5 sap.m.List application using Northwind OData service. Next, we apply the Filter, Sort, Expand and Group functionalities to this list.

Begin by creating a new SAPUI5 application by referring the post on

Follow the steps upto Code View1.xml.view. Till here, you will be able to create a SAPUI5 application and attach Northwind OData service to it.

Next, we will be coding the View1.view.xml. Here we will create a simple list and apply the Filter, Sort, Expand and Group functionalities to it.

Code View1.view.xml

The below code uses sap.m.List control to display Northwind OData Service data.

<mvc:View controllerName="Amarmn.znorthwind.controller.View1" 
xmlns:mvc="sap.ui.core.mvc" displayBlock="true" xmlns="sap.m">
<Shell id="shell">
<App id="app">
<pages>
<Page id="page" title="{i18n>title}">
<content>
<List id="productList" class="sapUiResponsiveMargin" 
width="auto" items="{/Products}">
<headerToolbar>
<Toolbar>
<Title text="Products in Stock"/>
</Toolbar>
</headerToolbar>
<items>
<ObjectListItem title="{UnitsInStock} x {ProductName}"
number="{ parts: [{path:'UnitPrice'},'EUR'], 
type: 'sap.ui.model.type.Currency', 
formatOptions: {showMeasure: false} }" numberUnit="EUR">
</ObjectListItem>
</items>
</List>

You have created a sap.m.List application. You can Run the application to see the output.

Using $filter in SAPUI5 Application

You can append $filter to an EntitySet OData call after placing a question mark.

The URL will be something like URL/<EntitySet>?$fitler=<PropertyName> <operator> <value>

We will apply a filter to Northwind Product EntitySet /Products?$filter= UnitPrice gt 20. This will ensure to fetch only those records where UnitPrice is greated than 20.

Add a SearchField to the above SAPUI5 application. When a user enters some value in SearchField and hits enter, the filter will come into action. The filter code will be written in the Controller file.

Change the Toolbar code in View1.view.xml to accommodate the SearchField.

<Toolbar>
<Title text="ProductsinStock"/>
<ToolbarSpacer/>
<Button icon="sap-icon://sort" press="onSortProductName"/>
<SearchField width="50%" search="onFilterProducts"/>
</Toolbar>

Next we will have to code function “onFilterProducts” in the View1.controller.js file. When the user enters some value in the SearchField, an event is triggered holding the “query” parameter. This “query” parameter is then used to create a new sap.ui.model.Filter element. Use the appropriate sap.ui.model.FilterOperator. This filter is used to alter the binding of the product list to fetch records that matches the filter parameter.

The code for View1.controller.js file will be

onFilterProducts: function (oEvent) {
// buildthefilterarray
var aFilter = [];
var sQuery = oEvent.getParameter("query");
if (sQuery) {
	aFilter.push(new Filter(
		"ProductName", FilterOperator.Contains, sQuery));
}
// filterthelistviabinding
var oList = this.getView().byId("productList");
	var oBinding = oList.getBinding("items");
	oBinding.filter(aFilter);
}

For the OData call the $filter parameter gets automatically attached to the URI. The URI will look similar to as shown below.

/Products?$filter=substringof('Chai',ProductName)

Using $orderby in SAPUI5 Application

The $orderby parameter when attached to an OData service will return a sorted list for the collection. The URI with $orderby parameter looks something like below.

/Products?$orderby=ProductName

The default sort is ascending but it is also possible to fetch the descending list. Adding the key ‘desc’ will return a descending list. Ascending list can be fetched by ‘asc’.

The change need to be done in the view.xml file. We set the ‘sorter’ property to ProductName in the view.xml file.

<List id="productList" 
class="sapUiResponsiveMargin" 
width="auto"
items="{ path: '/Products' , 
sorter:{ path: 'ProductName' }
}">

If you want to get a descending list, then sorter will be

sorter: {
path: 'ProductName',
descending: true
}

Run the application to see if you receive a sorted list. When you check the OData call in the Network tab, you will find the $oderby parameter attached.

Using $expand in SAPUI5 Application

Use the $expand parameter to fetch the data of entity as well as its associated navigation in one single request. For example, in Northwind OData service we get the information of product along with its category using the $expand query. For example

/Products(1)?$expand=Category

In the pattern above, $expand can be appended to a collection as well. Its also possible to call multiple navigation properties.

/Products?$expand=Category,Supplier

Implement the below code changes in List for $expand to be effective. Here we add the ‘expand’ parameter to the List control.

<List id="productList" 
class="sapUiResponsiveMargin" 
width="auto"
items="{ path: '/Products' , 
sorter:{ path: 'ProductName' }, 
parameters: { 'expand' : 'Category' }
}">

Implementing Group, Paging and Thresholds in SAPUI5 Application

The $expand helps us get the Category information for the products. Since we have the category information, we can now group the products into category using the ‘group’ parameter.

It makes no sense to call the entire data from the backend as our device can display a limited set of records. Calling a limited set of records also helps speed up the application. We can easily achieve this by using ‘growing’ and ‘growingThreshold’ parameters in our List control.

This will trigger the $top and $skip parameters at OData level.

The all above concept is added to the below code snippet.

<List id="productList" 
class="sapUiResponsiveMargin" 
width="auto"
items="{ path: '/Products' , 
sorter:{ path: 'ProductName' ,group : true}, 
parameters: { 'expand' : 'Category' }}" 
growing="true"
growingThreshold="5">
<headerToolbar>
<Toolbar>
<Title text="ProductsinStock"/>
<ToolbarSpacer/>
<SearchField width="50%" search="onFilterProducts"/>
</Toolbar>
</headerToolbar>
<items>
<ObjectListItem title="{UnitsInStock} x{ProductName}"
number="{ parts: [{path:'UnitPrice'},'EUR'], type: 'sap.ui.model.type.Currency', formatOptions: {showMeasure:false} }" numberUnit="EUR">
<ObjectAttribute text="{Category/CategoryName}"/>
</ObjectListItem>
</items>
</List>

Conclusion: In this example we worked on how to use $filter, $orderby, $expand $top and $skip odata services.