By using this website, you agree to our Terms of Use (click here)
Looking to see if anyone out there has written a Generic Inquiry on pulling Year to Date figures from GLHistory for Dashboard. My problem is as follows. Say for instance I am running Generic Inquiry against GL history pulling for 2019 on or before 4/30/19. I'm finding I need to pull the MAX dated records per GL Account for 2019. Just having difficulty with the grouping and formula for pulling correct figure.
I have attached the below example showing that I only need the records Highlighted in Yellow for example to derive the YTD Balance. The trick is ignoring previous period records in 2019 BUT incorporating the records for SubAccounts that may have only posted to prior periods and not current period. Any help is much appreciated.
Just a note that 6-2019 is fiscal April
Take a look at these joins in the Trial Balance Summary (GL632000) report. I think you could do something similar in your Generic Inquiry.
Forgot to follow up with this. I determined the key was adding the GLHistoryByPeriod table to query using the LastActivityPeriod within this query for the join.
Here are the following screen capture with added table and Join. Thanks Tim! I think the Trial Balance is doing something similar.
@greg-polsky I am trying to achieve the same thing you are: show only the YTD value for the most recent financial period:
I haven't had any luck though. Here are my joins and condition. Do you have any advice on how I can fix this?
Here's a link to a similar topic:
I have made a generic inquiry and dashboard that you can use as I know it can be problematic to build and very useful to many.
It has Period to date, year to date and gross margin inquiries in the attached zip. The dashboard is the xml file "Group Sales Manager".
For the one GI in the dashboard, needs you to create an attribute called MYPIVOT that is a combo, with 2 lines numbered 1 and 2.
It also assumes ACTUAL ledger is LedgerID 1 and BUDGET is LedgerID 2 but obviously you can change it in the GI's. It also assumes to exclude certain income account classes, so again, change as you need.
Enjoy!
After all these years, I've yet to see a full solution to this problem.
Is it possible to have a Generic Inquiry re-create the Alias'ing that can be done within Acumatica Report Designer? I dont know a way within the GI to have logic to choose either the select month if it has activity and if empty do the lookup based on the LastActivityDate
If anyone could post a detailed solution to this problem, it would be invaluable for new users (like myself).
Something as simple as re-creating the "Trial Balance Summary" within a Generic Inquiry would be wonderful.
Ideally this would be available out of the box, but one way to accomplish this would be to use a SQL View that could then be used wherever you want.
Online Members
