Dashboards, dashboards, dashboards, they’re everywhere. Everyone knows that they need Dashboards, but they don’t always know how to get from where they are to where they need to be.
The idea behind a Dashboard is that it brings the important information to the forefront so you can have it at your fingertips. This is much better than printing off pages and pages of reports, then sifting through the pages and adding up the numbers to find insights in the data.
Let’s be clear, Acumatica Dashboards are not a Business Intelligence (BI) tool. That’s not the purpose of an ERP system. If your ERP system does have a Business Intelligence tool, I’d be willing to bet that it’s actually a separate product that just has the same name and marketing logo as your ERP system. The similarity is skin deep only because they have the same coat of paint. The point is, ERP and BI are two different things. Acumatica Dashboards are not a full-blown Business Intelligence solution like Power BI.
That being said, you can still do some really cool things with Acumatica Dashboards.
Two things that I like about Acumatica Dashboards:
- Everything is real-time.
- It’s a Dashboard Engine, not a set of pre-canned Dashboards.
Everything is real-time because the Dashboards live within the Acumatica Framework and are not an outside 3rd party tool.
What do I mean by a Dashboard Engine? Well, a Dashboard Engine means that you have the ability to build your own Dashboards, from the ground up, pulling data from anywhere in Acumatica. It’s not a pre-canned set of Dashboard Widgets where you are only limited to being able to pick and choose the ones that you want to see. You craft the Dashboard Widgets exactly the way you want them. Pretty powerful stuff.
So how do you utilize the Acumatica Dashboard Engine to build the kinds of Dashboards that are relevant for your organization?
Well, I’m no expert, just a professional learner, but here are some of my tips and tricks.
Tip #1 – Start with a Whiteboard
The hard part is not building the Dashboard, it’s deciding what you want the Dashboard to look like.
Get people in a room with a whiteboard, the biggest whiteboard you can find. Then start drawing a rough sketch of what you want it to look like. Don’t draw in detail. Come up with a simple “cartoonish” representation of each Dashboard Widget, label each Widget with a Title, and add detailed notes to the side when needed.
Keep an eraser handy. Rarely will you get it right on the first pass. This is the whole point of using a whiteboard. Designing a Dashboard is a back-and-forth process where you have to see it in front of you, then you make adjustments, then you keep fine tuning until you have something that you like.
But don’t get too detailed yet. This is just a broad stroke, a general sketch on the canvas.
Tip #2 – Mockup in Excel with Sample Data
Now it’s time to take the next step. Now you want to take your Whiteboard sketch into the digital world.
Personally, I think that Microsoft Excel is the best software tool for this. I like to say that working in Excel is like working with “clay”, but working with Acumatica Dashboards is like working with “concrete”.
If you go directly to working with “concrete” before creating a “clay” model, then be prepared to have to get out the jackhammer and bust up some concrete when you realize that you need to make changes (which you will).
By working in Excel, you can easily make tweaks and changes fairly easily because Excel is very malleable, just like clay.
The most important thing about this tip is to use real sample data. Just get some historical data, load it into Excel, then build some Excel Dashboards on top of the data. It’s ok if the historical data is from another system. The important thing is that it’s real, something that people will recognize and understand.
It’s REALLY tempting to skip this step. But if you do, I think you’ll find that you’ll regret it later.
Building Dashboards is a back-and-forth process. Stay in Excel as long as you can during the process because it shortens the time delay between the back-and-forth. Once you have it looking the way you want in Excel, then you can “cast it in concrete” in an Acumatica Dashboard.
Or, you might be happy staying in Excel. If that’s the case, checkout these helpful posts:
- Acumatica Connects to Microsoft Excel and Power BI with OData
- Connecting to Acumatica data from Microsoft Excel
- Building a simple Dashboard in Excel using Acumatica ERP OData
- Microsoft Excel Dashboards in Acumatica ERP
- Excel Online Web App and Acumatica
Tip #3 – Add Calculated Columns to Generic Inquiry
Once you have an Excel blueprint in place, now you can start building in Acumatica.
One of the coolest things about Acumatica Dashboards is that they sit on top of Generic Inquiries. That means you can build whatever Generic Inquiry you want, giving you the data you need to build the Dashboard.
Building the Generic Inquiry is often the hardest part. Once you have the data that you need, adding the “pretty looking” Dashboard is fairly easy.
The key to building the Generic Inquiry is to add extra calculated columns using formulas that do the “thinking”. This is the intelligence that will sit underneath the Dashboard.
For example, let’s say you want to build a Dashboard on Aged Receivables based on Due Date.
First you need an Aging Bucket column. In this example, I’ll add it to the existing Generic Inquiry that’s attached to the Invoices and Memos (AR301000) screen.
You can build the Aging Bucket column like this:
=Switch(DateDiff('d',Today(),[ARInvoice.DueDate])>90,'5 - Over 90 Days',DateDiff('d',Today(),[ARInvoice.DueDate])>=61,'4 - 61-90 Days',DateDiff('d',Today(),[ARInvoice.DueDate])>=31,'3 - 31-60 Days',DateDiff('d',Today(),[ARInvoice.DueDate])>=1,'2 - 1-30 Days','1 - Past Due')
Now you can use this Aging Bucket column on your Dashboard. Maybe do a Bar Chart or a Column Chart based on it.
Click here to read Acumatica Dashboard Tips and Tricks (Part 2)