Filter Based on Data from Another Table
Overview
Sometimes you will want to filter one table based on data from a related table. When using the Universal connectors, the Reporting Intelligence Excel add-in provides NL("Filter") for this circumstance.
Universal Example of the NL(Filter) function
In this 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 function like:
=NL("Rows","Order Subtotals","Subtotal","OrderID",... {List of OrderIDs with OrderDates between 7/1/19 and 7/31/19} ...)
In the function above, you need an NL function which will replace the description of the OrderID list.
You can use NL("Filter") to create the OrderID filter from the Orders table as shown here:
NL("Filter","Orders","OrderID","OrderDate","7/1/19..7/31/19")
Finally, you need to replace the description in the first function with the second function:
=NL("Rows","Order Subtotals","Subtotal","OrderID",NL("Filter","Orders","OrderID","OrderDate","7/1/19..7/31/19"))
The inner NL function returns a list of OrderIDs that the Reporting Intelligence Excel add-in can use as a filter for the Order Subtotals table.
If you are using multiple cross table filters, you should be aware that the Reporting Intelligence Excel add-in 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.