Jet Reports functions explained: GL, NL and NF

There is a long list of Jet Reports functions in the documentation, and you can build a career on three of them. GL pulls general ledger figures. NL navigates a list - it fetches values, or the rows themselves, from any table. NF fetches one field from a record you have already found.

Understand what each returns, and the rest of the function list becomes variations on a theme you already know.

GL - general ledger figures

Use GL when the answer is a ledger number for an account or range of accounts over a period. It is the function behind almost every management accounts row, and it understands the concepts finance actually works in - account filters, a start and end date, and dimension filters.

Two details worth knowing on day one. GL returns the net change for the period you give it - leave the start date blank and you get a balance instead, which is what a balance sheet line wants. And budget figures are a different function: GLBudget, taking a budget name alongside the same account filter and dates. A budget-versus-actual row is therefore GL beside GLBudget, not one clever GL formula.

The normal pattern is one GL formula per report line, with the account codes down column A and dates driven by a cell at the top of the sheet, so changing one reporting date re-runs the entire pack. If you find yourself hard-coding dates inside individual formulas, stop and pull them out to a parameter cell - it is the difference between a report and a monthly rebuild.

NL - navigate a list

NL is the workhorse, because it reaches any table rather than just the ledger: customers, items, sales lines, jobs, ledger entries. What it returns depends on how you ask:

• A single value - a sum, a count, a first or last value matching your filters. Useful for a total in one cell.
• A list of values - for example every customer number matching a filter, expanded down the rows of your report.
• Rows - the important one. Rather than returning text, it returns a handle to each matching record, one per row. That handle is what NF consumes.

Filters are the other half of NL. Filter as tightly as you can, as close to the source as you can - a filter you apply in the formula is applied by Business Central, while a filter you apply afterwards in Excel means you fetched the data first and threw it away.

NF - navigate field

NF takes the record handle NL gave you and returns one field from it. On its own it does very little. Paired with NL it is what makes a wide report cheap: the row has already been located, so each additional column is a field read rather than another search of the table.

The link-column pattern - the one thing worth copying

This is the layout that separates fast reports from slow ones, and it is simple enough to adopt today.

Put a single NL in a hidden column - conventionally column A - asking for rows. Every visible column to its right is an NF pointing at that column. One lookup locates the record; every field after that is nearly free.

The anti-pattern is a full NL in every cell, each independently searching the same table for the same record. It produces identical output and it is the most common reason an inherited workbook takes twenty minutes to refresh. On a 500-row, six-column report that is 3,000 lookups instead of 500.

The status bar reports Report Runtime after every refresh. Note it before you change anything and again afterwards - it turns "it feels quicker" into a number you can show someone.

Rules of thumb that keep reports healthy

1. One NL per row, not per cell. The link-column pattern above.
2. Use GL for account balances, not a sum over ledger entries - GL is served by Business Central's own aggregates, while summing entries makes it read every one.
3. Total your replicated columns with NP, which aggregates what is already on the sheet, rather than sending another query back to Business Central.
4. Filter at the source. Never fetch a whole table to filter it in Excel.
5. Parameterise dates and companies in cells at the top, never inside formulas.
6. Prefer keys and indexed fields in filters over free-text matching.
7. Label the hidden column. The next person needs to know it is load-bearing.
8. Record the runtime in the workbook, so a slowdown is visible early.

When the functions look broken but are not

Most "the formula is wrong" calls turn out to be one of four things:

• Errors across a whole row - the link column is broken or was deleted, so every NF has nothing to point at. Fix the row, not the columns.
• Blank results with no error - the filter matches nothing. Test it against the table in Business Central before doubting the function.
• Nothing runs at all - usually connection rather than formula. Our guide on provider cannot be found covers that family of errors.
• Correct but slow - almost always the per-cell NL anti-pattern above.

A workbook built on these three functions, laid out with a link column and parameterised at the top, is one that the next person in your seat can pick up and change. That maintainability is worth more over a few years than any individual clever formula.

Frequently asked

← All guides