Issues with REST BAQ

It appears that the BAQ Service is not returning results for $metadata so this is causing compatibility problems with Excel and other modern Odata readers. Business Objects do.

Has anybody else seen this?

It returns metadata for one BAQ only, not for all.
Also even for one BAQ there is still incompatibilities with newer Excel versions in v1. See BAQ Odata help

But AFAIK they are addressed in v2.

2 Likes

Olga, I have access to a 10.2.300 and 10.2.400 system. Yet all the help built into those ONLY refers to the V1 api.

Every time I try and use any command shown in the V2 PDF, it returns an Internal Service Error on both servers/environments. It’s as if it’s not installed or activated. I can’t find any reference to it in the web.config file.

Is this V2 in 10.2.300 and/or 10.2.400?

Is there something I have to do to enable it because it doesn’t seem to be on by default.

V2 is in .400 only.
But I know that help page will be later in .400, it is not ready yet.

Ok, so the pdf that documents V2 that says 10.2.300 on the cover page is inaccurate meaning it’s only available with 10.2.400.?

i think so. Report it to support.

If you have access to 10.2.400 – then your Help URL should be pointing to http://help.epicor.com/102400/ in Company Maintenance.

The 10.2.400 Help Files do have the v2 Info in there. In addition to the Print Only Version. Which has its Cover Page updated.

2019-05-10_1639_001

1 Like

So I’ve been trying to make this work with limited success on a client’s 10.2.400 saas environment.

Nowhere in the new help is the exact syntax to retrieve the DATA of a single BAQ

The one exception is on the excel excample here:
Display Service and BAQ Data in Excel®

  1. https://EpicorServer/ERP102400/api/v2/odata/EPIC06/BaqSvc/COM-CustContacts/Data

But this doesn’t work, or it needs to show the example with the api-key which is now a required parameter.

Do you have a URL example that DEFINITELY works with Excel office 365 using the mandated API key feature?

The best I"ve been able to get is it returning metadata in the browser using the $metadata tag.

Either something is wrong in the documentation, the product is broken or I’m doing something wrong (probably the latter :slight_smile: )

Update:
I can get chrome to show the data from my BAQ. The ‘Data’ in the string is CASE SENSITIVE…ugh.

That being said, the same query in excel produces an error “Access to the resource is forbidden.”. I made sure I cleared all permissions just in case and re-entered credentials that work in the browser, but still doesn’t work with excel

Are you passing the API-KEY in the Request Header? I would use Postman first to get your data then move back to Excel.

To use API Key

add header: x-api-key: <keyvalue>

You can generate the API keys in Epicor.

1 Like

You can also pass the API Key in the Query String:

https://EpicorServer/ERP102400/api/v2/odata/EPIC06/Erp.BO.SalesOrderSvc
?api-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk

As to a few examples

https://[EpicorServer]/[ERPInstance]/api/v2/odata/[Company]/BaqSvc/[BaqName]/Da
ta?[baqParam1]=x&[baqParam2]=y&api-key=[Value]

https://EpicorServer/ERP102400/api/v2/odata/EPIC06/BaqSvc/COM-90daysSORel?api
-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk

https://EpicorServer/ERP102400/api/v2/odata/EPIC06/Erp.Bo.CustomerSvc/Customers
?$select=EstDate&$filter=EstDate eq datetime'2016-02-22T00:00:00'&api-key=atI7e
SN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
1 Like

Yes, I’m passing the api key in the query string.

I’m betting if you try (your example)
https://EpicorServer/ERP102400/api/v2/odata/EPIC06/BaqSvc/COM-90daysSORel?api-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
in chrome it will work. But if you try it in Excel it will not.

Any chance you can test?

Whats your exact Error? If you have a self-signed Cert you must add it to the Trusted Root Folder - Excel is very sensitive when it comes to a valid cert and https…

When you visit it via chrome is your SSL Indicator Green or red?

Ok so turns out this is either a ‘bug’ or ‘works as designed’.

You CANNNOT use API-Keys with Excel 2016 and above and the v2 driver.
You must go into your system configuration file and disable the API key requirement…

Except Epicor will NOT let you modify this file if the client is hosted by Epicor, which is the main reason I need to do this in the first place. If they were on prem, I could just connect to their SQL Server and we would be done.

This same Excel incompatibility is also affecting the cData driver I’m using to pull data into a data warehouse tool

One wonders if this was done just to force people onto Epicor’s own EDA solution which they allow an install of an agent that extracts your SQL Data and sends it to the EDA data center.

api

1 Like

Nice Find! Interesting, I dont understand why it wouldn’t support it, it supports other params. Unless OData does something different with its own params like $select= and so on

Your guess is as good as mine but I’ve been pulling my hair out on how to get a decent feed of Epicor hosted customer data into our data warehouse solution and it seems, as of now, it’s literally impossible

Man that sucks. I hope they can change this in the future.

I was always told by Epicor that hosted customers are allowed to replicate their DB for reporting?

I’m not sure what cData uses for their driver but you can build your own PowerQuery Data Connectors. It is currently limited to PowerBI but there is a UserVoice to be able to use them in Excel

Also, I don’t know if XL Connect works with hosted accounts, but if so, their RDL’s have to accessible. You might be able build an RDL using the XLConnect datasource and then pull data using the SSRS OData feeds.

Thanks. Unfortunately my own connector wouldn’t work for my purpose and would be way past my development pay grade anyway.

The XLConnect idea is interesting, but that would mean all customers would need to own XLConnect, which they don’t. And if I can get this working, my end solution would replace XLConnect’s functionality as well as it offers alot more.

Right now my problem is I can’t offer it to anyone hosted by Epicor. They are so darn restrictive with their SaaS hosting that I cringe whenever a new prospect comes our way that is hosted by them because of all the things we can’t do or hoops we have to jump through vs. on-prem or privately hosted customers where we don’t have handcuffs on.