0

Sales Line Filtering

Jet Reports Historic Posts

March 04, 2015 14:04

None

Follow

Good Morning :)

I'm creating a report where I need to look at the sales line of all orders and if SKU A is present, then I pull the Document No. Field and do stuff with it.
Where I'm running into an issue is that I need to apply a filter that, in english, says "All Document No. where, in the sales lines, SKU A is present UNLESS SKU B is also present."

I know what exactly the SKUs are and they never change. I've tried nesting the NL, I've tried pulling the Document No. from the Sales Header and linking to the Sales Line and in both cases, I get both the Document No. that have only SKU A and those with SKU A and SKU B.

Here's the current filter I have running which does not work :( :

=NL("Rows=5","Sales Header","No.","Link=Sales Header","Sales Line","Document No.","=No.","No.","MK-REQUIRED","No.","<> MK-STAGED")

Any advice is appreciated.

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts March 04, 2015 14:18

  This is a job for array caculations:
  
  http://kb.jetreports.com/article/AA-00498/0/Array-Calculations.html
  
  NL("Filter","Sales Line","Document No.","No.","SKU A") //Filter 1 - all the sales order no.s with and SKU A
  
  NL("Filter","Sales Line","Document No.","No.","SKU B") //Filter 2 - all the sales order no.s with and SKU B
  
  Lets get the list of all the ones you want to exclude:
  
  NP("Union",Filter1,Filter2) //Filter 3
  
  Now you can do a difference to give you everything with SKU A, minus everying with SKU A and SKU B.
  
  NP("Difference",Filter1,Filter3) // Filter 4
  
  That will turn an array which you can use as a filter
  
  =NL("Rows=5",Filter4) should give you all the order no.s you are after.
  
  Usual disclaimers about having typed this from memory!

• 0
  
  Jet Reports Historic Posts March 04, 2015 14:21

  Thank you so much!
  I'm giving it a spin right now.

• 0
  
  Jet Reports Historic Posts March 04, 2015 16:20

  as an update and in case someone else was waiting for the answer:
  
  TeresaRoberts' suggestion was the right direction and she's awesome for that.
  
  In the end, the answer was: =NL("Rows=5",NP("Difference",NL("Filter","Sales Line","Document No.","No.","SKU A"),NL("Filter","Sales Line","Document No.","No.","SKU B")))
  
  In a nutshell, I did the difference between all Sales lines with SKU A and SKU B. Because all orders with SKUB will have SKU A but not all orders with SKU A will have SKU B. (If that makes sense?)
  
  Again, thanks for the assist!