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. 

Please refer below link:

https://ui5.sap.com/#/api/sap.m.Table

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.

Regards, 

Swathi Rani.