How to get the aggregated values of an OData (V2) property?

Question

I want to display a chart (sap.viz.ui5.controls.VizFrame) that visualizes data from an OData Service. However, the data of the service has to be manipulated. See the example below:

Main.view.xml

<mvc:View controllerName="demo.chart.controller.Main" xmlns:mvc="sap.ui.core.mvc" displayBlock="true" xmlns="sap.m">
    <Shell id="shell">
        <App id="app">
            <pages>
                <Page id="page" title="Chart Demo">
                    <content></content>
                </Page>
            </pages>
        </App>
    </Shell>
</mvc:View>

Chart.fragment.xml

<c:FragmentDefinition xmlns:c="sap.ui.core" xmlns:viz="sap.viz.ui5.controls" xmlns:viz.data="sap.viz.ui5.data"
    xmlns:viz.feeds="sap.viz.ui5.controls.common.feeds">
    <viz:VizFrame uiConfig="{applicationSet:'fiori'}" vizType='donut'>
        <viz:dataset>
            <viz.data:FlattenedDataset data="{manipulatedData>/}">
                <viz.data:dimensions>
                    <viz.data:DimensionDefinition name="Gender" value="{manipulatedData>gender}"/>
                </viz.data:dimensions>
                <viz.data:measures>
                    <viz.data:MeasureDefinition name="Amount" value="{manipulatedData>amount}"/>
                </viz.data:measures>
            </viz.data:FlattenedDataset>
        </viz:dataset>
        <viz:feeds>
            <viz.feeds:FeedItem uid="color" type="Dimension" values="Gender"/>
            <viz.feeds:FeedItem uid="size" type="Measure" values="Amount"/>
        </viz:feeds>
    </viz:VizFrame>
</c:FragmentDefinition>

Main.controller.js

sap.ui.define([
    "sap/ui/core/mvc/Controller",
    "sap/ui/model/json/JSONModel",
    "sap/ui/core/Fragment"
], function (Controller, JSONModel, Fragment) {
    "use strict";

    return Controller.extend("demo.chart.controller.Main", {
        onInit: function () {
            const mDefault = this.getOwnerComponent().getModel();
            const mManipulatedData = new JSONModel([{
                gender: "F",
                amount: 0
            }, {
                gender: "M",
                amount: 0
            }, {
                gender: "X",
                amount: 12
            }]);
            this.getView().setModel(mManipulatedData, "manipulatedData");

            console.log(this.getView().getModel("manipulatedData"));

            mDefault.read("/ContactSet", {
                success: oData => {
                    const aManipulatedData = mManipulatedData.getData();

                    oData.results.forEach(entry => {
                        aManipulatedData.forEach(type => {
                            if (entry.Sex === type.gender) {
                                type.amount++
                            }
                        })
                    });

                    Fragment.load({
                        name: "demo.chart.view.Chart",
                        controller: this
                    }).then(oFragment => {
                        this.getView().addDependent(oFragment);
                        this.byId("page").addContent(oFragment);
                    });
                }
            })
        }
    });
});

Structure of the service (`GWSAMPLE_BASIC/ContactSet`)

{
    "d": {
        "results": [
            {
                "__metadata": {
                    "id": "https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/ContactSet(guid'0050568c-901d-1eda-bcae-e8394de7e116')",
                    "uri": "https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/ContactSet(guid'0050568c-901d-1eda-bcae-e8394de7e116')",
                    "type": "GWSAMPLE_BASIC.Contact"
                },
                "Address": {
                    "__metadata": {
                        "type": "GWSAMPLE_BASIC.CT_Address"
                    },
                    "City": "Walldorf",
                    "PostalCode": "69190",
                    "Street": "Robert-Koch-Straße",
                    "Building": "1",
                    "Country": "DE",
                    "AddressType": "02"
                },
                "ContactGuid": "0050568c-901d-1eda-bcae-e8394de7e116",
                "BusinessPartnerID": "0100000000",
                "Title": "",
                "FirstName": "Karl",
                "MiddleName": "",
                "LastName": "Müller",
                "Nickname": "",
                "Initials": "",
                "Sex": "M",
                "PhoneNumber": "0622734567",
                "FaxNumber": "0622734004",
                "EmailAddress": "do.not.reply@sap.com",
                "Language": "EN",
                "DateOfBirth": null,
                "ToBusinessPartner": {
                    "__deferred": {
                        "uri": "https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/ContactSet(guid'0050568c-901d-1eda-bcae-e8394de7e116')/ToBusinessPartner"
                    }
                }
            }
        ]
    }
}

So, as you can see, I'm only interested in the aggregated values of the Sex property. My current solution is to loop through all the entries of the entity set and increase the property in a custom JSON model. This is not only very inperformant because you do the heavy-lifting on the client side, it also requires you to know all the possible values for the data displayed in the chart right away. Is there a way to do this with OData queries or do I have to create a new entity set with the comulated data in my SAP system?

score 0 · Answer 1 · answered Sep 08 '20 at 07:32

0

OData should be able to count and group, as described in this quesiton: OData v4 groupby with $count

But I doubt your SAP OData Service will, most of the time, those services do not implement the full OData specification.

You can improve your js by not using two nested for loops but something like this:

mGender = {};
oData.results.forEach(entry => {
    if (!mGender[entry.Sex])
        {mGender[entry.Sex] = 0
    }
    mGender[entry.Sex]++
});

answered Sep 08 '20 at 07:32

inetphantom

2,498
4
38
61

Thanks for your reply, I'm going to change the js as you suggested. But I don't think OData V2 is capable of groupby, so probably I'll end up implementing another entity set with the aggregated data – mxmlndml Sep 08 '20 at 07:40
I don't think your snippet works because the aggregation binding of the `VizFrame` element expects an array in the model. With your solution the model only consists of objects. – mxmlndml Sep 08 '20 at 07:56
you can still remap the mGender to what you had with `Object.keys(mGender)`. In the end, you will need to decide on what you want to focus on - your decicion may be different if this should be a mobile/offline app or mainly run on a Computer. Your current main bottleneck will probably be the fetching of all that data if you only need that gender percentages. – inetphantom Sep 08 '20 at 13:04
My project is similar to a [Fiori Elements Analytical List Page](https://experience.sap.com/fiori-design-web/analytical-list-page/) but as a custom SAPUI5 app, so I display some visual filters on the upper half and a table with the data of the service on the lower half of the page. So I have to fetch the data either way. Do you think it is faster to convert the data on the frontend or to fetch another entity set with the aggregated data? The app will be desktop-only – mxmlndml Sep 09 '20 at 06:50
@MXMLNDML If you got the data in your model anyways, I would do it client side. If possible/not too much overhead I would try to put it in a custom component, so that it can be reused in other apps. – inetphantom Nov 17 '20 at 08:39

How to get the aggregated values of an OData (V2) property?

Main.view.xml

Chart.fragment.xml

Main.controller.js

Structure of the service (GWSAMPLE_BASIC/ContactSet)

1 Answers1

Structure of the service (`GWSAMPLE_BASIC/ContactSet`)