There is a pretty clear pattern emerging for me that I don’t have a great solution for, wondering if others have suggestions…
Today it’s OTD, yesterday sales data, but regardless the pattern is we want to leverage our data to look for trends. Excel pivot tables and pivot charts are kind of the gold standard for me(I can quickly pull in thousands of rows of data and summarize and graph then in a matter of minutes to see trends). The problem with excel is it’s a one time exercise it’s not dynamic. Sure I could link an Excel document to consume a BAQ so it is dynamic but I can’t imagine passing around a bunch of Excel documents to analyze our data(change/version hell trying to maintain such an approach). I’d love to have the functionality Excel offers programmatically so I could dynamically create a pivot chart and pivot graph so my users can visualize the trends with out me spending hours on each report to implement this myself
My current workflow is to create a BAQ to summarize and group the data and that is where I get stuck in regards to Epicor tools(see screenshot below for a concrete example). I typically then consume the BAQ via a REST API into PHP where I essentially implement my own one off pivot table(I’m not doing anything with charts but want to) so I can dynamically present the data for my users so they can easily see the “big picture” and spot trends which ideally leads to business insights we can act upon…
Other than still needing to find some good charting libraries my main problem is, the PHP side of things is really time consuming and monotonous(I’ll spend easily a couple hours slicing / dicing / and organizing the data and presenting it in an html table), it’s seems ripe for automation. I’m thinking there has to be better tools to solve what I expect is a common problem almost every business running Epicor would want solved so thought I’d ask as I head down the path of writing another PHP script to pull in Supplier OTD data so I can present it in a nice HTML table for my users so they can look for trends and problem suppliers…
Any ideas, suggestions, or experience you can share would be greatly appreciated. Even if there isn’t a tool to automate this maybe there are some nice libraries out their I could take advantage of or possibly another language makes this easier…
No ideas, suggestions, or experience … Just more questions for you
What is this to be presented on? Saying PHP, makes me assume it’s to be viewed on a web browser, using a URL that is internal (or possibly accessible via the internet). That correct?
I put this type of data on webpages for two reasons:
1.) A lot of our employees that don’t have to work in Epicor, think all ERP systems are evil and they would never ever attempt to login let alone learn how to use Epicor. But that is a totally different topic… From not such a negative perspective webpages are really nice, I can load them on my phone, I can read in the HTML and send out a monthly HTML email to the managers that need to see an important report(they like this).
2.) I’ve got a hammer(PHP) and this problem kind of looks like a nail. Said another way I just don’t know of any other tools to solve this problem.
Here is an example of another HTML table I created for sales data(we have one for revenue, # orders, etc). Someone can look at this and in about a minute see where we are today and what direction we’re trending.
Very vague comment coming here, but you can use microsoft power BI to embed power bi visuals or reports on web pages (additional licensing most likely required- it’s been a minute since I read up on it). You can connect to SQL using power BI and create a data gateway to allow power bi service to connect to it.
Here is an example of it in use for michigan’s coronavirus data:
Nice… Are any of these “live” as in the data displayed constantly updates? Or does it take user interaction to “refresh” the data? Because I’m thinking that SSRS reports might be something to look at. While we tend to think that they (SSRS) are meant to target a piece of paper (or PDF), they native tools in SSRS do lean towards web page output.
@ckrusen the user would have to hit refresh to call the BAQ again, but it wouldn’t be hard to refresh it for them.
Thanks for the suggestion about SSRS I wouldn’t have considered this. My limited experience with Crystal reports has soured me and I haven’t had any interest to learn about reports. If we have a problem with a Crystal Report my first thought is we might want to redo this as a dashboard…
My assumption was other company need to solve this same problem so I wonder how they are going about it and if they are happy with the current solution or if they are looking for something else…
