This requires some setup (and it may already be set up at your company), but we find it to be much better than the built in report.Make a new query object in the object designer and compile and save it. (Query export attached as Open Customer Ledger Entries.fob.txt, just take of the .txt extension to import it, and screenshot of the query (that shows the filter on the ledger entries table to show only open entries) is Query Screen Shot.png)In NAV, make this query available over web services by going to Departments/Administration/IT Administration/Services/Web Services. There will already be a bunch of default objects there, and you need to add the query you just made and then make sure it's published. (Screenshot: Web Services.png). If you like, you can change the base URL and ports that are used here for this in the NAV Administration tool (Screenshot: Odata Services.png), but default NAV installations will have this already configured.The attached Excel workbook grabs the query results through this web services line via the OData URLs shown. Excel uses Power Query to get the information, and then we do some data transformations on it, and then report the results in the first sheet of the workbook. It won't work, right away, because you have to supply the URL for your company. In Excel, go to Data (tab) > Queries and Connections (group) > Queries and Connections (button). When you click that button, you'll get a fly-out window, which shows two queries that are used in the workbook. Double click Aged Accounts Receivable and you'll get a new Query Editor window that takes over Excel until you close it out.In the Query Editor, highlight the first step in the query (Labeled Source) and update the formula there, replacing the bolded text with the OData V4 URL from the NAV Web Services that you set up in the third paragraph (again, Web Services.png):
= OData.Feed("OData V4 URL From NAV Web Services", null, [Implementation="2.0"])Once you do that, you can preview the results, and hitting the Close & Load button will load that query data to Sheet 1. Clicking Data (tab) > Queries and Connections (group) > Refresh (button) will go and query that URL you made, get live results from NAV, and load them back in. The slicers up top, perhaps obviously, will allow you to filter certain lines away. For instance, you might not want to go after late payments until they're, say, 15 days overdue. We've added this slicer to keep some of the noise down.Lastly, modifying the Aging Period (Days) value from the 30 shown here, will change the size of the "overdue buckets" that are shown.
All it looks for are open customer ledger entries, and grabs just the "Remaining Amount" field, in terms of what dollars it totals up.We don't really deal with partial payments, so I'm not able to really get into that in any meaningful way. I'm not sure what is meant by "tie back to any date." The workbook simply calculates how many days the payment is overdue (which is tied to the Due Date field, of course), and goes from there. Were you able to get this working for your company? I think you might want to try and get it working and go from there. With partial payments, if that's information that's available in NAV somewhere, you can modify the query or the workbook to include that information and filter or slice on that.
If you've found this thread useful, dive deeper into User Group community content by role