Filtering by a sum NL function

Questions and Answers asked by users to users, about how others use Jet Reports.

For additional "how to" information, please visit our online Knowledge Base https://jetsupport.jetreports.com/hc/en ... ofessional
Forum rules
The Jet Reports Community forums are not a guaranteed support medium. Official support for Jet Reports is available only through the support desk at http://support.jetreports.com. Staff responses to posts on the forum are not guaranteed.
joseph.castigliego
Posts: 2
Joined: Tue May 02, 2017 7:18 am

Filtering by a sum NL function

Postby joseph.castigliego » Tue May 02, 2017 7:28 am

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!

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 442
Joined: Wed May 26, 2010 3:40 pm

Re: Filtering by a sum NL function

Postby HPDeskJet » Tue May 02, 2017 8:47 am

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:
example1.png
(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.
-HP
_______________________
Jet 2015 Certified Trainer

joseph.castigliego
Posts: 2
Joined: Tue May 02, 2017 7:18 am

Re: Filtering by a sum NL function

Postby joseph.castigliego » Thu May 04, 2017 6:35 am

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.

mcemanuel73
Posts: 3
Joined: Sun Mar 12, 2017 5:22 pm

Re: Filtering by a sum NL function

Postby mcemanuel73 » Mon Jun 05, 2017 1:00 pm

I'm trying to do something similar to this. Any thoughts on how to do this?

mcemanuel73
Posts: 3
Joined: Sun Mar 12, 2017 5:22 pm

Re: Filtering by a sum NL function

Postby mcemanuel73 » Mon Jun 05, 2017 1:00 pm

I'm trying to do something similar to this. Any thoughts on how to do this?

rf_sbhr
Posts: 5
Joined: Wed Apr 29, 2015 10:28 pm

Re: Filtering by a sum NL function

Postby rf_sbhr » Mon Jun 12, 2017 2:52 pm

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


Return to “How do I?”

Who is online

Users browsing this forum: No registered users and 7 guests