Hi everybody,
After fighting with the excel & JetReport i dont know what else can I try. Please help me!!
I want to show quantity from the sales line table (filter by a posting date and a salesperson) group by product and classified by Item category Code (each product has this code). The structure of the results should be:
Item category –> Quantity
Product1.1 –> Quantity
Product1.2 –> Quant.
Item Category 2 –> Quantity
Product2.1 –> Quantity
Item Category 3 –> Quantity
Produc 3.1 –> Quant.
Produc 3.2 –> Quant.
Produc 3.3 –> Quant.
.
.
.
I have two problems with my functions.
First I just want to show products and "Item categories" with Quantity on Sales lines.
Second. In the results it JUST SHOW ONE PRODUCT, for each item category!! :-(
My funcions are these: (I have translated them from spanish so " ; " should be " , ")
TO SHOW ITEMS CATEGORY CODES: NL FUNCTION ON D9
=NL("Rows=2";"Item Category")
ON E9
=NF($D9;"Code")
ON F9
=NF($D9;"Description")
ON G9
=IF(E9="";"";NL("Suma";"Sales Line";"Quantity";"Type";"Item";"Item Category Code";E9;"Document No.";NL("Filtro";"Sales Header";"No.";"Posting Date";$G$3;"Salesperson Code";$G$4)))
ON D10 :
=IF(NL("Rows";"Item";;"Item Category Code";E9;"Link=";"Sales Line";"No.";"=No.";"Link=";"Sales Header";"No.";"=Document No.";"Posting Date";"01/01/2012..01/31/2012";"Salesperson code";"008")="";"";NL("Rows";"Item";;"Item Category Code";E9;"Link=";"Sales Line";"No.";"=No.";"Link=";"Sales Header";"No.";"=Document No.";"Posting Date";"01/01/2012..01/31/2012";"Salesperson code";"008"))
ON E10:
=IF($D10="";"";NF($D10;"No."))
ON F 10
=IF($D10="";"";NF($D10;"Description"))
ON G 10
=IF(E10="";"";NL("Suma";"Sales Line";"Quantity";"No.";E10;"Type";"Item";"Item Category Code";E9;"Link=";"Sales Header";"No.";"=Document No.";"Posting Date";$G$3;"Salesperson Code";$G$4))
Thank you so much for your helps and comments!
Date
Votes
1 comment
-
Jet Reports Historic Posts Official comment Hi,
You cannot wrap an NL(Rows) function in an Excel IF statement or it will not replicate (this is why you only see 1 product per category). You should change your NL(Rows) function in D10 to something like this:=NL("Rows";"Item";;"Item Category Code";"@@"&E9;"Link=";"Sales Line";"No.";"=No.";"Link=";"Sales Header";"No.";"=Document No.";"Posting Date";"01/01/2012..01/31/2012";"Salesperson code";"008")
The "@@"& before the cell reference tells it to filter for blanks if the cell reference is blank (hopefully there are no items with a blank Item Category Code). Instead of using IF functions around your NL(Sum) and NL(Rows) functions, you should prefix your cell references with the "@@" like this.
You also never need to put IF functions around an NF function. If the NF function references a blank cell, it will also return blank, so this should not be necessary. Does this help?
Regard,
Hughes
Please sign in to leave a comment.