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

[Swathi_Rani]

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.