EPICOR Report from OData

Is there a way to get an EPICOR report with OData? It seems from the REST help that only ERP.BO and some Ice are available. I need to bring the Trial Balance report into an excel spreadsheet. (I know you can export to excel, but managers want to cut that step)

Thanks!!

Not ‘in the box’.

Currently the Reporting system leverages a ‘staging’ / ‘temp’ / ‘reporting’ db that is a cache of data for that report. The reason is too often a customer wants to do some serious data crunching and lots of data gets queried and possibly printed. The amount of data can be massive and you see a lot of free ram disappear quickly, the lights dim and other users come to a halt as that data query downloads a sizable portion of the Internet to render the report. To prevent that, a ‘Report BO’ potentially does a ton of things that in the end is basically a sql data reader that funnels data quickly between the core erp db to the reporting db - a table per report output. Then SSRS points at that table as the data source so rendering is as simple as we can make it for SSRS.

Can SSRS use other data sources? Of course, lots of options. Go explore and build whatever you wish. You will need to be concerned about your impact on the server. In many cases it will be minimal and not a concern. You may end up needing a larger amount of data and then you may wish to leverage the BAQ Reports as an approach.

Does that make sense?

2 Likes

It looks like the recommended solution for SSRS is to built SQL Views , then to display the view in SSRS using URL specific report parameters. Something like

http://dbserver/ReportServer/Pages/ReportViewer.aspx?ERP10/reports/CustomReports/OrderParam&rs:Command=Render&Company=DALTON&OrderNum=10000

In your opinion would it make sense to move the most common reports to this method for speed and to leave the longer running reports to use the Task Agent?

Assuming the step you want to skip is exporting to Excel why not default the output to Excel and let the Trial Balance run that way?

Longer running / lots of data / cpu intensive would NOT be candidates for OData. Obviously I am looking at this from a SaaS / Cloud POV on what I see so mileage will vary for all on premise. YOU know your business and hopefully your data :slight_smile:

So since it seems I will need to re do Trial Balance, what would you recommend? I am a SaaS customer so I can’t use any type of coding, only BAQs

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

TrialBalance.baq (31.9 KB)

Something like this work?

2 Likes

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!