0

Double filter

Jet Reports Historic Posts

October 13, 2009 06:39

None

Follow

Alread, thanks for the post.

I`m gona thry it after the weekend. 8-) I let you know.

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts October 13, 2009 06:39

  Dear All,
  
  I am busy with a new project where I run against a problem. I ask some JetReport builders within our company but they also didn’t have the solution. Even Hans from JetReport couldn’t help me. So my hopes are on you. ;)
  
  Aim of the project: To set up MRP (Manufacturing Resource planning).
  Question: Is it possible to work with several filters in one string. So that the variables are at least matching one of the filters.
  
  Example: =NL (" Rows" ; " SKU" ;; " Item no." ; $J$11; " Number in Materials line " ; " <>0" ; " Number in Sales Orders" ; " <>0")
  
  In the example above you see two filters. At first the JetReport look for the ‘Number in Materials line’ if he found some matching records than it `s filtering with the next filter over these records with ‘Number in Sales Orders’. That means if there`s a Sales Order without Material line, these Items wouldn’t show in the JetReport. I would like to have them both in the JetReport. :o
  
  What I would like is a filter string that accepts one of those criteria (or both). I try to use the “&” function and the “|” function to make a double filter. But this isn`t working.
  
  Please if someone knows the answer for this problem…………HELP……thanks :oops:
  
  Kind regards Piet

• 0
  
  Jet Reports Historic Posts October 13, 2009 08:41

  from what i understand you would need to use the NP("Union") with NL("filter")
  If the item number is in B2 put this is C2
  =NL("Filter";"SKU";"Item No.";$B2;"Number in Sales Orders";"<>0")
  in D2
  =NL("Filter";"SKU";"Item No.";$B2;"Number in Materials Line";"<>0")
  And Finally in D2
  NL("Rows";"SKU";"Item no.";np("Union";C2;B2))
  
  the first Filter gets a list of SKU's with sales orders
  the second filter gets a list of SKU's with Materials
  
  the Union merges these item numbers together and does the Rows.
  
  i couldn't properly test the Formula because the field names are different (i dont have the "Number in Sales Orders" or "Number in Materials Line" and my SKU table is "Stockkeeping Unit")
  
  let me know if that works