Have you looked at Baq reports?
You mean BAQ report designer?
Yes. Design the report as BAQs and ‘print’ to Excel?
I am trying to create the BAQ, but Trial balance has a formula that I haven’t been able to reproduce, you know to calculate the Period OB and Period CB
Thanks!
ahh, i thought you already had the data of interest.
Well that is the whole point, EPICOR has a Trial Balance report that I can’t acces via OData haha
yup, got it now (I blame too much management and not enough code lately).
We have received requests like you are asking but as you look thru the thread you see the different scenarios where we don’t expose everything, everywhere for obvious reasons. The REST and OData features are still in their infancy at this time as anyone can see. They are also gaining traction extremely quickly as you can see by their use in numerous released products and features and a few more we still have under wraps for Insights and beyond. I’ll admit the reporting side is not near the front of the line for full REST / OData adoption. That area has a few other wish list things first. I would continue to watch the BAQ Reporting / Electronic Compliance area as that is area we have focused heavily on for the last few releases and is a much better reporting foundation.
So can you help me recreate the Trial balance report using just BAQ designer in EPICOR? I’m going crazy trying to figure out how to calculate the Period OB and closing balance, since the closing balance for period 1 has to be te opening for period 2 I need to be able to do some kind of recursive calculation. I have firuged out that using LAG you can get the previous value but you can’t aggregate :(.
And I mean this is something that is really helpful to be able to do, its just that I havent been able to crack this mistery.
Thanks!
I believe the Opening Balance is stored in the GLPeriodBal table in the OpenBalance column where the FiscalPeriod = 0
Righ, but that one is just for the beginning of the year.
So the opening balance for Period 1 is that one, then you add debits and credits and the closing balance of period 1 is going to be the opening balance of Period 2.
Once Period 2 is calculated you need to add debits and credits, and that will be your closing balance for Period 2, which is going to be Opening Balance for Period 3 and so on and so forth
If Management wants that kind of ease of use, why not look at XL Connect? I believe that is what they want.
1 Like
Thanks I will for sure forward this to the Finance Director, I believe he was actually there last year.
Well Yes! The BAQ worked, but When I try to show it up in excel with OData it crashes, probably due to the parameters. Do you know by any chance how to pass it ? The ideal thing would be to have to parameters inserted in the url by the users via cell input
: D Thanks!
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
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.
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
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
Okay, Here it goes!
First, we need to create the cells that will act as parameters
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”
In this case we change it to End Period
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
Then click on Save and load, A new sheet will be loaded
Please delete the sheet so that the message reads, connection only.
Repeat as needed
Then, go to New source in the Query Editor > Other Sources > ODataFeed
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
Now we will create the functions that will reference the cell value
Then we just create 3 variables and then call our function referencing our Table
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
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
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