Is there any thorough documentation on the usage of NL("Link") or the FilterParameter "Link="??
I've seen the few references in the Knowledge Documentation and answers to several questions, so I think I understand the syntax. But from what I've been reading, NL("Link") is supposedly faster than NL("Filter") and the recommended way to go to filter on a joined table.
My limited experience in trying to use NL("Link") is that it's performance is horrible, so I'm certain that I'm not using it properly.
I'm trying to link several tables with their own filters. Trying to do this in a single NL function was an issue because I used over 10 filter parameters. So I tried to break things out to separate the Link= into a separate NL("Link"), but it runs forever compared to simply using an NL("Filter"). A couple of specific questions about the usage:
1.) When creating an NL("Link") is there a recommendation on it's position relative to the NL(<function>) that will reference the NL("Link")? Doesn't seem to make a difference, but I wasn't sure. For example, assume NL("Link") in D4 and an NL("Rows",…,"Link=",D4) in E4 that references D4. Is there a performance difference if NL("Link") is in D4 or F4?
2.) Does NL("Link") replicate? I saw some examples of it being used where it there was a NL("Link") in F8 and then there was an NL("Rows", …, "Link=", $F8) in E8. Since $F8 was not referenced as $F$8, that's what made me curious about NL("Link") replicating. What would be the difference in the result of NL("Rows", …., "Link=", $F$8)?
3.) What I'm trying to do is fairly complicated so I'm probably trying to tackle too many obstacles while trying to understand NL("Link"). Everything works with NL("Filter") but I need to add another nested layer of filtering that makes the report run for hours instead of minutes when using an NL("Filter") so I was hoping NL("Link") may solve my performance issues. Depending on the responses to these questions, I may attempt to articulate the details of my report to seek advise, but if someone can help me understand NL("Link") a little better than it may not be necessary. But it definitely doesn't seem as intuitive as the NL("Filter") IMHO.
Thanks,
Sam
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Sam,
There are a couple of KB articles that discuss this
Filtering Based on Data from Another Table
http://kb.jetreports.com/article/AA-00494/0/
Using Link=
http://kb.jetreports.com/article/AA-00723
For performance -
NL("Filter") should be used to filter one table based on data from another table that is of smaller or equivalent size
"Link=" should be used to filter one table based on data from a larger table
You might need to look at how your data is keyed in NAV as well. If you're trying to filter on fields that aren't keyed, you can get bad performance.
Steve -
Jet Reports Historic Posts Thanks for the reply Steve.
I had not seen the first KB article but I did see the second one and other similar articles.
So is it safe to say then, that the example provided in that article (http://kb.jetreports.com/article/AA-00723) is a BAD example of using Link=, since it's filtering data from a Smaller Table (Sales Invoice Header) and in that scenario a Filter should actually be used. That helps to explain a lot.
I had not seen any information on specific use cases for each. I had only seen blanket statements that Link= was better performing than Filter.
Thanks,
Sam -
Jet Reports Historic Posts Hi Sam,
In the example in the linked article, Sales Invoice Headers are being filtered by results from the Sales Invoice Line table. The Sales Invoice Line table is larger than the Sales Invoice Header table, so Link= is the right way to go.
The question of whether Link= or NL(Filter) is faster is a pretty complex one actually. I think Link= tends to be faster in many more scenarios than NL(Filter) which is why that generalization is made. The relative size of the 2 tables isn't as important as how large the set being retrieved from the filter table is (the table in the NL(Filter) formula or the one after the Link=). In the example of simple linking provided in the article, you could convert the link= to the following NL(Filter) function:=NL("Filter","Sales Invoice Line","Document No.","Type","Item")
This would obviously produce a very large set. So from each result of the Sales Invoice Header table, you would have to compare the No. to the results in this set to see whether to return that record or not. The comparison would take a long time because it would have to look through a large set of data. This would make the NL(Filter) way of doing this very slow. With Link=, for each record from the Sales Invoice Header, Jet would do a subquery against the Sales Invoice Line and if the subquery returned a result, then that record would be returned. In this case, the subquery would be pretty fast, especially if you have a key to use which includes the Type and the Document No., so Link= is almost certainly faster.
The case where NL(Filter) is faster would be where the set returned by the NL(Filter) was only a few items, say a dozen or less. Then it's probably faster to compare each record being returned to those 12 items than to do a subquery to a different table. Of course, as Steve said, how fast the subquery works also depends on what keys you have on that table and what filters you are applying, so this can have an effect as well.
As you can see, it's a pretty complex question to answer and really the only way to know for sure in a specific case is to test the formulas in question (maybe in a different workbook from the rest of the report) and see in your specific case whether NL(Filter) or Link= is faster. Fortunately, it's fairly easy to do the conversion back and forth between NL(Filter) and Link= so it's usually not too hard to test. I hope that helps!
Regards,
Hughes -
Jet Reports Historic Posts Thanks for the update Hughes. They actually updated that KB article (on 12/23) after I posted this article and in response to support tickets that I submitted. But I appreciate you taking the time to respond. I understand now the difference better and the appropriate use cases.
Thanks,
Sam
Please sign in to leave a comment.