Hi I'm trying to write a report that will show the number of orders that were entered for a particular day, regardless of whether they are open or invoiced.
I was able to get my list of sales orders (both invoiced and open) and realized I didn't include in my formula that I want the sum of the amount of each sales line for each sales order.
The formula I was trying to use is : =nl("rows","sales invoice header","order No.","order date",option!$D$3,"Salesperson code",option!$D$4)=nl(""Sum"",""sales line"",""Amount"",""Document No."",NF(""order no.""),""order date"",option!$D$3,""salesperson code"",option!$D$4) and I'm getting an Excel error.
In order to save my report to send I needed to take out one of the quotes surrounding "Sum".
Attached is the report….any tips would be appreciated. Thanks! Jen
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi
Please find below the way I would do it :
To calculate amount of Open Orders Sales lines :
=NL("Sum";"Sales Line";"Amount";"Document No.";NL("Filter";"Sales Header";"No.";"Document Date";Option!$D$3;"Salesperson Code";Option!$D$4))
To calculate amount of Archive Orders Sales lines :
=NL("Sum";"Sales Line Archive";"Amount";"Document No.";NL("Filter";"Sales Header Archive";"No.";"Document Date";Option!$D$3;"Salesperson Code";Option!$D$4))
Either you keep it in 2 separate fields to get the ratio of the invoiced amount and the "still to be invoiced" amount or you just sum the 2 of it in one cell.
=(NL("Sum";"Sales Line";"Amount";"Document No.";NL("Filter";"Sales Header";"No.";"Document Date";Option!$D$3;"Salesperson Code";Option!$D$4)))+(NL("Sum";"Sales Line Archive";"Amount";"Document No.";NL("Filter";"Sales Header Archive";"No.";"Document Date";Option!$D$3;"Salesperson Code";Option!$D$4)))
Hope this helps… Filter function is very helpful to link 2 tables !
Best Regards -
Jet Reports Historic Posts Thanks! That gives me the total for all of the open orders. If I wanted to list the sales lines of each individual sales order would I reference the Sales order number and cell number?
-
Jet Reports Historic Posts Hi
I did not understand your 2nd question exactly perhaps you could describe a little bit more your need because if you just need a list then you just have to write the "classic" formula :
=NL("Rows";"Sales Line") and then display the fields the way you want with NF() function.
The only thing you could need is to sort on Document No..
For this you have to write down the formula this way :
=NL("Sum";"Sales Line";;"+Document No.";"*")
The "+" says that you want to see the list ascending.
I wouldn't suggest to list the datas in the same sheet you count or sum. -
Jet Reports Historic Posts This report has developed into something more than I had originally needed.
I'm looking to list out the sales orders for a particular "order date", along with the corresponding sales line/invoice line items that just show the line amount and product code with each sales order having a subtotal.
Thanks- -
Jet Reports Historic Posts Hi
In that case I guess the post is closed for the 1st question.
You can use the Report Builder functionnality to do what you want.
You have some tutorial on interent which show you how to use it :
http://www.youtube.com/watch?v=k9yUrvNNNJs
Bon courage !
Please sign in to leave a comment.