When working with the NL() function, you typically have the ability to apply multiple filters to refine the data you retrieve. A common use case involves using filters directly within the function, such as NL("Rows") or NL("Table"), to extract rows or create a table based on specific criteria.
However, situations may arise where you need to exceed this limit and apply more than the maximum 10 filters that Jet Reports allows. This is where the FILTERS= special filter comes into play. By using the FILTERS= argument, you can bypass the usual restrictions and add an expanded set of filtering criteria. It's particularly useful in complex reporting scenarios where precise and highly filtered data sets are required.
The FILTERS= argument accepts a string that contains the additional filters, formatted in a way that the NL() function recognises and can parse. This means that you can construct a more intricate query without being constrained by the filter count.
Let's look at an example where the 10 filter limit causes us a problem:
=NL("Rows","Customer",,"No.","C00001..C15999","Market Sector","ONLINE","Ship-to Code","*","Salesperson Code","PN","Customer Group","Tier 1","Export","True","Shipping Agent Code","*","Blocked","False","Tax Bus. Posting Group","*","Foreign Trade","EUR","Tax Liable","False")
The above will not produce results as there are a total of 11 filters. You can either look to see if there's a dimension that you can remove to get down to the maximum of 10 filters, or use the FILTERS= Jet function. Let's take a look:
=NL("Customer","Rows",,"Filters=",$B$2:$B$12)
Note that you have to list the Fields in one column and their corresponding Values in another. You may be thinking "I'll never need 10 filters on a table, that's crazy!", but the cool thing with this is that you can combine this with the LINK= Jet function where you connect tables together. In this case, there's a much higher chance that you'll find you'll be needing more than 10 filters and this is where FILTERS= will really help out.
❤️ Enjoyed this article?
Forward to a friend and let them know where they can subscribe (hint: it's here).
