I created a BAQ called JEMPackSlips.
I would like to use Excel to run the BAQ.
I was able to get logged onto the Epicor Swagger page and I can execute the BAQ and I see in the response the data from the BAQ. So that part is working.
However, when i try to use MS Excel to connect using OData I get an error message.
The URL i am using is: https:////api/v2/odata/157554/BaqSvc/JEMPackSlips/Data
I have tried V1 also however that is not working either.
Any suggestions greatly appreciated.
DaveO
When I try V1 - again the SWAGGER page results show all the records. however, when try to connect with Excel i get:
You can refer to my post here if you need help with the URL
Instead of doing an Odata connection you can simply select data “From Web”
when power query comes up, you can convert the json to a table by selecting the “list” hyperlink and then the to table button in the toolbar, then select the expand columns button which is where a filter button normally would appear on an excel row. I normally uncheck “RowIdent” and “Use Original Column Name as Prefix” boxes as well
Mr. Camren: Thank you for the tip. That worked from my Excel.
I was able to convert to table and did a close and load and Excel has the data now.
However, (there is always a but)
My ultimate goal is use Excel to get the OData information (on demand) from the Bartender Print station. Bartender is not able to connect directly to SaaS OData (at least that i know of) so my plan was to use Excel as a “fetcher” Bartender would connect to Excel and Excel OData would bring the data into excel (via a Macro).
Using the MPQ - works - however, not sure how I could automate that.
Any ideas?
And again THaNK YOU so much for the help.
DaveO
Make sure to specify your Instance. Check your username/pw.
https://[EpicorServer]/[ERPInstance]/api/v2/odata/[Company]/BaqSvc/[BAQName]/Data?param='2016-12-01'&api-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
For v1 I think you have to use Legacy Wizards in Excel.
EDIT
Saw Camren got to it.
Mr. Haso: Thank you for the suggestion - yes i have the server and the instance - however, when i copied the URL i modified the server and instance and it looks like the EpiUsers interface may have removed the data.
I do have the server and the instance included. i also tried adding the api-key=… at the end however i still struggled to get excel to use the OData.
The suggestion by Camren360 worked. i.e. using the Excel PQE worked for me - however, the OData is still not working.
I am open to any more suggestions - however it is working. i can open excel and the BAQ is automatically updated to the Excel Sheet. Now to see if the Bartender call to open excel will trigger the data RefreshAll - fingers crossed.
DaveO
What happens if you just put the URL into a browser, and log in with basic credentials when prompted? does it return the data? Do you have securities set up like an API Key or Access Scope?
One other thing, I still don’t understand it fully, but when I first approached connecting to Epicor through REST, I remember Odata feeds requiring valid SSL certificates. See this post for more details.
