Hi!
I think you might be having a cell referencing problem in your sort by sum formula. You are referencing $F46 for Posting Group. In a normal formula, this cell reference will automatically update as the row is copied down. However, the sort by sum formula is just a string to Excel, and the cell reference won't be updated while it is inside a string. So you need to change your sort by sum formula to put this cell reference outside the string like this:=NL("rows";"Sales Invoice Line";;"Sell-to Customer No.";$H$7;"Document No.";"<>FVB*&<>COR*";"Type";"Item";"Amount";"<>0";"Quantity";"<>0";"Posting Group";$F46;"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Type"";""Item"";""Sell-to Customer No."";$H$7;""Document No."";""<>FVB*&<>COR*"";""Amount"";""<>0"";""Quantity"";""<>0"";""Posting Group"";"""&$F46&""";""No."";NF(;""No.""))";"*")
You could do the same thing with $H$7 although I don't think you need to since it is an absolute cell reference and won't change anyway. Does this help?
Regards,
Hughes
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Ok, i seem to have found it now.
End result=NL("rows";"Sales Invoice Line";"No.";"Sell-to Customer No.";$H$7;"Document No.";"<>COR*";"Posting Group";$H$19;"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Sell-to Customer No."";"""&$H$7&""";""Document No."";""<>COR*"";""Posting Group"";"""&$H$19&""";""No."";NF(;""No.""))";"*")
Seems the following parts were essential in the end, wouldnt work at all without it.
The, if the first field doesnt display by No. jetreports wont know to show only One example of the highest selling item.
It seems those """& &""" signs were also a necessecity.
Thanks very much for the help! -
Jet Reports Historic Posts Thx For the Reply, i will try this out as soon as i can.
I got around to trying your suggestions but to no avail, i also tried moving the posting group reference to a location that could be absolute, as it doesnt really need to be changed automatically anyways. But this also does not help. Right now it is sorting by "No." as opposed to "Amount". Ive made a few changes here and there to the code so ill repost it.=NL("rows";"Sales Invoice Line";"No.";"Sell-to Customer No.";$H$7;"Document No.";"<>COR*";"Posting Group";$H$19;"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Sell-to Customer No."";$H$7;""Document No."";""<>COR*"";""Posting Group"";"""&$H$19&""";""No."";NF(;""No.""))";"*")
Ive tried creating a reference field and sorting by that in the hope that it wouldnt sort by No. anymore, now it does seem to sort by Amount, which is good, just that it seems to bring up a line for every single result of the reccord it find with the same Item No. which is not good , cause i just want one.=NL("rows";"Sales Invoice Line";;"Sell-to Customer No.";$H$7;"Document No.";"<>COR*";"Posting Group";$H$19;"-=NL(""Sum"";""Sales Invoice Line"";""Amount"";""Sell-to Customer No."";$H$7;""Document No."";""<>COR*"";""Posting Group"";"""&$H$19&""";""No."";NF(;""No.""))";"*")
Please sign in to leave a comment.