Finally figured out how to tie live BAQ's to Excel!

[twinfeatherz (Austan Meyer)]

Working much like a power query, we have been able to get BAQ’s live refreshing into excel sheets that we set up. It took me a few hours to figure out, but it is going to be tremendously helpful prior to our Go-Live. I am sure many of you know how this is done already, but I wanted to make a post explaining the process for those of you who may not know how. For those of you who do frequently do this, any extra tips and tricks you know will be greatly appreciated!

For us here, we will not be going live with Epicor until the start of January. So we are intending on using these to set up validation documents, pulling both from our existing system and Epicor, which will be a great help for data validation efforts. It’ll also allow us to maintain other reports that we will be using.

Step One: Create the BAQ that you would like to have translated over into an excel document.

Step Two: Head over to your Rest API server, which should look something like https://yourcompany-yourenvironment.epicorsaas.com/server/apps/resthelp/

Step Three: On the left hand menu, select “Business Activity Queries”

Step Four: Once inside the BAQ screen, on the left hand side, there are two drop drowns you will want to pay attention to. First thing is to select the BAQ that you would like to have populated in Excel, in the middle box. The second thing you need to pay attention to is the API version, which should be v1. (Note: I have tried to make v2 work, but it hasn’t quite worked out for me yet.)

Step Five: After those options are selected, the next step is to create a link you can use to pull data into excel. You will do this by selecting the “Get” button next to the command “Execute BAQ”

Then you will select “Try it Out” below that-

Then you click “Execute” below that-

You will then have a link that pops up below that under responses, with one being labeled “Request URL” This is the link that you need to have for Excel to pull the data. (Sorry, no screenshots of my url here)

Step Six: Copy that URL somewhere where you can get back to it again later. I have an Excel file called “BAQ Links” that I use to maintain links for all of my BAQ’s. A link has to be created once for each environment.

Step Seven: In Excel, go to your data tab. Then go to Get Data-> From Other Sources-> From OData Feed.

Step Eight: You will then be prompted to paste your link we received earlier from the REST API. Paste it here.

Step 9: I already did this step, so I am not getting the screens to pop up, but you will need to authenticate your Epicor login in Excel. Depending on how you configured your environment, this sign in may be Azure, Basic, or Identity provider.

Step 10: Load your data! You have now successfully linked your Excel sheet to your BAQ. You can use the Refresh button to live refresh your BAQ with data from Epicor.

Hope this is as helpful to others as it was to me!

[dr_dan (Dan Ramirez)]

Way to go! I, too, struggled with this for awhile before finally getting it all sorted out. My issue was mostly because we were on premise and did not have an SSL installed or something which would not let me use Rest services.

Anyway, if you want to get v2 to work, I can help you with that. I’m not sure why you’d want to do v2 instead of v1 - my guess is it’s just more secure because you need both an API Key and the user credentials. You would use the From Web instead of OData connection in Excel.

[twinfeatherz (Austan Meyer)]

Thank you! I am not sure what the benefits are of v2 vs v1 myself.

[Banderson (Brandon Anderson)]

V2 requires an API key, which is passed by passing headers. Excel doesn’t support this, so (I believe) using v2 with excel isn’t possible. Now, I could be wrong about that, but I think that’s the case.

[klincecum (Kevin Lincecum)]

You can do it. I have it somewhere. It’s a pain.

[CSmith (Clint Smith)]

It is

[timshuwy (Tim Shoemaker)]

This is an awesome example of “users helping users”. Thank you for publishing.

[twinfeatherz (Austan Meyer)]

Do we have a category set up just for guides like this? I am sure that it would be appreciated! I can think of at least a dozen topics that would fit well there, especially with users like me who haven’t even gone live quite yet.

[klincecum (Kevin Lincecum)]

@twinfeatherz

Do we have a category set up just for guides like this?

We have the Experts Corner.

What’s the consensus peeps? Is this post detailed enough to warrant a move to there?

[CSmith (Clint Smith)]

I think I have sample file somewhere based on this including v2 access:
Excel Parameter To Filter Epicor BAQ - Epicor ERP 10 - Epicor User Help Forum

I will get the v2 function I made to pass the header auth params. Just gotta find it

[twinfeatherz (Austan Meyer)]

I can’t believe I never found this sooner. Thank you so much! I know the rest of my day is going to be sniffing through this area. So helpful man!

[CSmith (Clint Smith)]

p function used below:

let
    Source = (paramName) => (Excel.CurrentWorkbook(){[Name=paramName]}[Content]{0}[Column1])
in
    Source

API v2 OData request

let
    Headers = [
          #"Authorization"="Basic " & p("Base64Auth")
        , #"X-API-Key"=p("x_api_key")
        , #"Content-Type"="text/plain"
        ],
    paramQuery="BAQ_Name",
    Source = OData.Feed(p("Uri_Protocol") & "://" & p("Server_FQDN") & "/" & p("Epicor_Instance") & "/" & "api/v2/odata/" & p("Company") & "/" & p("ServiceName") & "/" & paramQuery & "/Data?" , Headers, [Implementation="2.0"])
in
    Source

This used a named cell and pre encoded (base64) user:pw in that cell this was a TEST and does work, like I said below I was going to create a userform to take entry of epicor login info but … time is not my friend lately

[CSmith (Clint Smith)]

This is a QUICK example I had planned to make a userform to allow entry of Epicor credentials but… Ya not yet.

[antonyjim (Anthony Jund)]

I only use V1 and have no issue using the built in Excel authentication window. I have several BAQ connected spreadsheets that are shared in the organization, and each user will enter their own credentials to refresh the data in this window:

[CSmith (Clint Smith)]

I have many v1 spreadsheets and a v2 test case one.

Currently attempting to find a way to provide SSO or temporary auth based on either a temporary token or memory only auth param for the v2 OData connection. I only provided the v2 info as I had it, but I have not worked out all the items to better secure any data Excel might hold beyond its existence in memory.

[Hally (Simon Hall)]

For V2 we ended up using a macro, which is a bit daft, all of a sudden all secrets are in the macro…I am sure you could probably go the next step and use a key vault and then use token auth…peeeerhaps…

A total sledge hammer approach I feel.

What we found at the time when we were experimenting using odata, what the users were being prompted for username and password every time they ran the spreadsheet… one of the reasons we went for the macro.

[SjoerdR (Sjoerd Repping)]

This is really nice but is this also possible when using a dashboard?
At our company, our BAQs contain more columns (calculated fields for example) than required.
In the dashboards, we hide the [not required columns ] and use some color coding . No customizing or whatever.
We don’t want to loose these changes.

Thanks

[Chad_Smith]

That’s an interesting question. Waiting to hear if anyone has a quick answer on that. I feel like it might be possible but likely isn’t something super easy like the BAQ would be.

I haven’t found a way to make the REST calls use the aliases for field names. It seems to always override them with the actual field names. Dashboards could potentially handle some cosmetic type things that a BAQ could not but you might be best off to drop the raw data in and use Excel to format it if that’s what you want.

[CSmith (Clint Smith)]

Power M Query in excel has table transforms that allow renaming of the columns appropriately.

Kind of like this:

let
    Headers = [
          #"Authorization"="Basic " & p("Base64Auth")
        , #"X-API-Key"=p("x_api_key")
        , #"Content-Type"="text/plain"
        ],
    paramQuery=p("BAQ"),
    paramWSD=if (p("WeekStartDate") = null) then "" else "&WeekStartDate="&Date.ToText(DateTime.Date(p("WeekStartDate")),"yyyy-MM-dd") ,
    Source = OData.Feed(p("Uri_Protocol") & "://" & p("Server_FQDN") & "/" & p("Epicor_Instance") & "/" & "api/v2/odata/" & p("Company") & "/" & p("ServiceName") & "/" & paramQuery & "/Data?" , Headers, [Implementation="2.0"]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"OrderDtl_Company", "Calculated_TotalTrailerLines", "Calculated_TotalTrailerQtys", "RowIdent"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"OrderHed_EntryPerson", "Calculated_OrderCount", "Calculated_TotalPartsLines", "Calculated_TotalLines", "Calculated_YRMO"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Calculated_TotalPartsLines", Order.Descending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"OrderHed_EntryPerson", "Entry Person"}, {"Calculated_OrderCount", "Orders Written"}, {"Calculated_TotalPartsLines", "Parts Lines"}, {"Calculated_TotalLines", "Total Lines"}, {"Calculated_YRMO", "Beginning Date"}})
in
    #"Renamed Columns"

[malonso (Matthew Alonso)]

Thinking out loud…

Could you theoretically create an Epicor function and mimic the JSON structure returned by a BAQ, then call that function endpoint from excel?

This would allow you to do all calculations within the function and only return the columns you want to show.