By using this website, you agree to our Terms of Use (click here)
I have tried to create a few inquiries that provide data by period, such as cash by period. I can get it to almost work, except I cannot find a way for it to use data from the current period unless a journal entry has been created for that account. There are a few data fields that I believe would solve this issue for me, the GLHistoryEnqResults & AccountByPeriodFilter fields, except Acumatica does not allow me to use these fields in an inquiry. My end goal is to have a line chart widget in my dashboard, so it has to be an inquiry. Has anyone else found this issue or a way to work around it?
I would recommend your very first table relation to be Account CROSS APPLIED (Cross join type) to MasterFinPeriod, then left join into your Summary tables off of that. This will fill in all of the holes from the missing summary records.
I did that, and it did fill in the holes. However, now there are a lot of repeated lines. Would you know how I could get around that?
Sounds like your relations aren't complete. You should review the relations for specifically your summary joins and make sure they are linked on both account and financial period.
It cants only use simple relationship I think. check ARM at https://openuni.acumatica.com//wp-content/uploads/2016/10/F350-ARM-Reports.pdf
you might need to splite the Month by this function.
=IIf(Left(@StartPeriod,2)='01',
Left(Report.GetPeriodDescription(@StartPeriod),3)+
' '+Right(@StartPeriod,4),
Left(Report.GetPeriodDescription('01'+Right(@StartPeriod,4)),3)+
' '+Right(@StartPeriod,4)+'-'+
Left(Report.GetPeriodDescription(@StartPeriod),3)+
' '+Right(@StartPeriod,4))
This expression displays the range of the periods from the first period of the year to the
period selected by the user. For example, if a user ran the report for the 03-2013 period,
the header would show Jan 2013-Mar 2013.
What about starting with MasterFinPeriod then doing a Left Join to GLHistory. You'd have to turn Null values into zero values.
GL Inquiries should be using GLHistoryByPeriod joined (twice) with GLHistory because of the way Acumatica optimises posting to the ledger.
Discussed before: