The NL(“Lookup”) function in Jet Reports makes it easy to add Report Options to your Excel reports. With Lookups, report users (both Designers and Viewers) can select filters from a list of available values instead of typing them manually. This ensures accuracy, saves time and makes reports much more user-friendly.

 

What is the NL(“Lookup”) Function?

 

The NL(“Lookup”) function lets you pull a list of values directly from your database or from a predefined list, and display them as options in your report. Users can then choose the filters they want to apply.

 

This is especially useful when creating dynamic reports where users may need to filter by customer, salesperson, account number, region or other fields.

 

Common Use Cases of NL Lookup

 

1. Simple Field Lookup

The most basic use of NL(“Lookup”) is pulling values from a single field in a table.

Example: To display customer numbers from the Customer table:

 

👉 The resulting lookup shows only customer numbers, with the field name as the column header.

 

2. Multiple-Field Lookup

You can display more than one field to give users more context when selecting values.

Example: Display Customer No., Name, and State:

 

👉 Users see multiple fields, but only the first field (Customer No.) is returned when a value is selected.

 

3. Custom Column Headers

Want friendlier names instead of database field names? Use Headers= to customize column labels.

Example:

 

👉 Column headers now display as “Cust. No.”, “Cust. Name”, and “Cust. State”.

 

4. Hard-Coded Lookups

Instead of pulling values from the database, you can define your own list of values.

Example: Display N, S, E, W for directions:

 

👉 Great for fixed lists like regions, codes, or options not stored in your database.

 

5. Cell Reference Lookups

Instead of hard-coding values, reference a range of cells in Excel.

Example:

 

👉 This makes Lookups flexible and easy to maintain just update the cell range.

You can also expand this method to multiple columns, using arrays for column headers.

 

6. Lookups Filtered by Other Lookups

You can chain Lookups together so one depends on another.

Example:

• First Lookup: Select a Salesperson Code.

• Second Lookup: Display only customers linked to that salesperson.

👉 This allows cascading filters for more precise report control.

 

7. ScanLimit Option

By default, Lookups scan up to 1,000 records (as set in Jet Application Settings).

If your dataset is larger, use ScanLimit= to increase the number of scanned records.

Example:

👉 This lookup will return values from the first 5,000 records.

 

8. SmartLookup Option

Normally, NL(“Lookup”) only returns values actually present in the table.

With SmartLookup=TRUE, Jet Reports will display all possible values for option fields, even if they don’t appear in the table.

Example:

👉 This ensures users see the full list of valid values.

 

Why Use NL Lookup?

 

• Makes reports more user-friendly with dropdown-style filters.

• Reduces typing errors by providing ready-made value lists.

• Supports both database-driven and custom value lists.

• Allows cascading filters for advanced reporting scenarios.

• Flexible options like ScanLimit and SmartLookup for large or complex data sets.

Conclusion

 

The NL(“Lookup”) function is one of the most powerful features in Jet Reports for making reports dynamic, accurate and interactive. Whether you’re creating simple dropdowns, multi-field lookups or chained filters, Lookups give report users the freedom to get exactly the data they need; without modifying the report design.

 

By mastering NL Lookups, you can take your Excel reporting to the next level and deliver professional, customizable reports that your team will actually enjoy using.

 

If You Need Any Help, Contact us Now!