Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Using NL( Lookup ) in Jet Reports Part-2

We have discussed regarding Lookup in my previous post. If you missed can find link here.

Using NL( Lookup ) in Jet Reports Part-1.

I am continuing with more advanced usage here below.

In some instances it is also desirable to base the values that are displayed in one NL(“Lookup”) function on the results that were selected in another NL(“Lookup”) function.

An example of this could exist in a Sales Report. The viewer will have the ability to select a Salesperson Code to run the report for, and will also be able to specify Customer Numbers in order to filter the report further.

If only one Salesperson Code is selected, however, it may be undesirable to display Customer Numbers that are associated with other Salesperson Codes.

In this instance, two NL(“Lookup”) functions will be used, with the Customer Number filtered by the Salesperson Code so that the values are related. The first NL(“Lookup”) function, which will allow the selection of the Salesperson Code, will look like this:
Lookup-6

Lookup-7

The next NL(“Lookup”) function will give the viewer the ability to select from a list of Customer Numbers, but it will be filtered based on the Salesperson Code that was previously selected. This is done but inserting a normal filter into the function and referencing the cell containing the Salesperson Code that was previously selected by the viewer.

This addition would make the report look like this:
Lookup-8
After selecting Salesperson Code Filter when we open Customer List it will show lookup as below:
Lookup-9

Customer List is filtered out with Salesperson Code we selected using Salesperson List Lookup.

Will come up with more details in my upcoming post, stay tuned for more details.