Jet Express vs Professional Part Five

Jet Express for Excel vs. Jet Professional: Should You Upgrade? Part 5 – NP: More Complex Functions

Posted By

This is the fifth 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 like the NF function discussed in my last article, the NP function will be completely new to Jet Express users. NP is a utility function that can be used on its own or in conjunction with other Jet functions. This function has several different parameters that have dramatically different uses and it is the function that makes Jet Professional quite versatile as a report writing tool.

Since there are so many parameters for this function, I won’t go into detail on every single one. Let’s begin with the simpler NP parameters since these are very useful and are commonly used by beginners using Jet Professional.

NP “Eval”: This is a handy function that assists with report design and functionality. By design, Microsoft Excel likes to constantly recalculate formulas, which allows the formula result to update automatically. This can interfere with Jet report design when you don’t want it update constantly, especially in the case of volatile functions (such as “=TODAY()” and “=NOW”). The “Eval” parameter allows you to put another Excel formula inside of a Jet formula so that it will only calculate when the report is run. This is great for volatile functions and also for off-sheet cell references, preventing them from slowing down your reports.

NP “DateFilter”: Microsoft Dynamics NAV users will be used to using date filters within NAV, where you have a start and end date separated by “..”. The date filters in Jet work the same way. This is handy because in some Jet functions you can only enter a date filter once so it is easiest to use a date range in your formula. The dateFilter function will create the date filter for you using a start and end date.

NP “Companies”: Usually when you work with a Jet Report, you only use one company at a time. However, there may be circumstances where you want to list the companies in your database, either to use as a list within the report or as a Report Option, which this function allows you to do.

In addition to the simpler formulas above, this function also works with arrays (lists of data values) so it is a function that advanced Excel users will particularly appreciate. If you aren’t familiar with working with Excel arrays, then I would suggest sticking with more simple Jet functions at first. Eventually you might want to get into some more complex functionality for your reports and this is where arrays can be very useful.

An example of an array could be a list of numbers, such as customer numbers or any other number you need to use. It can be particularly useful to compare two lists of numbers. For example, say you have one list of customers with a sales invoice in 2016 {Array 1}, and another list of customers with credit memos in 2016 {Array 2}. NP will allow you to work with both arrays to display information without duplication.

Array 1: {C1000, C2000, C3000, C4000, C5000}

Array 2: {C4000, C5000, C8000}

Imagine what questions you might want to ask:

  • NP “Difference”: Which customers had Sales Invoices but no Credit Memos in 2016? RESULT: {C1000, C2000, C3000}
  • NP “Union”: Which customers had either a Sales Invoice or Credit Memo, without duplicating the customer number? RESULT: { C1000, C2000, C3000, C4000, C5000, C8000}
  • NP “Intersect”: Which customers had both a Sales Invoice and Credit Memo in 2016? RESULT:

{C4000, C5000}

You can also create arrays using the NP “Split” function. For more information on working with Arrays in Jet Professional, read the Jet Reports Knowledgebase article here.

Where the NF function adds efficiency, the NP function in Jet Professional adds utility. A full list of all of the NP parameters can be found here in the Jet Reports Knowledgebase. As you become a more advanced Jet Report Designer, this function becomes very useful and important. There are many uses for this function, and it far exceeds the capabilities of the simple functions provided in Jet Express for Excel. If you have anything more than very simple reporting requirements, this function alone may be a good reason to upgrade to Jet Professional.

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing