On Premise external SSRS reports vs BAQ Reports/Dynamic Reports in Epicor

Kinetic ERP
1

I was recently made aware that you can write SSRS reports that can reference the SQL Server data that Epicor uses and set up subscriptions all outside of Epicor itself. Previously, I’ve leaned heavily on the BAQ reports but more recently the dynamic reports to generate my RDL files and then I used Epicor’s System Agent to set up schedules to distribute those reports.

Is there an inherent advantage of doing it one way vs. the other?

2

Here are a few of my own:
Direct SSRS Pros:

  • You can write SQL directly into the report query and it will automatically setup all your fields based on your SELECT statement. This is nice.
  • You don’t need Epicor access or familiarity to interact with SSRS reports and they are all hyperlink friendly and can still be accessed from within Epicor if you want them to be (using a URL link menu).

BAQ/Dynamic Report Pros:

  • Complicated queries are easier to set up and maintain in the BAQ. Especially if there’s a lot of calculated fields and grouping. I like the visual the BAQ gives for structuring the query.
3

Some cons of hitting the SQL Server directly:

  • Your instance is hardcoded in your SSRS report, moving from Test to Live requires updated the data source
  • Does not respect field security, territory security, site security, or company security, or BAQ security.
  • Any user security will have to be maintained in SQL using credentials outside of Epicor’s users and groups
  • UD fields have to be accessed via views or you have to set up your own linkage
  • Depending on the permission granted to the SSRS agent, someone can use SQL to make updates to the database
  • Won’t transfer to cloud if that day ever comes
  • No Advanced Print Routing
4 Likes
4

Those are really great points. And from my data reporting chair, a couple easier steps doesn’t look to be worth the headache of some of this.

The views are already there so querying the view instead of the table is not a big deal… but you have to know to do it. I think my one use case that I did it for still makes sense… but in general, I’d prefer to go through Epicor for many of those security reasons you stated.

2 Likes
5

It’s the old story, just because you can, doesn’t mean you should.

1 Like