Apologies if this has been covered previously, this is my first post.
I am trying to recreate the Aged Accounts Receviable report in a PHP script, I am able to query the SQL database to acces the tables Cust Ledg_ Entry and Details Cust_ Ledger Entry and it appears that beween the 2 tables I have the correct details, my issue is knowing when I use the values from the detailed table and when not to.
I have been told I need to check the balance at the time the report ends, so I have a report that ends 30th of April so I have the following SQL.
SUM(CASE WHEN([company$Detailed Cust_ Ledg_ Entry.[Initial Entry Due Date] < '2011-04-30' THEN ['Amount (LCY)] END) AS BAL
If the balance is 0 then the customer entry ledger info is not included in the report. This is the point I am going wrong, any help greatly appreciated.
Thanks
There is no need of customer ledger table, you can only use the detail table to build the report.
"Verify Solution" to post that helped you solve the problem.
Thanks for that, if I stick to the detailed table it doesnt appear to match the hard copy I have from Navision. I am using the following SQL statement for a report ending 30-04-2011 for the current month, most of the time this appears correct but in some cases it doesnt match,
SUM(CASE WHEN ([$Detailed Cust_ Ledg_ Entry].[Initial Entry Due Date] between '2011-04-01' AND '2011-04-30') THEN [Amount (LCY)] END) as currentm
Is this incorrect, am I missing something?
Thanks for you help.
Instead of initial entry due date why don't you try with posting date.
The values using Posting Date appear more wrong than when using the initial entry due date. Is there any point that I should take the value from the cust ledge entry table rather than the detailed cust entry table, a combination of them both appear the most correct.
1 example I have gets £205 not due from the detailed table but gets £602 from the cust ledger table, the 602 is the correct one according to the hard copy I have received.
Is there any example code you can give me for creating the report?
Thanks.
Have you looked at the code of the actual NAV report to see what fields it is using? I understand you're doing this in another language, but you should be able to get some information from the report code.
Matt Traxinger - ArcherPointMicrosoft Dynamics NAV 2009 Programming Cookbook
Do you have an idea to improve NAV? Submit it on I Love NAV
You can check navision reports like thr customer trial balance to check the fields used to calculate the balance amount.
Sorry, I am really new to this and have never seen Navision in use, how do I access the code behind the reports?
Thnaks
You need to design the report (Ctrl+F2) and in there use the C/AL code to see the code.
Dear All,
i want to display Aged Account Receivable report in fixed slot wise
0-3days 4-7days 8-12days 13-16days 17-21days above 21days
shall i need to hardcode it or we can do this by standard report.