This is the fourth post in an ongoing series that explores the differences between Jet Reports‘ Jet Express for Excel and Jet Professional, and whether upgrading is the best decision for your organization.
Jet Express for Excel only includes the GL and NL functions, so the NF function will be completely new to Jet Express for Excel users. This function can only be used in conjunction with the NL function for freeform reporting – it cannot be used as a standalone function.
The NF function usually serves a very specific purpose, it is used to return a field based on a record key. What does that mean? The quick answer is that it saves you a lot of typing and allows you to use simple and reusable formulas in your reports. The NL performs the main lookup function in Jet Professional, and the NF function is used to show detailed data based on the main lookup.
Here is an example using NL on its own to return a list of customers with sales in 2016:
The first function creates a list of rows from the Customer table with a unique customer No. with Sales > 0 during 2016.
The second and third functions do another lookup on the customer no. returned on the first function, to look up other corresponding information on the customer, including the Customer Name and the Sales amount in 2016. Note that the third function for the Sales amount needs to have the Date Filter repeated so that the correct Sales amount for 2016 will be calculated, otherwise the total Sales for all time will show for each customer listed.
Now let’s look at another example of a way to create the same list using the NF function:
The first function is very close to the example above, with one exception. If you look closely at the formula, you will see that the “Field” parameter is left blank. What this does is create a “record key” – a way for Jet to query and cache all of the information from the Customer table for each record in the lookup. This creates the exact same list of customers as above but now it is easier to return the additional information on each customer that matches the filters.
This will return something that looks similar to this (your database name and company will be different):
Because this record key on its own isn’t useful to people using the report, it is normally hidden using a “Hide” tag in the column (for this report example, you would just type “Hide” (without the quotes) in cell B1).
The next functions are where the NF function comes in. They all refer to the record key and then a specific field in the Customer table. This is a huge time saver when writing reports because the same function can be copied over and just the field name is changed in the formula. This is especially useful when you have a report that requires many fields from the same table. For flowfields (such as the Sales ($) field), the Date Filter still needs to be used in the NF function to ensure that the correct results are calculated. For other types of filters, (e.g. a list of customers for a specific posting group), the filter does not need to be repeated in the NF function.
The two report designs will return exactly the same list, but the one using NF is a much more efficient report.
Not only does using NF make report design more efficient, the other benefit is that this method can also make reports run faster. The reason is that each formula in a report that requires a lookup to your database (in other words, each NL function you use), requires a separate database query. Each database query takes more time when generating the report results. In a simple report such as the example above, this won’t make much of a difference, but for a more complex report with many fields, this can make a significant difference in report runtime.
There is one more use for the NF function. Use it as a nested function within a NL function to compare the results of two fields against each other and to filter the results. As this gets a little more complex, please refer to the Jet Reports Knowledgebase for more information and examples on this subject. Note that this only works with Microsoft Dynamics NAV databases at the current time.
In summary, the NF function is only available with Jet Professional and it allows you to have much more efficient reports in both design and performance. This function is one of the many reasons that upgrading to Jet Professional may be a good value for your organization.