0

Filter in NL Function

Jet Reports Historic Posts

April 18, 2016 18:32

None

Follow

Hello,

I want to create a filter in an NL Rows function that says:

(Sell-to State <>CA) OR (Sell-to State =CA AND Sales Organization Code =EXPORT)

Is this possible to do in the Function Wizard?

• Facebook
• Twitter
• LinkedIn

6 comments

• 
  Jet Reports Historic Posts April 18, 2016 22:59 Official comment

  Hello YJury.
  
  Because you want to use one field (Sell-to State) to be either not equal to CA or equal to CA (with another condition), you can't do this in the filter section of your NL(Rows) function. You can't use parentheses to achieve both an equal and a not equal like you could using SQL.
  
  Depending on other filters you're using, you could use 2 NL("Filter") statements - one for "<>CA" and one for "=CA and Sales Organization Code=EXPORT", and then NP("Union") the two together. You could also use conditional hide to bring back everything and then hide the rows that don't fulfill the conditions. Better to not bring back the rows at all if possible.
  
  Array Calculations
  http://kb.jetreports.com/article/AA-00498
  
  Conditional Hide
  http://kb.jetreports.com/article/AA-00522

• 0
  
  Jet Reports Historic Posts April 18, 2016 19:24

  Hello YJury,
  
  With the use of filters and wildcards, you should be able to accomplish this.
  Are you attempting to EXCLUDE data where 'CA' is the Sell-to State?
  
  Please take a moment and review the below KB articles:
  Filtering Reference
  http://kb.jetreports.com/article/AA-00502
  
  Wild Card Filters
  http://kb.jetreports.com/article/AA-00582
  
  I hope these are helpful to you.
  
  Cheers

• 0
  
  Jet Reports Historic Posts April 18, 2016 20:38

  Yes, I want to exclude data where the state is CA, but if the sales organization code = Export, then I want to include rows where state = CA. So I need to evaluate 2 different fields. I can't figure out how to accomplish this in the NL function.

• 0
  
  Jet Reports Historic Posts April 19, 2016 16:46

  This is excellent, JamieA!!
  Thank you for the solution. I nested the NP("Union") and the 2 NL("Filter") statements within my original NL("Rows") statement and got the perfect results!

• 0
  
  Jet Reports Historic Posts April 19, 2016 21:13

  Hi please post the final formula

• 0
  
  Jet Reports Historic Posts April 19, 2016 21:37

  =NL("Rows",NP("Union",NL("Filter","Sales Invoice Header",{"No.","Sell-to State","Sell-to Customer No.","Sales Organization Code"},"Sell-to State","<>CA"),NL("Filter","Sales Invoice Header",{"No.","Sell-to State","Sell-to Customer No.","Sales Organization Code"},"Sell-to State","CA","Sales Organization Code","EXPORT")))