0

subledger - open balance at a given date

Jet Reports Historic Posts

July 10, 2015 08:25

None

Follow

Hi,

I would like to create a subledger for my vendors and customers. I would like to present only open balances at a selected date (in the past).

I created the report with a flow filter on the "remaining amount" fields. this works fine but the report returns all lines which are closed and balance zero. The report become huge.

I then created a filter based on the field "open" -> "open"="true". this solves the problem but creates another one. if I select today the report date as 31st of May with this filter then is eliminates also all balances which were paid AFTER the report date. so the result does not show the tue balance as of 31st of May.

Does anyone have an idea how to solve this issue?

Thank you,
Gideon

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts July 14, 2015 21:18

  Hi Gideon,
  
  Try the formulas below, I've got something similar working in an AP Aging report.
  
  To list the vendors with open payables as at a specific date (where xxx is the literal date filter or cell reference to a date filter, such as "..30/06/2015"):
  
  NL("Rows","Vendor",,"Date Filter",xxx,"Net Change","<>0")
  
  To list the vendor ledger entries (where yyy is cell reference to vendor replicator above):
  
  NL("Rows","Vendor Ledger Entry",,"Vendor No.",yyy,"Posting Date",xxx,"Date Filter",xxx,"Remaining Amount","<>0")
  
  I have the second replicator nested under the first one and then use NF formulas to pull the data I want into the body of the report.
  
  Regards,
  Rob

• 0
  
  Jet Reports Historic Posts July 15, 2015 08:45

  Hi Gideon,
  
  Attached an example of an ageing list (can be run at a specific date) for vendors and customers. I deleted the name of the customer and documents in the reports.
  
  A part of the reports is based on custom functionality in Navision, so you probably would have to delete this (for example customer comments).
  Perhaps you can use these reports for reference.
  
  Best regards,
  Gerard

  • Examples Ageing lists.rar
• 0
  
  Jet Reports Historic Posts July 20, 2015 09:10

  Thank you for your replies. I did now manage to solve the problem with combination of the net change files, date filter and remaining amount fields.
  
  I used the following formula :=NL("Rows=4","Customer","No.","No.",Options!$F$11,"Company=",$I$2,"net change (LCY)","<>0","date filter",Options!$B$14)
  
  Gideon