Has anyone used Power bi to make some reports/ analytics out of ECM?
our finance team wants to see things like what open pending approval, etc…
No ECM expert, but if the stats are in a different database (I’m sure they would be ) then why not use an external BAQ and create a dashboard. @MikeGross thoughts?
1 Like
ECM v22+ now has standard reports available within the application, one of which sounds like it would be the one you’re looking for. Designing your own reports requires a different license.
2 Likes
I think it really depends on what you are wanting to see - and more so what access does the finance team have.
If they can access ECM, then stick with the ECM reports if you can. I’ve found the built-in reports to be of little value (for us) except in the most basic sense.
However, writing a query against the ECM database to collect the raw data required for reports may be your biggest hurdle. There just isn’t any statistical data available and most data fields simply indicate a change of status with a date/time stamp. But without a bit more knowledge of the DB schema and relationships it’s hard to put it all together.
as @vleveris mentioned, you can write your own - There is a report creation tool in the latest version that seems to work OK (I have the reports license) but it’s more of a field selector. I’ve not created any yet, just played with it briefly.
As for using PowerBI, it should work just fine assuming you can build the SQL, but Id’ check the ECM reproting license cost before I paid MS $$ 
3 Likes
Thanks for the info. We are already paying MS for PBI and they have Power bi reports that point to our legacy workflow system. That is the main reason they want to stick with PBI.
1 Like
@Craig, just curious . . . is your ECM instance on premises ?
I worked with an on prem ECM instance from mid-2020 to the end of 2022 – before my company had access to the new “robust” ECM reports. I created a lot of SQL scripts & views against my ECM database for reporting purposes . . . including as a Tableau data source for charts & graphs.
Two issues with writing your own SQL scripts against ECM for reports, charts & graphs:
- Every document-level, or header-level, field that you see for a doc in ECM is stored as its own record in a database table, AAAA0001.[dbo].[CustomFieldValue]. The field is identified by a cryptic CustomFieldMetaId value for the table record.
So if you want your report script to pull in 5 document-level fields for 1 document, you’ll wind up writing 5 separate JOINS (possibly LEFT JOINS) to the same AAAA0001.[dbo].CustomFieldValue table to retrieve the values for the 5 fields, one JOIN for each CustomFieldMetaId. Or you could use a custom function 5 times instead of 5 separate JOINS / LEFT JOINS.
- If your document has line items, like AP Invoice Lines, and you want to query those, the scripting becomes more complicated, involving such tables as AAAA0001.[dbo].CustomFieldValue, AAAA0001.[dbo].CustomFieldGroupItem, and AAAA0001.[dbo].CustomFieldGroupTemplate.
In fact, I just looked at such scripts I wrote a few years ago & I’m amazed that I reverse-engineered the logic without a data model.
Workflow statuses also need to be taken into account when scripting.
I am not a developer. But querying the ECM database for some of the most obvious stuff can get a little hairy!
Best regards,
Eric
2 Likes
Yes we are On Prem
https://epicor-manufacturing.ideas.aha.io/ideas/ECM-I-1306
an epicor Idea to allow connections to Power bi from a report built in ECM.