SAP Business Application Studio, SAP Fiori, SAPUI5

Total and Sub-Total of a Table Using Northwind Service in BAS-(Business Application Studio)

Objective: This blog post provides insights about the Total and Sub-Total of a Numerical Units in a Table using Northwind Service and how to implement in SAP Business Application Studio environment.

In this blog post, I’ll demo about Total and Sub-Total of a Numerical Units in a Table using Northwind Service.

Northwind Service: Northwind Service is an OData Sample service and consumed in SAP UI5 and Fiori Applications.

Responsive Table: The responsive table automatically adjusts its appearance and behaviour based on the available screen size, making it suitable for both desktop and mobile applications.

Use Case: The Total and Sub-total Concept in a table is commonly used in scenarios where you need to display aggregated values for columns like Amount/Prices based on certain criteria like Quantity,Unit ect. For this case i came up with this solution.

In this scenario, we’ll assume a simple sales order table with columns like Product, Quantity, Unit and Price.

So, let’s get started,

Step 1: Creating an Application in Projects Folder

  • Click on File, “New Project from Template” option.

  • Select “SAP Fiori application” Template and click on Start Button.

  • Select “SAP Fiori Worklist Application” Template and click on Next Button.

  • Select: In Data source- “Connect to System”.

In System- “Northwind” Destination.

Give Service Path of Northwind Service- “/V2/Northwind/Northwind.svc/”

  • In Entity Selection,

Select: Object Collection- Products Entity Set.

Object Collection Key- ProductID Property.

Object ID- CategoryID Property.

Ater giving all the required details click on the Next Button.

  • In Project Attributes give required details and click on Finish Button.

  • Application Created Successfully.

Step 2: Developing Application

Worklist XML View:

  • Using Semantic Page Control instead of Native Page.
  • The SemanticPage control helps in creating a unified page structure for Fiori applications. It includes header content, content area, and a footer, providing a standardized layout for applications.
  • Give Semantic Page Name Space Library- xmlns:semantic=”sap.m.semantic”.
  • Implementing Sematic Page as shown in below image:

  • Added Toolbar Control in Semantic Page and added Three Buttons in it.
  • Buttons are Total Button, Sub-Total Button and Refresh Button.

Output Screen:

  • Output showing Toolbar with Three Buttons in Semantic Page.

  • Now let’s add Responsive Table Control for Displaying Products Data.

  • Added footer in column for displaying the Subtotal for the price.

XML CODE SNIPPET:

<mvc:View controllerName="com.total.samtotal.controller.Worklist"
    xmlns="sap.m"
    xmlns:mvc="sap.ui.core.mvc"
    xmlns:semantic="sap.m.semantic"
    xmlns:core="sap.ui.core">
    <semantic:SemanticPage class="sapUiSizeCompact" id="page" title="{i18n>worklistTitle}" showFooter="false" navButtonPress="onNavBack" showNavButton="false">
        <semantic:content>
            <Toolbar>
                <ToolbarSpacer />
                <Button icon="sap-icon://sum" press="SumList" type="Accept" tooltip="{i18n>Total}"></Button>
                <Button id="subTotBtn" icon="sap-icon://add-process" press="openSubTotalFunc" type="Accept" tooltip="{i18n>SubTotal}"></Button>
                <Button icon="sap-icon://refresh" press="onTableRefresh" type="Emphasized"></Button>
            </Toolbar>
            <Table id="tableSum" width="auto" items="{path: 'ProductModel>/',sorter: {path: 'SupplierID',descending: false}}" noDataText="{i18n>tableNoDataText}" growing="true" growingThreshold="100" growingScrollToLoad="true">
                <columns>
                    <Column width="5%">
                        <Label text="ID" design="Bold"></Label>
                    </Column>
                    <Column >
                        <Label text="Product Name" design="Bold"></Label>
                    </Column>
                    <Column >
                        <Label text="CategoryID" design="Bold"></Label>
                    </Column>
                    <Column >
                        <Label text="Unit" design="Bold"></Label>
                        <footer>
                            <VBox items="{STableModel>/QTYUnit}">
                                <Text visible="true" text="{STableModel>}" />
                            </VBox>
                        </footer>
                    </Column>
                    <Column >
                        <Label text="Price" design="Bold"></Label>
                        <footer>
                            <VBox id="UnitPriceSumId" visible="false" items="{STableModel>/UnitPricetotalVal}">
                                <Text text="{path:'STableModel>'}" />
                            </VBox>
                        </footer>
                    </Column>
                    <Column >
                        <Label text="Discontinued" design="Bold"></Label>
                    </Column>
                </columns>
                <items>
                    <ColumnListItem >
                        <customData>
                            <core:CustomData key="mydata" value="{ProductModel>Flag}" writeToDom="true"></core:CustomData>
                        </customData>
                        <cells>
                            <Text text="{ProductModel>ProductID}"></Text>
                            <Text text="{ProductModel>ProductName}"></Text>
                            <Text text="{ProductModel>CategoryID}"></Text>
                            <Text text="{ProductModel>QuantityPerUnit}"></Text>
                            <Text text="{ProductModel>UnitPrice}"></Text>
                            <ObjectStatus text="{ProductModel>Discontinued}" state="{path: 'ProductModel>Discontinued',formatter: '.formatter.status'}" />
                        </cells>
                    </ColumnListItem>
                </items>
            </Table>
        </semantic:content>
    </semantic:SemanticPage>
</mvc:View>

Getting Northwind Serivce Data:

  • In Life Cycle Method- onInit() Function just give the Read Call for Products Entity of Northwind Service.
  • Read Call for Products Entity Set from Northwind service.
  • In Read Call we just Split the QuantityPerUnit Property.
  • We just show QuantityPerUnit property Units without Value in the column.

Controller Code:

sap.ui.define([
    "./BaseController",
    "sap/ui/model/json/JSONModel",
    "../model/formatter",
    "sap/ui/model/Filter",
    "sap/ui/model/FilterOperator",
    "sap/ui/model/Sorter"
], function (BaseController, JSONModel, formatter, Filter, FilterOperator, Sorter) {
    "use strict";

    return BaseController.extend("com.total.samtotal.controller.Worklist", {

        formatter: formatter,

        /* =========================================================== */
        /* lifecycle methods                                           */
        /* =========================================================== */

        /**
         * Called when the worklist controller is instantiated.
         * @public
         */
        onInit: function () {
            var oViewModel, that = this;
            // Model used to manipulate control states
            oViewModel = new JSONModel({
                worklistTableTitle: this.getResourceBundle().getText("worklistTableTitle")
            });
            this.setModel(oViewModel, "worklistView");
            that.onGetService();
        },
        onGetService: function () {
            var that = this;
            var TableModel = new JSONModel();
            that.getView().setModel(TableModel, "STableModel");

            //Read Call
            var oDataHeader = this.getOwnerComponent().getModel();
            oDataHeader.read("/Products", {
                success: function (oData, res) {
                    var sArray = []
                    var sData = oData.results;
                    for (var i = 0; i < oData.results.length; i++) {

                        var oQtySplit = sData[i].QuantityPerUnit.split(/(\d+)/);
                        var oQty = oQtySplit[oQtySplit.length - 1].toUpperCase();

                        var sObj = {};
                        sObj.CategoryID = sData[i].CategoryID;
                        sObj.Discontinued = sData[i].Discontinued;
                        sObj.ProductID = sData[i].ProductID;
                        sObj.ProductName = sData[i].ProductName;
                        sObj.QuantityPerUnit = oQty;
                        sObj.ReorderLevel = sData[i].ReorderLevel;
                        sObj.SupplierID = sData[i].SupplierID;
                        sObj.UnitPrice = parseFloat(sData[i].UnitPrice).toFixed(2);
                        sObj.UnitsInStock = sData[i].UnitsInStock;
                        sObj.UnitsOnOrder = sData[i].UnitsOnOrder;
                        sArray.push(sObj);
                    }
                    var ProductModel = new JSONModel(sArray);
                    ProductModel.setSizeLimit(sArray.length);
                    that.getView().setModel(ProductModel, "ProductModel");
                }
            });
        },


    });
});

Output Screen:

  • Here we can see only Units in Units Column without value and price.

Total Button Logic:

  • Now can see Code of Total Sum for the Unit and Price Columns in Table.
  • Here “SumList” Function is an event of Total Button in Toolbar of XML view.

Controller Code:

//**************************** Sum List Fields Dialog *****************************//
        SumList: function () {
            var that = this;

            var oPricePath = "UnitPrice";
            var oUnit = "QuantityPerUnit";
            var oData = [];
            var oTable = that.byId("tableSum");
            var uniqueQuantityUnit = [];
            oTable.getItems().filter(function (item) {
                var oPath = item.getBindingContextPath();
                var oContext = oTable.getModel("ProductModel").getProperty(oPath);
                oData.push(oContext);
                if (uniqueQuantityUnit.indexOf(oContext[oUnit]) === -1) {
                    uniqueQuantityUnit.push(oContext[oUnit]);
                }
            });
            that.getView().getModel("STableModel").setProperty("/QTYUnit", uniqueQuantityUnit);
            var oTotalArray = [];
            for (var i = 0; i < uniqueQuantityUnit.length; i++) {
                var sArray = that.onTotalUnitQtyData(uniqueQuantityUnit[i], oData, oPricePath, oUnit);
                oTotalArray.push(sArray);
            }
            that.byId(oPricePath + "SumId").setVisible(true);
            that.getView().getModel("STableModel").setProperty("/" + oPricePath + "totalVal", oTotalArray);
            that.getView().getModel("STableModel").setProperty("/sTotalPath", oPricePath);
            var sumMsg = "Sum Calculated";
            sap.m.MessageToast.show(sumMsg);
        },
        onTotalUnitQtyData: function (unitQty, oData, oPricePath, unit) {
            var oTotal = 0;
            for (var i = 0; i < oData.length; i++) {
                if (unitQty === oData[i][unit]) {
                    var stData = oData[i][oPricePath];
                    var oValue = parseFloat(stData);
                    oTotal += oValue;
                }
            }
            var oMainTotal = parseFloat(oTotal).toFixed(2);
            return oMainTotal;
        },

Output Screen:

  • Click on The Total Button to see the Total of Unit &Price in Custom footer of Table.

Sub-Total Button Logic:

  • Now can see Code of Sub-Total Sum for the Unit and Price Columns in Table.
  • Here “openSubTotalFun” Function is an event of Sub-Total Button in Toolbar of XML view.

Controller Code:

//**************************** Sum Sub Total List Fields Dialog *****************************//
        openSubTotalFunc: function () {
            var that = this;
            // CategoryID is field with which we do subtotal functionality in table
            var fieldName = "CategoryID";
            var oPricePath = "UnitPrice";
            var oView = that.getView();
            var oTable = oView.byId("tableSum");
            var unique = [], oData = [];
            var qty = 0;
            oTable.getItems().filter(function (item) {
                var oPath = item.getBindingContextPath();
                var oContext = oTable.getModel("ProductModel").getProperty(oPath);
                oData.push(oContext);
                if (unique.indexOf(oContext[fieldName]) === -1) {
                    unique.push(oContext[fieldName]);
                }
            });
            unique.forEach(function (uniqVal) {
                var sObj = {};
                for (var i = 0; i < oTable.getItems().length; i++) {
                    var sPath = oTable.getItems()[i].getBindingContextPath();
                    var sContext = oTable.getModel("ProductModel").getProperty(sPath);
                    if (uniqVal === sContext[fieldName]) {
                        qty = that.onTotalUnitQtyData(uniqVal, oData, oPricePath, fieldName);
                        sObj[oPricePath] = qty;
                    }
                }
                sObj[fieldName] = uniqVal;
                if (sObj[fieldName] === uniqVal) {
                    sObj.Flag = "LightOrange";
                }
                oData.splice(oData.length, 0, sObj);
                oTable.getModel("ProductModel").setData(oData);
                oTable.getModel("ProductModel").setSizeLimit(oData.length);
                oTable.getModel("ProductModel").refresh();
                oTable.getBinding("items").sort(new Sorter(fieldName, false));
                qty = 0;
            });
        }

CSS Code for Table row Colour:

tr[data-mydata="LightOrange"]{
    background: #FFD898 !important;
    }
tr[data-mydata="None"]{
    background: #f7f7f7 !important;
}

Output Screen:

  • Click on the Sub-Total Button for Displaying Sub-Totals of Price in Custom Footer of Table.

Refresh Button Logic:

  • Click on the Refresh Button to Refresh/Clear Totals and Sub-Total in Table.

Controller Code:

/*** Event handler for refresh event/ Refreshing Table */
        onTableRefresh: function () {
            var that = this;
            that.onGetService();
        },

Output Screen:

Conclusion:

We had seen how to do Total and Sub-Total of Units with Values in a Table using Northwind Service.

NOTE: Displaying Table data using Northwind Service.