is there anyway to pull a report using Jet from multiple tables?
here is my dilemma.
I need to get this Data from the Sales Header Archive:
NL:
=nl("rows","Sales Header Archive",,"Sell-to Customer No.","EASTE0","Order Date","01/01/07..12/31/07")
then I need some Info from the Sales Line Archive, that uses the Header info as a filter..
basically what I am trying to do is look at several specific customers, during specific time frames and see exactly what items they ordered..
the Sales Header includes the Sell-To Customer Info, and the the Date Filters, and the Sales Line includes the Item No. and Line Amount..
so I need these 2 tables linked in the same worksheet in Excel.
is there a way to do this using Jet Reports?
or is Jet limited to pulling data from one table at a time?
0
Comments
yes I can do that, but i need the data for the Sales Line to reflect the date filter and the Order Numbers..
neither of these items are on the Sales Line, only on the Sales header..
"Sales Invoice Header"."No."->"Sales Invoice Line"."Document No."
http://www.BiloBeauty.com
http://www.autismspeaks.org
yes that is very helpful thank you..
but I am still stuck on the order date..
it is not on the Sales Line.. and I need to filter on it.
here's the senerio:
I have several customers that have contract that require them to buy X number of items per month and spend X number of $ per month..
I have several that have not kept their end of the contract, so I need a report to prove to them that they only ordered Y number of Items not X and they only spent Y $ not X $ in a given month.
#10048
The Customer Tab lets you filter on anything you have in the table (say "On Contract" or however you specify it).
The Value Entry tab lets you filter on Item No if you wish and/or Posting Date.
Then you can see what they bought (Cust) & how many(QTY) & how Much ($$) for whatever period (Date).
Plus you can always tweak it to you needs.
http://www.BiloBeauty.com
http://www.autismspeaks.org
yes that gives Invoiced data..
I need order data..
I need to filter on the specific Customer, then the Order Date, then the items they purchased and the total price of said Item.
the Invoiced amounts are not accurate for this report as they factor in a discount % for early pay under the NET30 agreements.
I need to see
Sales Order # by Customer Price Group, for Version 1 of the order, resulting in Total dollars ordered for a given period.
For example;
=NL("Rows","Sales Line Archive",,"Document No.",NL("Filter","Sales Header Archive","No.","Sell-to Customer No.","EASTE0","Order Date","01/01/07..12/31/07")
The NL("Filter") creates a string of values (in this case "Document No."'s) and passes it to the NL("Rows"..."Document No.") as a filter.
http://community.jetreports.com
Thanks you
this is perfect, this is what I could not figure out, how to run 2 NL statements in the same formula...