One of the new features that I’m loving in Acumatica 2019R2 is the ability to use conditional formatting on an Acumatica dashboard table widget. Previously conditional formatting was available on generic inquiries, but the formatting didn’t carry over to the dashboards.
Today I’m going to describe how to implement this feature so that a Sales Manager can have a color coded view of YTD sales vs quota. What we’re aiming for is something like this:
Here is how our Sales Manager dashboard currently appears:
You can see that the YTD SALES VS QUOTA table is black and white, making it more difficult for the sales manager to assess with a quick glance. We would like to color code the YTD SALES VS QUOTA widget based on performance.
Our target dashboard widget is based on a generic inquiry. To add the conditional formatting, we’ll need to make a couple of changes to that inquiry.
Here is the logic that we hope to capture:
We can express this as a formula as follows:
=IIf([ARTran.TranAmt]>=CDec( [CSAnswers.Value] ) * Month( Today()) / 12, ‘green’, iif( [ARTran.TranAmt]>=0.75*CDec( [CSAnswers.Value] ) * Month( Today()) / 12, ‘yellow’, ‘red’))
If you’re used to writing formulas in other report writers, you’ll find that there’s no need to explicitly add the sum function to group and total the transaction amount. Acumatica has automatically applied this logic based on the aggregation selections in the generic inquiry.
To add the formula to the Generic Inquiry, we simply select the ‘Results Grid’ tab, and click on the pencil next to Row Style.
We enter the formula, then click ‘Validate’ to be sure we don’t have any syntax errors.
Save the generic inquiry, then click ‘View Inquiry’ to test. When I initially tested this I got this:
Looks like all of our reps are doing great, but in fact the sales quota was evaluating to 0 in the formula. Clearly this is not what we want! In order for the formula to work correctly, I found that we need to add the sales quota attribute (CSAnswers.Value) to the results grid of the generic inquiry. We can uncheck the “Visible” box so it won’t appear on the dashboard.
Here’s our Result Grid with the new row added:
Once we save the generic inquiry, the conditional formatting appears in our dashboard.
And that’s it, we’ve updated our dashboard with the new conditional formatting. Pretty easy, if you ask me!
If you would like to learn more about Acumatica, please give us a call at 440-498-9920.
