cancel
Showing results for 
Search instead for 
Did you mean: 

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

nk77
Explorer
0 Kudos
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
nk77_0-1709291941790.png

 

 my-service.js
nk77_1-1709291975551.png

EXT_SERVICE.edmx (scrubbed):

nk77_0-1709291752939.pngnk77_1-1709291804419.png
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?
View Entire Topic
Dinu
Contributor

List report can show parameterized views. The annotations required are described in SAP UI5 documentation for Configuring Filter Bars. I have not found a way to annotate the view parameter correctly with the annotation Common.ResultContext in CAP. So I used an additional annotation file in the ui5 application to add this annotation as follows:

 

<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="/browse/$metadata">
        <edmx:Include Namespace="BrowseService"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="parameterize">
            <Annotations Target="BrowseService.BooksParameters">
                <Annotation Term="Common.ResultContext" />
            </Annotations>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

 

All the other restrictions you want can be added using CAP annotations. You need to prefix references to fields with "Set." like in the following snippet.

 

//Capabilities do reflect in metadata; it has to be for Set/
@Capabilities : { 
  FilterRestrictions     : {
    NonFilterableProperties : [Set.currency_code],
    FilterExpressionRestrictions : [
        {
            $Type : 'Capabilities.FilterExpressionRestrictionType',
            AllowedExpressions : 'MultiValue',
            Property : Set.genre_ID,
        },
    ],
  },

  SortRestrictions       : {NonSortableProperties :   [Set.author_ID, Set.currency_code]},
  NavigationRestrictions.RestrictedProperties : [
    {NavigationProperty : Parameters, FilterRestrictions : {Filterable : false} },
    {
      $Type              : 'Capabilities.NavigationPropertyRestriction',
      NavigationProperty : Set,
      FilterRestrictions : {
          $Type                  : 'Capabilities.FilterRestrictionsType',
          FilterExpressionRestrictions : [{
              $Type              : 'Capabilities.FilterExpressionRestrictionType',
              Property           : Set.genre_ID,
              AllowedExpressions : 'MultiValue'
          },
          {
              $Type              : 'Capabilities.FilterExpressionRestrictionType',
              Property           : Set.currency_code,
              AllowedExpressions : 'SingleValue'
          }]
      }
    }
  ]
}

 

Update:

Changed external term "external annotation"  to "additional annotation file" with reference to UI5 documentation.

nk77
Explorer
0 Kudos
Thanks but I am not sure I understand. First, to add annotations.xml so that I can include the "Common.ResultContext" annotation, I created the app once again but instead of "use a local cap service" I used "get metadata", I suppose this is the correct way of using xml annotations file? I also had to configure the service url in several places, so the Fiori application finally started. Unfortunately, there was absolutely no difference w/o the annotation. Also, the request URL did not change a bit. And for the annotations from your last snippet - I still do not understand, even researching a bit. The main goal here is to make the Fiori app call the backend service in the entity(param1='val', param2='val2')/Set... approach, can you perhaps include more details/code snippets if possible?
nk77
Explorer
0 Kudos

In addition, yet the first condition in the UI5 documentation is not met - "sap-filterable=true", all of the parameters in the external entity are set to sap-filterable=false

Dinu
Contributor
0 Kudos
No! I used the wrong terminology. I have updated the answer with the link to documentation for additional annotation file. This will not change the metadata served by the cap service. But, the UI5 app will augment the metadata with additions from this file.
Dinu
Contributor
0 Kudos

If you find any annotation in the imported file is not convenient, you can manually edit the imported file and change it or override these in your projection. I did not directly map the external service to the provided service. Instead I defined a new entitiy with parameters without persistence and took full control of the definition.

nk77
Explorer
0 Kudos
Ok, here is what I did: added the xml annotation file with proper configuration in manifest.json, as a result: input parameters are still not visible in the filter area (as stated in the documentation). Although, when I added them via "UI.SelectionFields", they appreared as mandatory (*) OOTB, which I accept as a sign that the annotation.xml is working properly. As for the next steps, I researched a bit and tried the annotations you left in the snippet above, therefore I am only receiving errors - not 1 successful proper call to the service from the UI. The most common errors I receive now are: "Mandatory filter field 'Set*/Attribute1' not visible on FilterBarBase has no value." (when trying to put an additional attribute - not an input parameter, in the filter bar). It comes together with the pop-up: "Enter a value in all the required fields". Even if I do not do this, sticking to input parameters as filters only - I constantly receive "TypeError: s.storeInnerAppStateAsync is not a function" in the console. Is there any sample project you could share a link to so I can see how it is actually done?
nk77
Explorer
0 Kudos
More details: I am creating the List Report Page from the MyCustomEntity and specify "Set" as navigation during the List Report Page creation. Also, if I do not add any annotations, I receive "Error during request to remote service: Entity 'EXT_PARAMETERIZED_ENTITY': Parameter entity set 'EXT_PARAMETERIZED_ENTITY' cannot be used for read"
Dinu
Contributor
0 Kudos
Please check https://github.com/dinurp/fev4-view-with-parameters (app/books-by-an-author))
nk77
Explorer
0 Kudos
Thanks, it was helpful to see how you created the parameterization with OData v4. Although, in my use-case I should only consume an existing, lets say non-changeable as well, parameterized entity from an external OData v2 service (the edmx declaration of which can be seen above). If I use "as projection on" the external Set entity and Parameters entity, almost the same metadata gets generated in OData v4, which is good. And yes - I can modify the OData of my service with annotations additionally. Unfortunately, I was not able to achieve the end result (although fulfilling the steps in the README.md of the project you provided, thanks for which), and it would be: 1. make the Report List Page app invoke the service in the style of: Entity(param='paramVal', param2='paramVal2')/Set?... 2. invoke the external service in this manner so that actual results could be fetched The errors I see are related to always invoking (in the filter bar) either: the Set entity (where the style with parameters in parenthesis is not achieved) or the Parameters entity (where it says that I cannot read data from it). Perhaps it might be that you are using attributes from other entities as your input parameters and I have 2 entities in the external service - the Set and the Parameters ones. Or I might not be using the correct annotations for binding between the Set and the Parameters entity. And it would also be that I am trying to consume OData v4 service, wrapping it in OData v4, then trying with the OData v4 approach for using parameterizations in the List Report Page?
Dinu
Contributor
0 Kudos

You need to define an entity with parameters in CAP. You can use the types from the import in this definition. But declaring the parameters and results as independent entities did not work in my experiments; there are subtle metadata aspects/relations that are generated on the fly for entities with parameters. 

nk77
Explorer
0 Kudos
Hi Dinu, I got the parameterization working by modifying my service entity a bit - I included the parameters - MyEntity(input_param1, ...) as projection on EXT_ENTITY {//and selected the attributes I need from the set with Set.attr_name}. This produced correct request from the Fiori List Report Page to my service, but during the call to the external service, the FROM clause somehow got lost (the name of the entity and the parameters are omitted in the final request). I was able to trace down to the @Sap/cds/libx/_runtime/remote/Service.js where in the handle(req) the main entity name and the parameters got lost. Do you have any idea for this external service call issue?
Dinu
Contributor
0 Kudos

Unfortunately, the entity in imported service is not imported as an entity with parameters. It is imported as 2 entities one for the parameter and another for the result associated by association Set. So, when you delegate the call to the remote service you have to transform the query. You have listed in your question an example of doing this this. It is just like you see in the oData URL. 

Incoming URL: /Books(in_AuthorID=150,DisplayCurrency='USD')/Set

Incoming CQN: Books(in_AuthorID:150,DisplayCurrency:'USD') as Books is an entity with parameters. 

Expected Outgoing URL: /Books(in_AuthorID=150,DisplayCurrency='USD')/Set

Expected CQN for the above: Books[in_AuthorID=150 and DisplayCurrency='USD'].Set as Books on the remote service is not an entity with parameters 

You have to transform from the incoming CQN to either the expected URL and use REST API or to the corresponding CQN.