jet report novice, need some assistance

mr._incredible
mr._incredible Member Posts: 33
edited 2008-01-21 in NAV/Navision Classic Client
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?

Comments

  • stamron
    stamron Member Posts: 32
    You can pull info from as many tables as you want. They key, just like any database, is to find a common key between the two tables. In this case it would probably be the Customer No. Use the number from the formula you wrote to look up your next piece of information.
  • mr._incredible
    mr._incredible Member Posts: 33
    stamron wrote:
    You can pull info from as many tables as you want. They key, just like any database, is to find a common key between the two tables. In this case it would probably be the Customer No. Use the number from the formula you wrote to look up your next piece of information.

    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..
  • Savatage
    Savatage Member Posts: 7,142
    These link
    "Sales Invoice Header"."No."->"Sales Invoice Line"."Document No."
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • mr._incredible
    mr._incredible Member Posts: 33
    Savatage wrote:
    These link
    "Sales Invoice Header"."No."->"Sales Invoice Line"."Document No."

    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.
  • Savatage
    Savatage Member Posts: 7,142
    Have you tried the Customer/Item Statistics Report?

    #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.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • mr._incredible
    mr._incredible Member Posts: 33
    edited 2008-01-03
    Savatage wrote:
    Have you tried the Customer/Item Statistics Report?

    #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.


    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.
  • mr._incredible
    mr._incredible Member Posts: 33
    Using the Sales Header Archive and Sales Line Archive…


    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.
  • mr._incredible
    mr._incredible Member Posts: 33
    bump
  • shermang
    shermang Member Posts: 3
    Yes, you can do this by using the NL("Filter") function.

    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.
    Sherman Glass
    http://community.jetreports.com
  • mr._incredible
    mr._incredible Member Posts: 33
    shermang wrote:
    Yes, you can do this by using the NL("Filter") function.

    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.


    Thanks you

    this is perfect, this is what I could not figure out, how to run 2 NL statements in the same formula...

Categories