0

Sum G/L Entries with no related record in Cust. Ledger Entry

Jet Reports Historic Posts

July 15, 2013 15:23

None

Follow

Hi all,

I am trying to sum the Amount in G/L Entries, based on several filters. This could be the formula:
=NL("Sum";"G/L Entry";"Amount";"DataSource=";$E$3;"Company=";$E$4;"Global Dimension 1 Code";$E$5;"Posting Date";$F$15;"G/L Account No.";$C24;"Gen. Posting Type";"<>Sale")

In addition to the above, I need to included a filter which will include only G/L Entries with no related record in the Cust. Ledger Entry table. I've tried different options, but can't get it to work. A "not equal to" on NL("Filter") does not work but it should be something like that…based on the "Transaction No."-field.

Any help is greatly appreciated. Thanks in advance.

Best regards,
Gerard

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts July 15, 2013 16:48

  Hi Gerard,
  
  I think you will need to use NL(Difference) for that. The idea is that you get all the G/L entries that match your filters and then you subtract from that set all those that have transactions in the Cust. Ledger Entry table. Unfortunately, this won't work correctly with an NL(Sum) because the records will be unique-ified which means you might lose some amounts; you will have to use NL(Rows) and then sum the results I'm afraid. It might look something like this:
  
  =NL("Rows",NP("Difference",NL("Filter";"G/L Entry";"Entry No.";"DataSource=";$E$3;"Company=";$E$4;"Global Dimension 1 Code";$E$5;"Posting Date";$F$15;"G/L Account No.";$C24;"Gen. Posting Type";"<>Sale"),NL("Filter";"G/L Entry";"Entry No.";"DataSource=";$E$3;"Company=";$E$4;"Global Dimension 1 Code";$E$5;"Posting Date";$F$15;"G/L Account No.";$C24;"Gen. Posting Type";"<>Sale";"Link=";"Cust. Ledger Entry";"Entry No.";"=Transaction No.")))
  Then you could get the Amount based on the Entry No. with an NL(First) function and sum the results. Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 16, 2013 06:42

  Hi Hughes,
  
  Thanks for your reply!
  I will give this a try. However, to be honest, due to the setup/layout of the report adding a sum on a NL("Rows") is not ideal.
  
  Keep you posted on my progress.
  
  Best regards,
  Gerard

• 0
  
  Jet Reports Historic Posts July 16, 2013 14:41

  Gerard,
  
  You can always hide the replicated rows by using Hide in that row in column A. It might also be possible to put the replicated rows on another sheet that is hidden when you run the report and just reference the sums. Just some things to think about.
  
  Regards,
  Hughes