Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Filtering Based on Data from Another Table


Sometimes you will want to filter one table based on data from a related table. In Great Plains and the Universal connector, Jet Reports provides NL("Filter") for this circumstance.

In Northwind for example, the Order Subtotals table does not have the OrderDate in it, but the Orders table does. The OrderID is common to both tables, so if you wanted to list Order Subtotals based on an OrderDate, you would start out with an NL formula like the following.

=NL("Rows","Order Subtotals","Subtotal","OrderID",<List of OrderIDs with OrderDates 7/1/96..7/31/96>)

In the formula above, you need an NL formula which will replace the English description of the OrderID filter. You can use NL("Filter") to create the OrderID filter from the Orders table as shown in the formula below.

NL("Filter","Orders","OrderID","OrderDate","7/1/96..7/31/96")

Finally, you need to replace the English description in the first formula with the second formula as shown below.

=NL("Rows","Order Subtotals","Subtotal","OrderID",NL("Filter","Orders","OrderID","OrderDate","7/1/96..7/31/96"))

There is one NL function inside another. The inner NL function returns a list of OrderIDs that Jet can use as a filter for the Order Subtotals table.

If you are using multiple cross table filters, you should be aware that Jet Reports will use each of the elements in each list as a filter in combination with all of the elements of the other list. This can result in very slow reports if you are not careful.

=NL("Rows","Sales Line",,"Document No.",{List of Document No.s with Posting Dates 1/1/02..1/31/02})

=NL("Filter", "Sales Header", "No.", "Posting Date", "1/1/02..1/31/02")

=NL("Rows","Sales Line",,"Document No.",NL("Filter","Sales Header","No.","Posting Date", "1/1/02..1/31/02"))

=NL("Rows","Sales Invoice Header","No.","Posting Date","7/1/05..7/31/05","Link=","Sales Invoice Line","Document No.","=No.","Type","Item")

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments