0

GL detail down to Purchase invoice line level

Jet Reports Historic Posts

April 30, 2014 14:46

None

Follow

Hello,

We are trying to get to grips with Navision and Jet reports and one major problem we are having is the high level of consolidation on the GL.

For example a 100 line purchase order (on the Purch. Inv. Line table) becomes a 1 line entry on the General ledger - for example with a description of "Invoice PI0001555".

For analysis purposes this is too high level and I need to be able to build a report that will give me the detail behind that 1 liner. I.e. we need the GL detail down to Purchase (or sales) invoice line level.
I’ve tried to build something but with little success as I need to retain both the general journal line item detail from the GL and effectively expand those 1 line Invoices to show all line item detail.

Can anyone help point me in the direction of how we can do this through Jet?

Thanks,
Andrew

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts April 30, 2014 20:18

  Hello Andrew -
  I use Navision myself, although our database has certain customizations that do not appear in all versions. So I'll try to help out - but we may have some difference in terminology.
  
  I would suggest building a report using the NL(ROWS) command - with a second NL(ROWS) following that
  
  So your first line would be something like:
  in cell C6: =NL("ROWS=2","INVOICE HEADER","INVOICE NUMBER","DATE",date filter reference)
  
  This would be immediately followed by a second ROWS command, but this time you are going to look in the INVOICE LINE table
  In cell C7: =C6 (copying the invoice header from the row immediately above)
  in cell D7: =NL("ROWS","INVOICE LINE","LINE NUMBER","INVOICE NUMBER",C7)
  
  Use the D7 cell as your reference point for the data off of the invoice line, note that this is not a traditional record key, so you will need to use NL(FIRST) to retrieve the data in each area, not the NF record key command.
  
  What will happen is that the first NL(ROWS) will replicate the line it is on and the following line, and the second line will replicate for each line in the invoice. Once the invoice is fully listed, the first NL(ROWS) will then report the next appropriate invoice and so on.
  
  ~ Heather

• 0
  
  Jet Reports Historic Posts May 01, 2014 14:31

  Thanks Heather - I will try this to see what it returns, It looks very similar to what I am already doing - However I'm trying to combine the invoice line table this with the G/L entry - i'e so I my total combines all general journal line items as per the GL with the posted invoices by line item rather than consolidation. I'm returning one or the other ok but combining 1 report to do both is proving quite difficult
  
  Thanks,
  Andrew

• 0
  
  Jet Reports Historic Posts May 01, 2014 14:56

  if you're still having issues, please upload a sample so that it can be reviewed, there may be issues that I'm not aware of in your output.