Questions 5. Stuff That Should Never Happen (STSNH) Dashboard - Summit 2019 Session Examples

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 #5, I want to give an example of a Widget that you could add to what I like to call a Stuff That Should Never Happen Dashboard. Or STSNH for short.

An STSNH Dashboard is targeted at the Controller because the Controller is the one who has to deal with problems during the month-end close process.

One "problem" with catching problems at month-end is that each problem could have happened anytime during the month. Maybe you are closing the month on the 2nd and you discover a problem that happened last month on the 5th. You might be able to figure out who created the transaction that caused the problem, but when you talk to them, they might not remember why they even created the transaction. Because it's been almost a whole month! Many people (including myself) sometimes have trouble remembering what they did yesterday, let alone last month.

The idea of a Stuff That Should Never Happen (STSNH) Dashboard is that it surfaces problems as soon as they happen so they can be dealt with as soon as possible while everyone still remembers the situation surrounding the problem.

Also, dealing with problems during the month rather than waiting to catch everything at month-end makes the Accounting department's month-end close job a lot easier. Maybe they now get to go home at 5pm rather than 10pm during the close which makes them, their spouses, and their kids a lot happier. That's why I say that the STSNH Dashboard is targeted at the Controller.

There are lots of things that you could put on a Stuff That Should Never Happen Dashboard. You can add as many Widgets to a Dashboard as you want. Just keep scrolling down to view them. And the Accountants can make the STSNH Dashboard their homepage so they see it every time they login to Acumatica.

In this example, let's look at one idea for a STSNH Dashboard Widget: Comparing the Accounts Payable subledger balance to the AP Account balances in the General Ledger.

The hardest part of this example is building the Generic Inquiry. I had to use a SQL View using the technique outlined by Doug Johnson in this post about creating SQL Views in Acumatica to be used by Generic Inquiries (click here).

1. I created two separate SQL Views. One to grab the Accounts Payable balance. The other to grab the General Ledger balance. Here is the SQL Code for each SQL View (note that this will probably only run on a Microsoft SQL Server database and not MySQL):

CREATE VIEW [dbo].[QQAPBalanceAP] AS
SELECT APHistory.CompanyID,'APHistory'[Table],MAX(BranchCD)[BranchCD],MAX(AccountCD)[AccountCD],MAX(SubCD)[SubCD],(SELECT MAX(FinPeriodID) FROM APHistory MaxVal WHERE CompanyID=APHistory.CompanyID AND BranchID=APHistory.BranchID AND AccountID=APHistory.AccountID AND SubID=APHistory.SubID)[FinPeriodID],
SUM(FinYtdBalance)[FinYtdBalance]
FROM APHistory
LEFT OUTER JOIN Branch ON APHistory.CompanyID=Branch.CompanyID AND APHistory.BranchID=Branch.BranchID
LEFT OUTER JOIN Account ON APHistory.CompanyID=Account.CompanyID AND APHistory.AccountID=Account.AccountID
LEFT OUTER JOIN Sub ON APHistory.CompanyID=Sub.CompanyID AND APHistory.SubID=Sub.SubID
WHERE APHistory.AccountID IN (SELECT APAcctID FROM VendorClass WHERE CompanyID=APHistory.CompanyID)
AND FinPeriodID=(SELECT MAX(FinPeriodID) FROM APHistory MaxVal WHERE CompanyID=APHistory.CompanyID AND BranchID=APHistory.BranchID AND AccountID=APHistory.AccountID AND SubID=APHistory.SubID AND VendorID=APHistory.VendorID)
GROUP BY APHistory.CompanyID,APHistory.BranchID,APHistory.AccountID,APHistory.SubID
CREATE VIEW [dbo].[QQAPBalanceGL] AS
SELECT GLHistory.CompanyID,'GLHistory'[Table],MAX(BranchCD)[BranchCD],MAX(AccountCD)[AccountCD],MAX(SubCD)[SubCD],FinPeriodID,SUM(FinYtdBalance)[FinYtdBalance]
FROM GLHistory
LEFT OUTER JOIN Branch ON GLHistory.CompanyID=Branch.CompanyID AND GLHistory.BranchID=Branch.BranchID
LEFT OUTER JOIN Account ON GLHistory.CompanyID=Account.CompanyID AND GLHistory.AccountID=Account.AccountiD
LEFT OUTER JOIN Sub ON GLHistory.CompanyID=Sub.CompanyID AND GLHistory.SubID=Sub.SubID
LEFT OUTER JOIN Ledger ON GLHistory.CompanyID=Ledger.CompanyID AND GLHistory.LedgerID=Ledger.LedgerID
WHERE LedgerCD='ACTUAL'
AND GLHistory.AccountID IN (SELECT APAcctID FROM VendorClass WHERE CompanyID=GLHistory.CompanyID)
AND FinPeriodID=(SELECT MAX(FinPeriodID) FROM GLHistory MaxVal WHERE CompanyID=GLHistory.CompanyID AND BranchID=GLHistory.BranchID AND AccountID=GLHistory.AccountID AND SubID=GLHistory.SubID AND LedgerID=GLHistory.LedgerID)
GROUP BY GLHistory.CompanyID,GLHistory.BranchID,GLHistory.AccountID,GLHistory.SubID,GLHistory.FinPeriodID

2. In order to use a SQL View in a Generic Inquiry, you have to turn it into a Data Access Class in Acumatica. For details on that, see then link to Doug Johnson's post above. For this example, I'm just attaching the Customization Project so you can import it into your environment. Simply download the Summit2019STSNH Customization Project (click here), import it into your environment with the IMPORT button on the Customization Projects (SM204505) screen, check the box on the Summit2019STSNH line, and click the PUBLISH button:

3. Then you can create the Generic Inquiry that is based on the Data Access Classes we just created. Here are screenshots from my Generic Inquiry. The formulas for the last two fields (Difference and Difference Abs) on the RESULTS GRID tab are as follows:

=[QQAPBalanceAP.FinYtdBalance]-[QQAPBalanceGL.FinYtdBalance]
=Abs([QQAPBalanceAP.FinYtdBalance]-[QQAPBalanceGL.FinYtdBalance])

4. Now the hard part is done and you have a Generic Inquiry that compares the balances between the General Ledger and Accounts Payable modules by Branch, Account, and Subaccount for the latest Post Period and tells us whether there is a difference:

5. Now we can create a Scorecard KPI Widget on our Stuff That Should Never Happen Dashboard to detect if the two modules are out-of-balance.

6. On the Widget Properties, set the Inquiry Screen field to Hidden -> Hidden Generic Inquiries -> Stuff That Should Never Happen, the Field to Aggregate field to Difference Abs, the Aggregate Function field to Sum, the Normal Color field to Green, the Warning Color field to Yellow, the Alarm Color field to Red, and the Caption field to $ Difference (AP/GL):

7. Now you have a Widget on your Stuff That Should Never Happen Dashboard:

8. Since we set the Alarm Color field to Red, the Widget will turn red if there is ever if difference between the balances in the two modules. Let's say that someone makes a General Ledger journal entry (no Accounts Payable module entry) to the Accounts Payable account for $50 in the PRODWHOLE Branch. Now the two modules are out-of-balance by $50 and our Widget turns Red and we know that we are off by $50:

9. Click on the Widget to drilldown and see where the problem is:

10. Remember, you can add as many Widgets as you want to this Stuff That Should Never Happen Dashboard. Just keep scrolling down to see them. Here is an example of a Dashboard with a bunch of Widgets. Imagine each Widget being red if there is a problem and green if there is no problem. You can spot the reds easily at a quick glance. If everything is green, everything is good, nothing to worry about!

11. Lastly, I want to point out in the SQL code above that I tried to be as dynamic as possible, finding the most recent Financial Period and looking at the GL Accounts across all Vendors to find the relevant AP Accounts. But I did hardcode ACTUAL for the Ledger so you'd need to change that if your Actual Ledger has a different ID.

Also, if you do create a Stuff That Should Never Happen Dashboard, be prepared. Your Controller might give you a big hug because you will be making their job a lot easier.

Want to join the conversation? Feel free to add a Post below to this Topic to continue the conversation.