Hi,
I've created a report listing the forecast (table name "PPOrder") vs. actual sales ("SEOrder") pulling data from a SQL db [Non-NAV]. My report will show a list of forecast and certain forecasted items had commited with SO documents.
Here's my Jet query:
=NL("Rows","PPOrder",{"FDate","FBillNo","FCheckDate","FInterID"},"FInterID",NL("Filter","PPOrderEntry","FInterID","FNeedDate",ForecastDate,"FInterID",NL("Filter","SEOrderEntry","FSourceInterId","FInterID",NL("Filter","SEOrder","FInterID","FDate","..16/11/2012"))),"FCheckDate",ApprovalDate)
Is it possible to apply date filtering only on those transactions with SO and list out those forecast with blank SO date?
For example, when I run the report I want to know the forecast vs. sales prior to 16/11/2012. So forecasted items with SO dated on 17/11/2012 onwards show not display on my report. Is it possible?
I applied above query and the report will remove all blank SO date as well but I want to display it out.
Any idea?
Appreciated and look forward to hearing from you.
thank you.
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi Michelle,
I'm not positive I understand what you mean, but I think you just want to filter for either blank or ..16/11/2012. In this case you can construct the filter like this I believe:=NL("Rows","PPOrder",{"FDate","FBillNo","FCheckDate","FInterID"},"FInterID",NL("Filter","PPOrderEntry","FInterID","FNeedDate",ForecastDate,"FInterID",NL("Filter","SEOrderEntry","FSourceInterId","FInterID",NL("Filter","SEOrder","FInterID","FDate","''|..16/11/2012"))),"FCheckDate",ApprovalDate)
Notice that this is 2 single quote characters (') not a double quote. Does this help?
Regards,
Hughes
Please sign in to leave a comment.