By using this website, you agree to our Terms of Use (click here)
This post shows you how to create one of the examples from my presentation at Acumatica Summit 2019 in Houston, entitled C103: Getting Actionable Information with Self-Service Reports & Dashboards (click here for all of the examples).
In this Example #2, when analyzing data, it's useful to put things into groups or categories. This is what I mean by "banded values".
Let's look at the Sales Orders (SO3010PL) inquiry screen in Acumatica. I can see the Order Total for each individual Order.
That's great, but it doesn't help me see how I'm doing across all of my Orders.
I can add a banded value column to assign a group/category to each Order.
Then I can do things with that new column, like add Pivot Tables and Dashboards.
1. Go to CUSTOMIZATION -> Edit Generic Inquiry in the upper right-hand corner to edit the Generic Inquiry:
2. Add a column to the RESULTS GRID tab called Order Size and use the following formula in the Data Field column:
=Switch([SOOrder.OrderTotal]>100000,'Large',[SOOrder.OrderTotal]>50000,'Medium','Small')
This will put all Orders greater than $10,000 in the Large group/category, all the Orders between $5,000 and $10,000 in the Medium group/category, and all the Orders $5,000 or less in the Small group/category. The Switch formula is basically like using a bunch of nested Iif (yes, there is an extra "i" for some reason) formulas, but it looks a lot cleaner and is easier to read. You can put as many arguments in the formula as you want. The last argument ("Small" in this case) is the catch-all that will be the value if nothing else is true.
3. Now when we look at the Sales Orders (SO3010PL) inquiry screen, we can see our new Order Size column:
4. We can add Pivot Tables to this Generic Inquiry using our new Order Size column. First, use ... -> Save As Pivot to create the Pivot Table:
5. You can drag Order Size and Order Nbr. to the Rows area, Date to the Columns area, and Order $ Total and Order Count to the Values area. Also note that Created By, Order Type, and Date have been dragged to the Filter area:
6. Here are the Order Size field Properties. Note that there is a really nice feature in Acumatica 2019 R1 which allows you to set a level to Collapsed by default:
7. Here are the Order Nbr. field Properties:
8. Here are the Date field Properties. Note that I've set the Round to field to Years. Note that my filter on the top is set to only display one year, but if you filtered across multiple years, then you would have a column for each year because we put Date in the Columns area:
9. Here are the Order $ Total field Properties. Note that I set the default Width to 150:
10. Here are the Order $ Total field Properties. Note that I set the Aggregate to Count and we are counting the SOOrder.OrderNbr field:
11. Click the Settings icon to get out of Edit mode:
12. And now we are looking at the Pivot Table. What I really like is that this is a tab on the Generic Inquiry (I called mine BY ORDER SIZE $) instead of launching a separate screen like used to happen in older versions of Acumatica.
13. In Acumatica 2019 R1, you can set a field to collapsed by default (like we did up above), then you can click a + icon to expand a specific row. Since we dragged Order Nbr. to the Rows area, we are now looking at all of the Orders within the Order Size group/category of Medium:
14. You can define as many Pivot Tables as you want. I created another one called BY ORDER SIZE %. Here we see percentages rather than dollar amounts:
15. The only difference is that I checked the Show Percent box on both of the fields in the Values area:
16. We can also create a Dashboard widget using the new Order Size column:
17. First add a new Widget to the Dashboard:
18. Choose a Chart Widget:
19. Set the Inquiry Screen to Hidden -> Primary Lists -> Sales Orders, the Caption to "Sales Order Sizes", then click the CONFIGURE button:
20. Set the Chart Type to Bar, the Legend to Order Size, and the Value to Order Total:
21. That should give you a nice looking Dashboard Widget:
22. Lastly, don't forget that you can drilldown on either a Pivot Table value or a Dashboard Widget bar in the screenshot above. In either case, the result is that a new tab is created called DRILLDOWN with an on-the-fly filter applied based on what I clicked on. If I click on Medium in the screenshot above, a new tab opens and I'm now only looking at records in the Generic Inquiry that have an Order Size value of Medium:
23. If you want to be sure that the context-sensitive filter that was applied is correct, you can click the Filter icon and review the filter.
Want to join the conversation? Feel free to add a Post below to this Topic to continue the conversation.