By using this website, you agree to our Terms of Use (click here)
I am looking to creating a GI that can be used to display the following data in a chart format on a dashboard:
1) Current Accounts Receivable
Vertical Axis: Total (Currency, such as USD - $)
Horizontal Axis: Current, 1-30, 31-60, 61-90, 90+, Total
In order to achieve #1, I would need the resulting query to return a result set that had one row for each horizontal axis and two columns such as:
Table:
Column 1 : Column 2
Current : $100
1-30 : $50
31-60 : $30
61-90 : $20
$90 : $10
Total : $210
I could implement something like this fairly easily in SQL, but using the GI I haven't been able to identify a solution to building a query to achieve this. This seems like a fairly standard dashboard element.
Is this something that anyone could provide some guidance to me to achieve?
Additional, I would also like to achieve the following related chart if anyone has guidance:
2) Accounts Receivable Trend
Vertical Axis: Total (Currency, such as USD - $)
Horizontal Axis: Total AR for the past x financial periods (such as 3/6 periods)
- If we want to get cool, a stacked bar chart that shows current, 1-30, 31-60, 61-90, and 90+ would be useful 🙂
We did one, but it required a decent amount of work. Because we wanted to see upcoming invoices before they were released, we had to figure out a solution to see payments. We made our own view and then added that to Acumatica (see code below). From there we joined that to ARInvoice and did some math to get the balance (code below for balance). The Buckets were another tricky part. Getting them to show in a specific order wasn't happening, so we added some periods to the beginning of the bucket to get them to sort properly. We split up current AR into a -30 to -16 and -15 to 0 buckets (code below). This gives AR a list of upcoming AR to go after earlier on. Hopefully this helps you out. I don't know a way to just send you our GI or I would do that.
Bucket Code from GI
=IIf( DateDiff( 'd', IIf( [ARInvoice.DocType]='CRM', [ARInvoice.DocDate], [ARInvoice.DueDate]), Today())<=-16, '. -30 to -16', IIf( DateDiff( 'd', IIf( [ARInvoice.DocType]='CRM', [ARInvoice.DocDate], [ARInvoice.DueDate]), Today())<=0, '.. -15 to 0', IIf( DateDiff( 'd', IIf( [ARInvoice.DocType]='CRM', [ARInvoice.DocDate], [ARInvoice.DueDate]), Today())<=30, '... 1 to 30', IIf( DateDiff( 'd', IIf( [ARInvoice.DocType]='CRM', [ARInvoice.DocDate], [ARInvoice.DueDate]), Today())<=60, '.... 31 to 60', IIf( DateDiff( 'd', IIf( [ARInvoice.DocType]='CRM', [ARInvoice.DocDate], [ARInvoice.DueDate]), Today())<=90, '..... 61-90', '...... 90+')) )))
Doc Balance Code from GI
=IIf( [ARInvoice.DocType]='CRM', -1,1)*([ARInvoice.CuryOrigDocAmt]-IsNull( [V_Inv_Paid.PaidAmt], 0))
Code for View
ALTER VIEW [dbo].[V_Inv_Paid]
AS
SELECT SUM(PaidAmt) as PaidAmt, AdjdRefNbr from
(SELECT SUM(CuryAdjgAmt) AS PaidAmt, AdjdRefNbr
FROM dbo.ARAdjust
WHERE (CompanyID = 2)
GROUP BY AdjdRefNbr
union all
SELECT SUM(CuryAdjgAmt) AS PaidAmt, AdjgRefNbr
FROM dbo.ARAdjust
WHERE (CompanyID = 2)
GROUP BY AdjgRefNbr) A
group by AdjdRefNbr
@ryan brown Thank you for the detailed information on your approach. You've touched on an aspect that has been very tempting for myself. If I could do this in SQL, it would be easy. It's trying to do this in the confines of GI's that has been my limiting factor. I'll make an acknowledgement that I have not been able to learn BQL but the documentation of both the syntax as well as the tables/classes is sorely lacking meaning trial and error.
I'm assuming you created the view in SQL Server and then added it to acumatica through a customization. Would you be able to share the code that you used in the customization for adding it to Acumatica?
Have you ran into issue with database upgrades wiping out additional elements in the database, such as views? In an earlier version of Acumatica, I found an error in one of their indexes on a table. I reported it and it was fixed later. I'd patch the error (update the index) so I didn't get flagged constantly. However with each Acumatica update it would overwrite my changes. Have you have issue with adding custom views (or stored procedures, functions, etc.) with them being deleted during Acumatica updates?
From the GI, we created a dashboard bar chart to summarize the data.
@morgan-taylor, the report you described is available out-of-the-box with DataSelf Analytics ( https://dataself.com). This canned template comes with a few variations such as sliced by customer, customer address and salesperson. In total, there are 550+ report and dashboard templates focused on open receivables analysis. If interested, please contact sales @ dataself.com to learn more. Thanks!
We've had no issues upgrading. We have a ton of views used for outside datafeeds and internal reporting and have not had any issues. The stored procedures have also been fine across upgrades. We use those mostly for updating custom fields. The stored procedures also create custom tables which then populate Acumatica data through import scenarios.
To add views to Acumatica -
Create a new project from your customization projects screen
Go to the Code section
Click on the plus sign
For File Template, select New DAC
Put the name of the view in Class Name
Check the "Generate Members from Database"
And then click on OK - It will add the necessary code for you
Just publish that and you will be able to access your view from a GI or dashboard
Just created my first function with input variables, multi-statement function that pulls in variables from various tables including the master financial calendars (i.e. determines the last closed month so I can display AR reports and eventually financial dashboards for the last x closed months), and a view associated with all.
The created DAC was then pulled into a generic inquiry and data displayed correctly. Could have never even considered doing this in Acumatica.
Very cool. Thank you for your help! This has completely given me a direction with how I can accomplish my question as well as some more advanced data views in the future.
I'm a kid with a new toy...
Here's an article from Doug Johnson on the topic of linking SQL Views to Generic Inquiries for anyone else who is interested:
https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/
Online Members
