0

Trial Balance Range of Accounts

Thomas Morales

May 16, 2018 16:02

Answered

Follow

 Hello I am trying to get a detail trail balanced over a range of accounts inside certain departments and I am in need of help of how to get a NL (rows) function to read a General Ledger accounts filter (4000..5000)  as for each account if amount exceeds zero. Hopefully to have the ability to just change  account numbers to any range and everything adjust to it. I would greatly appreciate the help.

Department 4

NL Row ( acct # 4005)

Nl Row (acct # 40007)

No Row (acct # 40008)

Department 5

NL Row ( acct # 4000)

Nl Row (acct # 40001)

No Row (acct # 40002)

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Harry Lewis May 17, 2018 16:58

  Hello Thomas.

  It appears that you want to create a Grouping Report where you list certain departments and then list certain G/L account balances that are somehow related to each department.

  Assuming that you are using Dynamics NAV and DEPARTMENT is one of your Global Dimensions, this would be fairly straightforward.

  The top part of our grouping would list out the possible values in our DEPARTMENT dimension, and the next level(s) would list the accounts we want and their balances - respective to the specific department:

  

  The Jet Function Wizard for the GL() function would look like this:

  

  and, once run, the report would look like this:

  

  If needed (as in your example), there could be multiple NL(Rows) functions under each department... we would just need to ensure that the ROWS=x value in our top-level function was extended to included all of them.  e.g., NL("ROWS=4")

  I hope that helps get you pointed in the right direction.

   

  Edited by Harry Lewis May 17, 2018 16:59
• 0
  
  Thomas Morales May 17, 2018 17:26

  Thank you first of all for replying and the help I greatly appreciate the assistances. Although how is it possible to be able to have a departments such as ADM to be filtered for only a certain g/l account to be able to see each transaction within a posting date inside that certain department. ( I hope this makes sense) but here is an example of what I am mean.

  16210

  ADM 

  16210     3/1/2018

  16210      3/5/2018

  PROD

  16210      3/2/2018

  16210     3/15/2018

  Sales

  16210   3/16/2018

  16210    3/28/2018

  G/L Account 16220

  ADM

  16220         3/8/2018

  16220        3/10/2018

  16220         3/11/2018

  and so on

   

  Thank you once again for the help

   

   

   

• 0
  
  Harry Lewis May 17, 2018 19:57

  Hello Thomas -

  It is certainly possible to add starting and/or ending dates to the GL function.

  You would need some way to determine what date to use.  So, in your example above, how do you know when to use the specific dates you list?

• 0
  
  Thomas Morales May 17, 2018 20:15

  I put a posting date lets say for march 3/1/2018..3/31/2018

   

  The issue I have is circular references towards my g/l account no, because I have it set as it spits out the g/l accounts that fit the filters  then within them I have it set up to spit out the departments then within the departments I have the transaction number or entry no inside that. I am having difficulty tracing a g/l account number to the entry number. I cant anchor the G/L Accts down because if I do then the all the accts that fit the filter will be reference off of the first one so all the department even in other G/L accts will be locked to the first one. As well if I don't anchor the accts down then #Value and error signs pop up in the picture below

   

   

  G/L Acct. 16220

          Department 04

                  Entry no. 654314

   

   

• 0
  
  Thomas Morales May 17, 2018 20:15

  Thank you for the help once again and sorry if I am not explain the situation well.

   

• 0
  
  Harry Lewis May 17, 2018 21:35

  Ah!  I think I understand the issue you mean.

  Whenever you are creating a Grouping Report - especially with more than just two levels - you want to ensure that any cell references within your function are to cells on the same row as your function..

  You can see a simple example of this in my first screen shot:

  

  My function in cell G4 does *not* reference the Department Code in cell D3.  Rather, I keep a copy of that data in column C.  Then, my function in cell G4 refers to cell C4 

  I keep column C hidden but I still use it.

  If I had more levels that I needed to ensure I could reference in the same way, I need more columns - essentially, one column for every grouping.

  Here's another example of this "workaround" technique:

  (you may have to open the image in another tab to see it larger)

  I have four total levels.  The first three (Salesperson, State, and Customer) are all copied in columns B,C, and D (respectively).

  My functions in cells I6 and L7 only refer to other cells that are on the same row... they do *not* attempt to refer to the salesperson code in F4, the State in H5, or the Customer in J6.

  I hope that helps.

   

  Edited by Harry Lewis May 17, 2018 21:37
• 0
  
  Thomas Morales May 18, 2018 14:48

  Yes thank you so much. I greatly appreciate all the help.

   

  Thomas M.