0

General Ledger Detail report

Yuliya Baver

March 29, 2019 14:13

None

Follow

Hi,

I hope someone can help me. I’m new to working with Jet and Great Plains but have experience with Jet and Navision. I’m trying to write a report where I can pull details (all transactions) for a single GL acct. for a period of time.

In Nav this is what it will look like:

Highlighted cell has the NL function (screenshot next to line 15 is what I had in D12 for Navision).

How do I write the same report for GP? What table in GP corresponds with G/L Entry table in Navision?

Thank you so much for any help!

• Facebook
• Twitter
• LinkedIn

11 comments

• 0
  
  Harry Lewis March 29, 2019 14:26

  Hello Yuliya.

  Dynamics GP has a very different underlying structure than Dynamics NAV.

  You may find the file link to from the Most Common GP Tables For Reporting to be helpful.

  (note: the GL function can be your friend... a single function is able to locate G/L data in the various tables within GP)

  Edited by Harry Lewis March 29, 2019 14:27
• 0
  
  Harry Lewis March 29, 2019 14:28

  You may also want to take a look at the GL Function for Dynamics GP and the Videos for Dynamics GP (especially the GL section)

  Edited by Harry Lewis March 29, 2019 14:29
• 0
  
  Yuliya Baver March 29, 2019 15:13

  Hello Harry,

  Thank you for getting back to me! 

  I already realized that GP structure is very different than Nav...And I already read everything on GL function in GP and watched all videos I could find for GP...Don't know what is wrong with me as I can't grasp how to do this simple report...I send lots of reports to different departments where on one tab I have GL account listed with date, balance, budget and variance for different periods of time (no problems getting this report done), and I need now to have second tab where all details are listed for that GL account for the period of time. How does anyone report details for a particular GL account in GP using Jet? 

  Thank you

• 0
  
  Harry Lewis March 29, 2019 16:16

  If your period of time is not historical (that is, has not been closed within Dynamics GP itself), you may have good luck with GL11110 (Account Summary Master View). This view includes records from the Account Current Summary Master Table (GL10110) and merges information from the Account Master Table (GL10100).

  If your time period has already been closed, you would want to work with GL111111 (Account Summary History View) which includes records from the Historical Year Summary Master Table (GL10111) merged with information from the Account Master Table.

• 0
  
  Yuliya Baver March 29, 2019 16:38

  Thank you Harry!

  I understand that I have to use both: Current and Historical Master Tables in GP in order to pull data for the different periods of time. 

  I will keep trying and post solution as soon as I come up with it. I just can't believe it is so hard to do for such a easy task...

  Thanks again!

• 0
  
  Tracy Smith April 23, 2019 20:33

  Hi Yuliya,

  I need to create the same report as you inquired about and was wondering if you ever figured this out. If so, I would appreciate it if you could share your findings with me.

  Thanks,

  Tracy

• 0
  
  Yuliya Baver April 25, 2019 19:03

  Hi Tracy,

  I just got back to the office and saw your message.

  Yes, I almost got Jet report do what I need it to do :)

  So far I figured out how to run report for one (or more) GL acct to get details for a period of time (includes dates from Historical and Current transactions).

  Here is how I did it:

  Details in:

  C2: enter beginning date

  C3: enter ending date

  C4: =NP(“DataFilter”,C2,C3)

  C5: enter GL acct number (first 4 digits) that you need to get details for. Note: you can enter 1005|1006|1007 to get details for those 3 accts, etc

  C9: =NL("Filter","GL00105","ACTINDX","ACTNUMBR_1",$C$5,"ACTNUMBR_2",$C$6,"ACTNUMBR_3",$C$7)

  D12: =NL("Filter","GL20000","ACTINDX","TRXDATE",$C$4,"ACTINDX",$C$9)

  E12: =NL("Filter","GL30000","ACTINDX","TRXDATE",$C$4,"ACTINDX",$C$9)

  F12: = NL("Rows=6",NP("Union",$D12,$E12))

  G12: =NL(,"GL00105","ACTNUMST","ACTINDX",""&$F12)

  I12: =G12

  J12: =GL("Cell","Name","@@"&$I12)

  K13: =NL("Rows","GL30000",,"TRXDATE",$C$4,"ACTINDX",$F13)

  K14: =NL("Rows","GL20000",,"TRXDATE",$C$4,"ACTINDX",$F14)

  L13: = NF($K13,"TRXDATE")

  M13: = NF($K13,"REFERENCE")

  N13: = NF($K13,"DEBITAMT")

  O13: = IF($K13="",0,-NF($K13,"CRDTAMNT"))

  P13: = SUM(N13:O13)

  L14: = NF($K14,"TRXDATE")

  M14: = NF($K14,"REFERENCE")

  N14: = NF($K14,"DEBITAMT")

  O14: = IF($K14="",0,-NF($K14,"CRDTAMNT"))

  P14: = SUM(N14:O14)

  I7: = "For the Period of "&TEXT($C$2,"MMMM D, YYYY")&" through "&TEXT($C$3,"MMMM D, YYYY")

  I16 – O16 (merged cells): = I12&"     "&J12&"        Total:"

  P16: =SUBTOTAL(9,P13:P15)

  Here is what reports looks like after it run:

   

  Hope this helps!

  Yuliya

• 0
  
  Tracy Smith April 25, 2019 20:14

  Yulia,

  Thank you so much. I'm still having a little bit of trouble. If you don't mind, would you please double-check the NL formula you have listed in cell C9? I'm getting an error message related to ACTINDX cannot be blank.

  You have no idea how much I appreciate your help!

  Tracy

• 0
  
  Yuliya Baver April 25, 2019 20:23

  Hi Tracy,

  I checked and formula in C9 is correct. Make sure you have a * in cell C6 and C7. I just tried and took it out and it gave me the error you are talking about. Hope this fixes your problem.

  So welcome and I have a very good idea :)

  Yuliya 

• 0
  
  Tracy Smith April 26, 2019 16:38

  Yulia,

  I got it! Thank you so much!

  Tracy

• 0
  
  Yuliya Baver April 26, 2019 17:05

  So welcome!