Hello,
Trying to build a report that will show all items and item variants with a quantity <0 by location.
I'm stuck on filtering by a sum NL Function from the item ledger entry table.
Currently I'm bringing in the location code "=NL("rows=4","Location","Code","Code",$C$3), followed by the variant "=NL("rows=3","Item Variant Registration","Variant","Location Filter",$I12), then I would like to show all item no.'s in that location, with that variant who's quantity on hand is less than 0.
Cannot get the nested NL fn filter to work properly. I tried "'=NL("Rows","Item","No.","=NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Location Code"",$I12,""Variant Code"",$H12)","<>0"), but with no success. Know I am doing something incorrect.
Any help is appreciated!
Thanks!
Date
Votes
8 comments
-
Jet Reports Historic Posts Official comment Thanks for all of your responses, but I was able to figure it out.
Everyone should refer to this link for step-by-step help on this topic: https://jetsupport.jetreports.com/hc/en-us/articles/115006132828-Sort-by-Sum-or-count-with-Dynamics-NAV -
Jet Reports Historic Posts When dealing with nested replicating functions, it can be challenging to get cell references just right.
The easiest way I know of is to ensure that your final cell references are always on the same row as your function. This can be accomplished by using a technique that is commonly referred to as a "cell workaround".
In this technique, you have one or more hidden columns that copy the values you need so that they appear on the same row as the function that needs them.
Here's a really rudimentary example:
(click image to enlarge)
I'm listing some type of category (in this case it just happens to be my Global Dimension 1, but it could be just about any applicable field). Under that, I'm listing locations that are related to that category. Then, under that, I'm counting how many records match both the category and the location.
I'm copying the category and location codes to columns C and B, respectively.
By copying the needed values, my NL(Count) function can reference cells on the same row at the function itself.
I hope that helps get you started. -
Jet Reports Historic Posts Hello,
Thanks for your response!
So I'm not really having an issue with the rows functions, but I'm having trouble filtering Items and their Variants by a sum of the item ledger entry quantity field.
Basically, I'm trying to find all item numbers (which may or may not have a variant), who's inventory per location is <0.
So I'm doing the location function as rows=3, the item no. function as rows=2 and the variant code as rows. My issue is that we have a massive amount of item no.'s and the report is taking forever to run.
So, if there is a way to filter the items by a function where the sum of the item ledger entry quantity is <0, by location, by variant that's what I'm looking for.
I know you have to quote the sum function of the item ledger entry quantity, but not sure how to get it to work as a filter, and not sure which nested function to place the filter. -
Jet Reports Historic Posts I'm trying to do something similar to this. Any thoughts on how to do this?
-
Jet Reports Historic Posts I'm trying to do something similar to this. Any thoughts on how to do this?
-
Jet Reports Historic Posts Hi Joseph,
In case you are still trying to figure this one out, maybe see if this would work:
=NL("Rows","Item","No.","No.",NL("Filter","Item Ledger Entry","Item No.","Location Code",$I12,"Variant Code",$H12,"Quantity","<>0")
Rob -
Jet Reports Historic Posts Is it not the link function you want to the item ledger entry to filter on the result
=NL("Rows","Item","No.","Link=","Item Ledger Entry","Item No.","=No.","Remaining Quantity","<>0","Location Code","*","Variant Code","*")
Or use a flowfield on the item table to show inventory at with variant and location filter -
Jet Reports Historic Posts Hi
Please share the final report if possible
Thanks and regards
Ravi - Hong Kong
Please sign in to leave a comment.