EPICOR Report from OData

Epicor ERP 10
21

The best thing to do is to get your query working in Postman, then copy that into Excel and create your Connection while recording it as a macro.

What I did after that was to parameterize the URL. I removed the actual data and replaced it with “$name” placeholders. Finally, I created a button that calls the VBA routine that replaces the $parms with actual values from cells. You can try to build the formula from scratch but the quotes get real hairy really fast.

Here’s some of my initial testing code as an example:

    ' Get Data and format to REST format
    sStartDt = Format(Range("StartDate"), "yyyy-MM-dd")
    sEndDt = Format(Range("EndDate"), "yyyy-MM-dd")
    sPeriod = Format(DateAdd("d", -1, sStartDt), "yyyy-MM-dd")
    ' Change Instance as neede
    sURI = "https://ausdtspilot100.epicorsaas.com/SaaS000Pilot"
    ' BAQ to call
    sBAQ = "000-Backlog"
    ' Create a new formula from the template
    sTemplate = "let" & Chr(13) & "" & Chr(10) & _
        "    Source = OData.Feed(""$URI/api/v1/BaqSvc/$BAQ?Company='101'&Period='$PERIOD'&Start_Date='$START'&End_Date='$END'"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"

    ' Update Formula with parameters
    sFormula = sTemplate
    sFormula = Replace(sFormula, "$URI", sURI)
    sFormula = Replace(sFormula, "$BAQ", sBAQ)
    sFormula = Replace(sFormula, "$PERIOD", sPeriod)
    sFormula = Replace(sFormula, "$START", sStartDt)
    sFormula = Replace(sFormula, "$END", sEndDt)

    ' Update Formula
    ActiveWorkbook.Queries("BacklogBAQ").Formula = sFormula
    
    ' Clear Data tab
    Set wsData = Sheets("Data")
    wsData.Activate
    Cells.Select
    Selection.ListObject.QueryTable.Delete
    Selection.ClearContents

    ' Refresh data

    With wsData.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=BacklogBAQ;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [BacklogBAQ]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "BacklogBAQ"
        .Refresh BackgroundQuery:=False
    End With

Mark W.

2 Likes
22

Thanks! I managed to get ti to work with Power query, but now it seems that the BAQ is excluding accounts with no period movement, so now I am breaking my head to get it in.

If any is interested in my solution on how to pass the parameters with OData, let me know.

23

TrialBalance1.baq (35.1 KB)
I updated BAQ to include accts with no period movement and correctly calculates Period CB now

If you could show your solution to pass the parameters, that would be great

Bernie.

1 Like
24

99893-TRIALBALREPV2.baq (35.6 KB)

Hello Bernie, there were still some issues with the BAQ, but it is fixed now. I’ll post the steps for passing the solutions in a moment.

Thanks!

2 Likes
25

Okay, Here it goes!

First, we need to create the cells that will act as parameters
OData1
If you see the name of the cell is A2 right now. First, we change the name of the cell to the “Name of the table”
OData2
In this case we change it to End Period
Odata3
Then we click in the From Table/Range button in the Data Ribbon
Then, make sure that the table icon is at the left of the name of the cell

OData4
OData41837×387 30.7 KB

Then click on Save and load, A new sheet will be loaded
OData5
Please delete the sheet so that the message reads, connection only.OData6
Repeat as needed
OData7
Then, go to New source in the Query Editor > Other Sources > ODataFeedOData8

Then enter your BAQ with some random parameters to establish connection.
https://yourserver/instance/api/v1/BaqSvc/YourBaq?BeginPeriod=7&EndPeriod=7&FiscalYear=2017
Then Select Close and Load
Then open again the query editor, select Query1 or whatever the name of the table that contains the BAQ is.
You will see something like this

OData9
OData91046×113 5.87 KB

Now we will create the functions that will reference the cell value
OData10
OData10583×599 19.2 KB

Then we just create 3 variables and then call our function referencing our Table
OData11
OData111053×169 7.37 KB

Then we reference it in our parameters, if you see after the main request that ends in the BAQ ID we need to type a closing question mark “?” and then we start calling our parameters, that is why I have BeginPeriod=, then we add an “&” to concatenate the text with the value of the variable, in this case we don’t use double quotes since it is a string.
Don’t forget to add an ampersand as part of the string at the beginning of the following parameters.

Then Press Close and Load… and That is it. You can now change the value of the cells, hit refresh and it should be working.
What I did is just cut the cells, added some rows at the top of the query to keep everything in the same worksheet

OData12
OData121232×123 4.88 KB

Hope this helps someone! I searched like crazy a lot of different methods, this is the one that worked for me. Any questions please let me know!

8 Likes
26

Bernie,

Your BAQ is exactly what I have been looking for. I am running into one problem though. When I run the BAQ, it is not picking up any accounts with dynamic segments. Would you be able to tell me how to get these accounts to pull into the BAQ?

Thanks,

CF