Consume a Parameterized View from External OData v2 service and use it in a List Report Page

[nk77]

Hello Experts,

I am currently struggling with the following use-case:

A List Report Page should be created, displaying data coming from an external OData v2 service (S/4 on-premise system). The difficulty comes from the fact that the entity from the external service is parameterized. What I want to do is to have mandatory filters on the UI for all of the entity input parameters, including some additional filters to the rest of the attributes. The end result should be that the input parameter values should be filled from the List Report Page mandatory filters, and the rest of the filters should be attached in the "$filter" part of the request (also the input parameters should be not included as part of the $select, $orderby, etc. because that would cause an erroneous request).

My current setup:

my-service.cds

 my-service.js

EXT_SERVICE.edmx (scrubbed):

With this setup, I am able to manually pull some service data manually from the browser with this url: https://port4004-basworkspace.applicationstudio.cloud.sap/odata/v4/my-service/MyCustomEntity(input_p... but there is no way to make the List Report Page send the exact URL structure to the external service.

I read that List Report Page does not officially support views with parameters. Although I found out that with CQN I could transform the query with an event handler, like it is explained in:   https://community.sap.com/t5/technology-q-a/how-to-use-parameterized-external-service-programaticall... but unfortunately without success.

Could you please give your thought/help?

[nk77]

@Dinu thanks a lot for the support, I was able to consume the external OData v2 service containing the view with parameters in the following way:

CDS service:

using {EXT_SERVICE as ext} from '../srv/external/EXT_SERVICE';

service MyService {

    @readonly

    entity MyCustomEntity(input_param1: ext.EXT_PARAMETERIZED_ENTITY:input_param1,

                         input_param2: ext.EXT_PARAMETERIZED_ENTITY:input_param2,

                         ) as

        projection on ext.EXT_PARAMETERIZED_ENTITY {

            key Set.Attribute1,

            key Set.Attribute2,

                Set.Attribute3,

                Set.Attribute4

        }

}

CDS service implementation:

const cds = require('@sap/cds');

module.exports = cds.service.impl(async function () {

    const extService = await cds.connect.to('EXT_SERVICE');

    this.on('READ', 'MyCustomEntity', async req => {

        try {

            const params = req.query.SELECT.from.ref[0].args;

            const projection = req.target.projection.from.ref[0].substr(req.target.projection.from.ref[0].lastIndexOf(".") + 1);

            let modifiedFromClause = { ref: [{ id: projection, where: [] }, "Set"] };

            for (let param in params) {

                modifiedFromClause.ref[0].where.push({ ref: [param] }, "=", { val: params[param].val }, ",");

            }

            modifiedFromClause.ref[0].where.splice(-1);

            req.query.SELECT.from = modifiedFromClause;

        } catch (e) {

            console.log(e.message);

            throw new Error("PARAMETERIZED_REQUEST_PROCESSING_EXCEPTION");

        }

        return extService.run(req.query);

    });

});

annotations.cds

using MyService as service from '../../srv/my-service';

annotate service.MyCustomEntity with @(

    Capabilities      : {

        SearchRestrictions: {Searchable: false},

        FilterRestrictions: {

            RequiredProperties          : [Set.Attribute1],

            NonFilterableProperties     : [

                Set.Attribute2,

                Set.Attribute3

            ],

            FilterExpressionRestrictions: [{

                $Type             : 'Capabilities.FilterExpressionRestrictionType',

                AllowedExpressions: 'SingleValue',

                Property          : Set.Attribute2

            }]

        }

    },

    UI.SelectionFields: [Attribute1]

);

annotate service.MyCustomEntity (@title: '{i18n>param1}' input_param1,

                                @title: '{i18n>param2}' input_param2);

// rest of the annotations - line items, etc.

Also - using the parameterize.xml in app/{appname}/webapp/annotations/parameterize.xml (+ adding the file reference to the manifest.yaml) like you had shown, @Dinu 

<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">

    <edmx:Reference Uri="https://sap.github.io/odata-vocabularies/vocabularies/Common.xml">

        <edmx:Include Alias="Common" Namespace="com.sap.vocabularies.Common.v1"/>

    </edmx:Reference>

    <edmx:Reference Uri="/odata/v4/my-service/$metadata">

        <edmx:Include Namespace="MyService"/>

    </edmx:Reference>

    <edmx:DataServices>

        <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="parameterize">

            <Annotations Target="MyService.MyCustomEntityParameters">

                <Annotation Term="Common.ResultContext" />

            </Annotations>

        </Schema>

    </edmx:DataServices>

</edmx:Edmx>

Therefore there are still some issues I could not deal with:

1. The CDS annotation: Capabilities/SearchRestrictions: {Searchable: false} - I expected the annotation applied on MyCustomEntity to have an effect on the main search bar in the header area of the List Report Page - I expected it to hide it, but the search bar remained. Therefore, I included the custom CSS to hide it: 
   div.sapUiAFLayoutItem:has(div.sapMSF) {
       display: none;
   }​ 
   I would for sure take advise if there is a better way to hide the main search bar.
   
   
2. The annotation FilterRestrictions.RequiredProperties [Set.Attribute1] applied to the attributes with the "Set." prefix only puts a red "*" to the corresponding attribute chosen as an additional filter, but the attribute is not actually required - I can omit filling it and still click the Go button without any actual validation (also, the attribute is shown as not-required upon clicking on the "Adapt filters" button). Am I missing any additional annotations here?
   
   
3. I would also want to create an object page to the corresponding List Report Page via including the standard Object Page settings (js file with the Object Page definition, declaring it in manifest.json together with navigation etc.). The end result was that once I click on the specific entry (List report page) to go to its object page, the URL in the browser changes like this: ...MyCustomEntity(input_param1=value1, input_param2=value2)/Set(Attribute1=val3, Attribute2=val4) where the Attribute1 and Attribute2 should be keys, but the page does not load, no logs in the console, terminal, etc.

@Dinu what are your thoughts on the above?

[matthias_dichtl]

You wrote in one of your posts that you receive the error "TypeError: s.storeInnerAppStateAsync is not a function" in the console.
I get this error too and can not find any solution.

Have you fixed this? appstate for filtering is not working in my app. 
My App is deployed on BTP Cloud Foundry not in FLP. Is saving AppState / InnerAppstate possible without FLP?