Consume CAP service in excel

[WouterLemaire]

Hi CAP experts,

We want to consume a CAP NodeJS service in excel with the option "From odata feed":

I have a demo service with an entity "Dashboard"

When I now try to consume this in excel, it gives me the following message:

When trying to use the v2 proxy, I am able to get to the second view. But this give me another error, not found. If you look carefully, you'll notice that the path is changed. It removes the selected entity from the url:

Looks very similar to this issue: https://github.com/SAP/olingo-jpa-processor-v4/issues/103

Any idea how to solve this?

Thank you in advance!

Kr, Wouter

[christian_d]

Hi, I just followed all the instructions above. And it worked on local. 🙂

But I didn´t manage to deploy successfully to cloud foundry if I changed the start script within package.json from "cds run" to "node srv/index.js".

Any Ideas?

Best regards

index.js:

const express = require("express");
const cds = require("@sap/cds");
const proxy = require("@sap/cds-odata-v2-adapter-proxy");

const host = "0.0.0.0";
const port = process.env.PORT || 4004;

(async () => {
  const app = express();

  await cds.connect.to("db", {
    kind: "sqlite",
    credentials: {
      database: ":memory:",
    },
  });

  const srv = await cds.load("./srv/");
  await cds.deploy(srv);

  // OData V2
  app.use(proxy());

  // OData V4
  await cds.serve("servicename").from("./srv/").at("/").in(app);

  const server = app.listen(port, host, () => console.info(`app is listing at ${host}:${port}`));
  server.on("error", error => console.error(error.stack));
})();

[OliverKlemenz · Accepted solution]

New Patch Version is available:

https://www.npmjs.com/package/@sap/cds-odata-v2-adapter-proxy/v/1.4.59

With this version a connection with Excel shall be possible:

========================

The following needs to be done:

- It only works, if a single OData service is served at root path "/". Then Excel does not cut it...

- Currently "cds run" or "cds serve" cannot be used for that, as per default an "index_html" is served at root path "/"

- An own start file, with custom cds.serve() needs to be created, as described here: https://www.npmjs.com/package/@sap/cds-odata-v2-adapter-proxy#cds-combined-backend-cap-nodejs---cust...

- Instead of cds.serve("all"), only a dedicated service needs to be served at root path like this:

await cds.serve("DemoService").from("./srv/demo").at("/").in(app);

- If this is in place, the OData V2 service is accessible at "localhost:4004/v2/" (OData V4 service at "localhost:4004/")

- Now the service can be almost used in Excel

- There is a small bug in CDS OData V2 Adapter Proxy for $metadata call for root path service, I will fix in next patch release soon. Then the service in principal works.

- There is still some specialty for Edm.DateTimeOffset type. When using CDS DateTime type (e.g. directly or by using managed aspect), Excel expects it to have the format as ISO Date Time, where as UI5/Fiori expects it to have it in /Date(....)/ format.

- As two client demand two different data formats for the same type, there is the possibility to switch the data format from /Date(...)/ to ISO using the new proxy option "isoDateTimeOffset: true". I can also be switched per entity using the entity annotation "@cov2ap.isoDateTimeOffset".

- In that way, the same base entity can be exposed multiple times in same service, having different data format for DateTimeOffset, by annotating the service entities differently...

- Unbound functions/actions also work, in case the return type is an entity type and all parameter values are provided with an non-empty value...

- Bound functions/actions do not work correctly...

[gregorw · Accepted solution]

Hi Oliver,

I've updated bookshop-demo with the 1.4.59 version of the proxy and gave it a try. Unfortunately it didn't work for me when using the V4 endpoint. I get this error message:

My Excel Version is:

When I try the V2 Endpoint at http://localhost:4004/v2/ I get the error message:

'v2' is not an entity set, a singleton, an action import, or a function import

You can replicate this issues via the REST Client script excel.http#L22.

Best regards
Gregor

[OliverKlemenz · Accepted solution]

Hi Gregor,

I checked your bookshop. And binding a service to root path has some specialties 😉

It turns out, that proxy needs to be created before cds serve is called. In that way proxy has priority over CDS services... So that v2 is not looked up as Entity or Function, but acts as prefix to be taken up by CDS OData V2 Adapter Proxy.

I updated your index.js (now also including a SQLite deployment, so that tables are there)... Then it should work... Service is available under "http://localhost:4004/v2/" (trailing slash is important)

const express = require("express");
const cds = require("@sap/cds");
const proxy = require("@sap/cds-odata-v2-adapter-proxy");

const host = "0.0.0.0";
const port = process.env.PORT || 4004;

(async () => {
  const app = express();

  await cds.connect.to("db", {
    kind: "sqlite",
    credentials: {
      database: ":memory:",
    },
  });

  const srv = await cds.load("./srv/");
  await cds.deploy(srv);

  // OData V2
  app.use(proxy());

  // OData V4
  await cds.serve("CatalogService").from("./srv/").at("/").in(app);

  const server = app.listen(port, host, () => console.info(`app is listing at ${host}:${port}`));
  server.on("error", error => console.error(error.stack));
})();

[WouterLemaire · Accepted solution]

Thanks Oliver, great stuff!

I should I expose all my services at root? Or can the others (that are not needed in excel) be exposed as before?

Kr, Wouter

[OliverKlemenz · Accepted solution]

No, basically only the service for Excel needs to be exposed on root level (until we find out, why Excel cuts wait the path). If that is fixed, the service can be again exposed anywhere.

All other services can be exposed/served as before. But to be able to have multiple cds.serve for different services, you need a custom index.js ... Maybe also the @path: '/' annotation can be used to change the path for the excel service to root path..

But as said CDS serve/run did not work for me, as CDS run/serve serves a index_html on root path... (I requested an option switch in CDS to disable that)...

[gregorw · Accepted solution]

Hi Oliver,

thank you for the code correction. I've applied it and now I can see the Entities provided by the Service in Excel. But in the last step of the wizard where the actual data is read I get this error message:

And this errors in the console:

#2.0#2020 12 03 12:39:29:085#+01:00#ERROR#/cov2ap/Authorization#####ki8ro4vx##########ki8ro4vx#PLAIN##Cannot read property 'split' of undefined#
Dec 03, 2020 12:39:29 PM Logger.js [ki8ro4vx] ERROR: Cannot read property 'split' of undefined
TypeError: Cannot read property 'split' of undefined
    at decodeJwtTokenBody (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/@sap/cds-odata-v2-adapter-proxy/lib/index.js:2514:42)
    at /Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/@sap/cds-odata-v2-adapter-proxy/lib/index.js:180:21
    at Layer.handle [as handle_request] (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/layer.js:95:5)
    at trim_prefix (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:317:13)
    at /Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:284:7
    at param (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:354:14)
    at param (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:365:14)
    at Function.process_params (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:410:3)
    at next (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:275:10)
    at createLoggingContext (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/@sap/cds-odata-v2-adapter-proxy/node_modules/@sap/logging/index.js:25:5)
#2.0#2020 12 03 12:39:36:326#+01:00#ERROR#/cov2ap/Authorization#####ki8roah2##########ki8roah2#PLAIN##Cannot read property 'split' of undefined#
Dec 03, 2020 12:39:36 PM Logger.js [ki8roah2] ERROR: Cannot read property 'split' of undefined
TypeError: Cannot read property 'split' of undefined
    at decodeJwtTokenBody (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/@sap/cds-odata-v2-adapter-proxy/lib/index.js:2514:42)
    at /Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/@sap/cds-odata-v2-adapter-proxy/lib/index.js:180:21
    at Layer.handle [as handle_request] (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/layer.js:95:5)
    at trim_prefix (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:317:13)
    at /Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:284:7
    at param (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:354:14)
    at param (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:365:14)
    at Function.process_params (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:410:3)
    at next (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/express/lib/router/index.js:275:10)
    at createLoggingContext (/Users/gwolf/Documents/Projects/cap/bookshop-demo/node_modules/@sap/cds-odata-v2-adapter-proxy/node_modules/@sap/logging/index.js:25:5)

The default service is configured to used mocked authentication. I've also tried with this settings:

But that made no difference.

Best regards
Gregor

[OliverKlemenz · Accepted solution]

That's very strange: How does the authorization header look like?

The code is called, because you have "Bearer", but the token is undefined.

Normally authorization header is "Bearer dasdsklfksdlfksdfl"... But token is undefined, if the authorization header does not have a space...

I could do this more robust, and check for undefined token... But the question is why that is the case?

Can you debug at line 180 and check, what's in req.headers.authorization?

[OliverKlemenz · Accepted solution]

And if this undefined check would be in place, would then the service work?

[OliverKlemenz · Accepted solution]

Also this error is not influencing normal processing.. I checked, and the error is catched and logged (as you have seen), but it does not stop request processing. Something still wrong here..

[gregorw · Accepted solution]

Hi Oliver,

I've replaced the code in node_modules/@sap/cds-odata-v2-adapter-proxy/lib/index.js with:

  function decodeJwtTokenBody(token) {
    if(token) {
      return JSON.parse(decodeBase64(token.split(".")[1]));
    } else {
      return ""
    }
  }

But the error in Excel is still "The content of the data feed is not valid".

Best regards
Gregor

[OliverKlemenz · Accepted solution]

As I expected. I try to reproduce this on my side with your project...

[OliverKlemenz · Accepted solution]

We tested your project (bookshop-demo) and it worked for us, especially the Entity "Authors".

For entity "Books" there is an issue, with type "cds.Date":

will provide with next proxy version a new annotation @cov2ap.isoDateTime, that switches cds.Date representation to ISO format, which is accepted by Excel.

OrderItems does not work, as it is not explicitly exposed in CatalogService (but auto-exposed, so not directly reachable)...

We tested Excel Version 2002 (Build 12527.21330):

[OliverKlemenz · Accepted solution]

Excel Version 2002 (Build 12527.21330)

[WouterLemaire · Accepted solution]

Thank you very much! This works (almost) 🙂

I can confirm that @path annotation works in combination of the custom proxy. In case of multiple services, the one on root level should be loaded as last. Otherwise it will try to find an entity for that service

Regarding the "isoDateTimeOffset", I'm having a similar error for fields of type Edm.Date. My metadata only has normal fields and a few of type Edm.Date. For some reason it tries to map it with Edm.DateTime. The parameter "isoDateTimeOffset" does not solve this problem:

One last point, to make the proxy work local and deployed (using mta) I use an argument to pass the path from the npm script. Local test will use "./srv/" and deployed will use "./".

npm script in root package.json: "start": "node ./srv/index.js ./srv/"

npm script in srv package.json: "start": "node ./index.js ./"

Proxy script:

const express = require("express");
const cds = require("@sap/cds");
const proxy = require("@sap/cds-odata-v2-adapter-proxy");
const args = process.argv.slice(2);
const model = args[0];
const host = "0.0.0.0";
const port = process.env.PORT || 4004;

(async () => {
  const app = express();
  await cds.connect("db");

  const srv = await cds.load(model);
  try{
    await cds.deploy(srv);
  }catch(ex){
    console.log(ex);
  }
  // OData V2
  app.use(proxy({
    isoDateTimeOffset:true
  }));
  // OData V4
  await cds.serve("OtherService").from(model).in(app);
  await cds.serve("RootService").from(model).in(app);
  
  const server = app.listen(port, host, () => console.info(`app is listing at ${host}:${port}`));
  server.on("error", error => console.error(error.stack));
})();

Maybe there is another way for doing this?

You might also noticed the try catch for "cds.deploy". I need to do this because I'm directly connecting to a HANA and it fails to drop tables:

{ Error: insufficient privilege: Detailed info for this error can be found with guid '123'
    at _traced (C:\Users\lemaiwo\scp\srv\node_modules\@sap\cds-runtime\lib\cds-hana\client\Client.js:412:33)
  code: '258',
  message:
   'insufficient privilege: Detailed info for this error can be found with guid \'123\'',
  sqlState: 'HY000',
  query: 'DROP VIEW CatalogService_TABLE' }

Not really a problem to make it work but wanted to make you aware of this.

[WouterLemaire · Accepted solution]

One more error I get when trying to deploy. The app is not able to start the service. My service raises the error that it cannot find local module:

Error: Cannot find local module '../db/db-model'

Before this error, it also raises the following:

UnhandledPromiseRejectionWarning: Error: CDS compilation failed

Any idea what's missing in the proxy to make it work?

[OliverKlemenz · Accepted solution]

Yes, I noted this myself. Next patch will add additional options/annotations to control Date as well... I think a new version will be available on Monday, so that the Edm.DateTime error will be gone using the option isoDate: true... Proxy option is globally, so if you need the date not in ISO format, you can use the annotation @cov2ap.isoDate on excel Service entity only, so that UI5 still works on /Date(...)/ format...for other service entities.

in addition to get the service running deployed follow the section Cloud Foundry deployment from here https://www.npmjs.com/package/@sap/cds-odata-v2-adapter-proxy

MTA shall be based on folder /gen/srv.

With regards to deploy, we use a index.js and a index-local.js both sharing the same code in bootstrap.js, and the local one does the deployment to SQLite... with this setup, code can be reused, but still differences parameterized in bootstrap, or put into the one or the other index(-local).js

Then “npm start” is mapped to index.js, as called by CF, and “npm run dev” is mapped to index-local.js, that can be called locally for development.

[WouterLemaire · Accepted solution]

Works perfect! Thank you big time!

[OliverKlemenz]

I had a look again, and got an CAP OData V2 service working with CDS OData V2 Adapter Proxy.
The following I needed to do:

- It only works, if a single OData service is served at root path "/". Then Excel does not cut it...

- Currently "cds run" or "cds serve" cannot be used for that, as per default an "index_html" is served at root path "/"

- An own start file, with custom cds.serve() needs to be created, as described here: https://www.npmjs.com/package/@sap/cds-odata-v2-adapter-proxy#cds-combined-backend-cap-nodejs---cust...

- Instead of cds.serve("all"), only a dedicated service needs to be served at root path like this:

await cds.serve("DemoService").from("./srv/demo").at("/").in(app);

- If this is in place, the OData V2 service is accessible at "localhost:4004/v2/" (OData V4 service at "localhost:4004/")

- Now the service can be almost used in Excel

- There is a small bug in CDS OData V2 Adapter Proxy for $metadata call for root path service, I will fix in next patch release soon. Then the service in principal works.

- There is still some specialty for Edm.DateTimeOffset type. When using CDS DateTime type (e.g. directly or by using managed aspect), Excel expects it to have the format as ISO Date Time, where as UI5/Fiori expects it to have it in /Date(....)/ format.

- As two client demand two different data formats for the same type, there is the possibility to switch the data format from /Date(...)/ to ISO using the new proxy option "isoDateTimeOffset: true". I can also be switched per entity using the entity annotation "@cov2ap.isoDateTimeOffset".

- In that way, the same base entity can be exposed multiple times in same service, having different data format for DateTimeOffset, by annotating the service entities differently...

- Unbound functions/actions also work, in case the return type is an entity type and all parameter values are provided with an non-empty value...

- Bound functions/actions do not work correctly...

Stay tuned for the next patch release coming soon..., I will post again, when it is available...

[OliverKlemenz]

New patch release https://www.npmjs.com/package/@sap/cds-odata-v2-adapter-proxy/v/1.4.60 is available, introducing new proxy options to switch date formats to ISO format:

• isoTime: Use ISO 8601 format for type cds.Time (Edm.Time). Default is false.
• isoDate: Use ISO 8601 format for type cds.Date (Edm.DateTime). Default is false.
• isoDateTime: Use ISO 8601 format for type cds.DateTime (Edm.DateTimeOffset). Default is false.
• isoTimestamp: Use ISO 8601 format for type cds.Timestamp (Edm.DateTimeOffset). Default is false.
• isoDateTimeOffset: Use ISO 8601 format for type Edm.DateTimeOffset (cds.DateTime, cds.Timestamp). Default is false.

Corresponding entity annotations are available as well:

• @cov2ap.isoTime: Values of type cds.Time (Edm.Time) are represented in ISO 8601 format for annotated entity.
• @cov2ap.isoDate: Values of type cds.Date (Edm.DateTime) are represented in ISO 8601 format for annotated entity.
• @cov2ap.isoDateTime: Values of type cds.DateTime (Edm.DateTimeOffset) are represented in ISO 8601 format for annotated entity.
• @cov2ap.isoTimestamp: Values of type cds.Timestamp (Edm.DateTimeOffset) are represented in ISO 8601 format for annotated entity.
• @cov2ap.isoDateTimeOffset: Values of type Edm.DateTimeOffset (cds.DateTime, cds.Timestamp) are represented in ISO 8601 format for annotated entity.

That should help for Excel import issues.

[gregorw]

Hi Wouter,

I've just tried with my bookshop-demo project. Using OData V4 fails with this error message:

When using the V2 Proxy I get this error message in the backend:

GET /catalog/Books
[2020-11-28T10:17:40.038Z | WARNING | 1315923]: No matching content type found for representation kind 'ENTITY_COLLECTION' and accept 'application/atomsvc+xml,application/atom+xml'
[2020-11-28T10:17:40.039Z | WARNING | 1315923]: Missing format for representation kind 'ENTITY_COLLECTION'
[2020-11-28T10:17:40.039Z | WARNING | 1315923]: No matching content type found for representation kind 'ERROR' and accept 'application/atomsvc+xml,application/atom+xml'

I've created a test request that makes this issue reproducible without Excel:

### Read Books via OData V2 like Excel does it
GET http://localhost:4004/v2/catalog/Books
Accept: application/atomsvc+xml,application/atom+xml

Either oliver.klemenz can help to fix the behaviour of the Proxy or hobruche can give us an Update from the Microsoft side if there are any plans to support OData V4 in Excel.

But still alfter this issues are solved there will be the challenge of Authentication when running the CAP App deployed to SAP Cloud Platform Cloud Foundry. There is only SAML or JWT based authentication possible.

Best regards
Gregor

[WouterLemaire]

Thanks Gregor!

In my version of Excel OData v4 works when I try a demo service: https://services.odata.org/TripPinRESTierService/

But it does not work with my CAP service... looks to me that CAP is not 100% following the OData standards...

Authentication is not a problem in our case. We made that specific service to basic auth in the AppRouter

"authenticationType": "basic"

[gregorw]

Hi Wouter,

what is your deployment target? HANA on Prem or SAP CP? I guess authenticationType = basic will work as long as no Identity Provider is involved.

Best regards
Gregor

[martinstenzig]

There are a few "specialties" that CAP is not supporting yet, see Oliver Heinrich's comment in here: https://answers.sap.com/questions/13188787/consuming-cap-odata-v4-service-through-hana-sdi-od.html You might have a similar situation. Took me a while to figure out why one V4 service works and the other does not.

[WouterLemaire]

Thank you both!

gregorw , HANA on Prem with Identity Provider, tested and works fine!

martin.stenzig3 V4 is not that important to us but V2 is also not working...

[OliverKlemenz]

Don't load and serve "./srv", as it references the db folder, which is not deployed to CF with your app.
Instead prepare a csn.json, that resides in /srv folder, that you can load and serve. See https://www.npmjs.com/package/@sap/cds-odata-v2-adapter-proxy section Cloud Foundry Deployment

[christian_d]

ah, cool i removed it.. and it worked. thx a lot.

[OliverKlemenz]

What you can try, is to use https://www.npmjs.com/package/@sap/approuter#example-routes

And define a simple routing from a single level url to the two level url.
e.g /v2demo -> /v2/demo

Then the Approuter url can be used in excel...

Approuter is anyways always a good idea as single entry point into your application and for authentication challenge.

[WouterLemaire]

I tried this for v4 which solved the first error but in the second step it changed the url again like this:

hostname/demo/entity => hostname/entity

I guess it will give the same result for v2 but I will give it a try anyway. I keep you posted.

Kr, Wouter

[hobru]

I have also asked a few colleagues to look at this (thanks for the detailed analysis done already). As mentioned above Odata v4 is supported with Excel. As soon as I hear back, I will let you know 🙂

Regards, Holger.

[hobru]

Can anyone give me access to one of the Odata services? gregorw can I just clone one of your projects and run it myself to get the service?

Otherweise, can you post the bodies of the responses from the requests to the root and metadata URLs?

Thanks,

Holger.

[gregorw]

Hi hobruche,

I would suggest this service:

https://beershop.azurewebsites.net/beershop
https://beershop.azurewebsites.net/beershop/$metadata
https://beershop.azurewebsites.net/beershop/Beers

It's also reachable via the OData V2 Proxy:

https://beershop.azurewebsites.net/v2/beershop/
https://beershop.azurewebsites.net/v2/beershop/$metadata
https://beershop.azurewebsites.net/v2/beershop/Beers

the repository behind this service is pg-beershop and it is running on Azure using PostgreSQL deployed using Azure DevOps :-).

CU
Gregor

[gregorw]

Hi hobruche, Hi oliver.klemenz,

as a reference this OData Services from the ES5 System which are accessible without authentication can be used:

EPM Products
https://sapes5.sapdevcenter.com/sap/opu/odata/sap/ZPDCDS_SRV/
EPM SalesOrders with Items
https://sapes5.sapdevcenter.com/sap/opu/odata/sap/ZSOCDS_SRV/
Agencies for the flight model
https://sapes5.sapdevcenter.com/sap/opu/odata/sap/ZAGENCYCDS_SRV/

I've tested the EPM Products in my Excel Version and it just works as a charm.

Best regards
Gregor

[hobru]

Hi Gregor,

all these URLs work for me in Excel...

Regards,

Holger.

[OliverKlemenz]

Do you know, if Excel uses the XML format or the JSON format for those URLs (default seems to be XML)? Because there seems to be a problem for JSON format and the type Edm.DateTimeOffset. In CAP OData service only the JSON format is provided, and if the Edm.DateTimeOffset value is in format /Date(...)/, then Excel rejects the value, and if it is in ISO format, Excel accepts it.

I checked the ABAP based service in $format=json, and there also Edm.DateTimeOffset is returned as /Date(...)/, therefore I assume, that XML format is used, where Edm.DateTimeOffset is correctly represented in ISO format.

So I see two Excel issues:

- Excel crops the URL for CAP based service, if path has more than one segment.. (don't know, why this is not an issue in other services), but I see in Node.js express request, that is not the correct one (and the information on the service path is lost, to find the corresponding CAP service).

- Excel does not accept Edm.DateTimeOffset as /Date(....)/ format for JSON based OData service. Strangely SAP UI5 OData Model accepts Edm.DateTimeOffset only in /Date(...)/ format, and rejects the ISO format... So two different clients require the same data type Edm.DateTimeOffset in two different representations for the same service - strange...

But According to OData v2 Specification, I think ISO format is the correct one... But as said SAP UI5 and SAP ABAP based OData v2 service return /Date(...)/ format. So I guess SAP has it's own OData specification for Edm.DateTimeOffset, different from Microsoft's...

From Spec:

https://www.odata.org/documentation/odata-version-2-0/json-format/

Edm.DateTimeOffsetLiteral form of Edm.DateTimeOffset as used in URIs formatted as a JSON string

And the Literal in URI is based on ISO Format... e.g. 2002-10-10T17:00:00Z

https://www.odata.org/documentation/odata-version-2-0/overview/ (Primitive Data Types)

Edm.DateTimeOffset

Represents date and time as an Offset in minutes from GMT, with values ranging from 12:00:00 midnight, January 1, 1753 A.D. through 11:59:59 P.M, December 9999 A.D

datetimeoffset'<dateTimeOffsetLiteral>' dateTimeOffsetLiteral = Defined by the lexical representation for datetime (including timezone offset) at http://www.w3.org/TR/xmlschema-2

Example 1: 2002-10-10T17:00:00Z

[gregorw]

Here I have a OData Endpoint provided from HANA XS Classic XSOData:

https://sitregparticipantlist-a5a504e08.dispatcher.hana.ondemand.com/destinations/HANAMDC/com/sap/sa...

as CAP is the way forward regarding OData Services also in SAP HANA on-premise I would expect a simmilar behaviour. And this service simply works.

[OliverKlemenz]

yes, I was wondering as well... the difference might be, that the other services are more resilient with regards to the url, because I also tested the northwind OData v2, which also has two uri levels, and that worked... unfortunately the CAP service always provides multiple OData services, that are named... and with the addition of v2, we always have two levels. I don‘t see a way to have a single level uri currently with CAP...

I investigate further... would it also be an option to file an issue to Microsoft and challenge them with the OData service and the strange behavior on their side?

[WouterLemaire]

I will check if we can challenge Microsoft. Big thanks already!

[OliverKlemenz]

I debugged the service again, and it's definitely an Excel (resp. MS OData Client Bug).
When service root and metadata call is made, the Url is correct:

e.g. http://localhost:4004/v2/main/

e.g. http://localhost:4004/v2/main/$metadata

That's why the entities are shown correctly in the tree. After selecting an entity, the data call provides a wrong URL:

e.g. http://localhost:4004/v2/Header

The "main" part is missing, but this is important to identify the OData V4 service to be able to call

e.g. http://localhost:4004/main/Header

I have no idea, why this is left out. I see in Node.js backend, that the incoming originalUrl of express request is wrong (without service name), and the error dialog of Excel shows the same... I cannot call the OData V4 service with this wrong URL - no chance...

Debugger in Proxy Backend: url is /v2/Header (missing main service)

[WouterLemaire]

Exactly! But why does it work for the odata.org service? I was thinking that this only has one level in the uri. Could we try to change the proxy to only one level?

Next to that, this was working with an xsodata service. We now migrated to CAP, so it's hard to explain to the customer that our migration is taking us a step back...

[OliverKlemenz]

I tried it myself, and even with enforced accept "application/json", I get the same error as described in the problem by Wouter. It seems Excel is not happy with the OData Service. But I have no glue, why. It all looks correct.

Also I cannot explain, why the service path is removed from URL. But of course this explains the 404. Honestly, this looks a little bit like a Excel bug, that the service path is pruned... That should not happen...

As workaround I tried "Import Data From Web", where a specific OData Entity can be read in JSON format. This did work to some extend, of course with some limitations... But maybe this is an option as well...

[WouterLemaire]

Thanks Oliver. Indeed strange why excel is removing the service path...

Import Data From Web might be a work around for excel but we do have a similar problem with another tool. I was hoping that fixing this for excel would fix this for other tools as well.

[OliverKlemenz]

The difference I think may be, that OData v4 default is JSON. OData v2 default is atom-xml. So accept header may be wrong, as tested by Gregor...

accept = 'application/atomsvc+xml,application/atom+xml'

Maybe it works using url parameter: $format=json, to enforce JSON? Just an idea...

Apart from that, I cannot explain, why the service part „demo“ gets lost, but if this is the case, the 404 is not very surprising...

Is the url in service root xml correct?

http://localhost:4004/v2/demo/

[WouterLemaire]

Tried $format=json but no success. The url in the service root xml is correct.

Can I force the response header in the proxy?

[OliverKlemenz]

CDS OData V2 Adapter Proxy only supports application/json format, as the underlying Node.js OData v4 Service does this only, as well...

So it looks as if excel tries to call with format application/atom-xml, which will not work...

Only way i see currently is, to get Excel to use JSON format...

[WouterLemaire]

Thank you Oliver for your answer.

When I test this with this demo OData v4 service: https://services.odata.org/TripPinRESTierService/ it works fine. This service is also returning format application/json so I would assume that json is not the problem. At least not in my version of Excel...

[gregorw]

The interresting thing with https://services.odata.org/TripPinRESTierService is that it seems that it simply ignores the Accept header. When I send:

GET https://services.odata.org/TripPinRESTierService/Airlines
Accept: application/atomsvc+xml,application/atom+xml

It returns

HTTP/1.1 200 OK
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 397
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true
Content-Encoding: gzip
Expires: -1
Vary: Accept-Encoding
Server: Microsoft-IIS/10.0
OData-Version: 4.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Access-Control-Allow-Origin: *
Date: Mon, 30 Nov 2020 22:00:31 GMT
Connection: close

{
  "@odata.context": "https://services.odata.org/TripPinRESTierService/$metadata#Airlines",
  "value": [
    {
      "@odata.etag": "W/\"J0FtZXJpY2FuIEFpcmxpbmVzJw==\"",
      "AirlineCode": "AA",
      "Name": "American Airlines"
    },
    {
      "@odata.etag": "W/\"J1NoYW5naGFpIEFpcmxpbmUn\"",
      "AirlineCode": "FM",
      "Name": "Shanghai Airline"
    },
    {
      "@odata.etag": "W/\"J0NoaW5hIEVhc3Rlcm4gQWlybGluZXMn\"",
      "AirlineCode": "MU",
      "Name": "China Eastern Airlines"
    }
  ]
}

So I guess if you rewrite the Accept header CAP would also work with Excel.