Hello,
I am working outside of my skill set on a JET report (I am a SQL guy) that pulls data from Navision, from a table called “shipment ledger entry”. I need to sum up the Revenue, Expenses and Accruals “Amount” for each of the shipments in the table based on “First revenue date” and a date range provided by the user. I thought I got it when the numbers for “Revenue” tied perfectly to the Navision screens but when I try to sum up the “Expenses” using almost the same query, I am getting incorrect totals and just dont understand why. I would really appreciate some help if anyone can offer it. The query I am using in JET is;
=IF($M13=“False”,NL(“Sum”,“Shipment Ledger Entry”,“Remaining Amount”,“Shipment No.”,$J13,“Posting Date”,"<=02/01/2012",“Company=”,“30-Movers International”,“Posting Type”,“Expense”,“Exclude from Rev/Exp”,“False”),NL(“Sum”,“Shipment Ledger Entry”,“Remaining Amount”,“Shipment No.”,$J13,“Posting Date”,“01/01/2012…02/01/2012”,“Company=”,“30-Movers International”,“Posting Type”,“Expense”,“Exclude from Rev/Exp”,“False”))
$M13 is a test to see if the First Rev Data is within the Date Range provided by the person running the report. If it is within the date range it needs to sum all “Expenses” from the beggining of time to the end date of the Date Range using the Posting Date. If the FRD is outside of the Date Range it is to sum only the Expenses using the Posting Dates that are only within the Date Range.
this all seems to make sense to me but the numbers do not tie to Navision for the Expenses or Accruals. I must be missing something? Hopefully this makes enough sense to some NAV/JET guru to offer a suggestion or two?? Any help appreciated. I know Navision data is full of crazy flow fields etc and maybe this is a result of that? HELP!
Thanks,
K