I need to build a Trial balance in SSRS from NAV.
There is a ‘Balance at Date’ flow field I could probably use for Opening Balances.
Here is this flow field code:
Sum(“G/L Entry”.Amount WHERE (G/L Account No.=FIELD(No.),G/L Account No.=FIELD(FILTER(Totaling))
,Business Unit Code=FIELD(Business Unit Filter)
,Global Dimension 1 Code=FIELD(Global Dimension 1 Filter)
,Global Dimension 2 Code=FIELD(Global Dimension 2 Filter)
,Posting Date=FIELD(UPPERLIMIT(Date Filter))))
Please, advise how this flow field could look in SQL.
It seems as it’s just a SUM of the account Amount up to the date. I’ll probably go with this.
Flow fields are not avaialbe in Sql becuase it is logical /calculated field
You need to write query to achieve the Flow field result.
Flow fields will not be available in SQL it will be populated at run time
First get the Acct no, Posting Date, Business unit code, Glbl Dim 1 and Glbl Dim 2 as input from user (you can get range of input too )
(select Amount from “G/L Entry” where
Global Dimension 1 Code = ‘User Input’ and
Global Dimension 2 Code = ‘User Input’ and
Business Unit Code = ‘user Input’ and
Posting Date between ‘User Input Date1’ and ‘User Input Date 2’ and
“G/L Entry”.G/L Account No. in
(Select “No.” from “G/L Account” where
“G/L Account”.Account Type = ‘total’ and
“No.” in (‘User Input Acct no’)))
as i dont have sql in my system the syntax may be different please adjust
Jerome Marshal. J
Thank you so much, Marshal, for your response.
It’s actually by departments and accounts our user want to see grouping with totals. I’d also need to show running totals for detailed (by entry) account balances grouped by departments and accounts. So, I’d need to show the running totals for balances. Do you have any advice how I could do it with the NAV data? I’ll probably need to post to SQL forum with the running balances question.
use " GROUP BY G/L Entry.Department Code, G/L Entry.G/L Account No. " at end so that this will give you the sum by group.
Jerome Marshal. J