0

Ignore empty Options

Jet Reports Historic Posts

March 03, 2011 11:34

None

Follow

Hi @ all,
i'm fairly new to JetReports and have a simple Question.

I want to show all Product Order Numbers (from Product Order Line) where ItemNo. 1 and ItemNo. 2 and so on (up to 5 Item numbers).

Example with 3 Options

I've tried
=NL("Rows";"Production Protocol Line";;"DataSource=";"dabasebase";"No.";"@@"&$C$2;"No.";"@@"&$C$3;"No.";"@@"&$C$4)
If all 3 cells or filled with Item Numbers everything worked as it should. But if i dont need Item number 3 #value appears. This is comprehensible for me.
How do i disable the non filled options in the NL formula.

BR
MG

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts March 03, 2011 16:37

  Hi MG,
  
  So your report isn't going to work because you can't filter by the same field more than once in your formula. What you want to do is only use one cell for your filter value instead of putting the item numbers in 3 separate cells and separate the item numbers with | (OR operator) like this:
  
  10000|20000|30000
  Then your formula would look like this:
  
  =NL("Rows";"Production Protocol Line";;"DataSource=";"dabasebase";"No.";"@@"&$C$2)
  This means you want Production Protocol Line records where the No. field is 10000 or 20000 or 30000. Then if you decide you want to see all line numbers, you can change the value in the filter cell to *.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 03, 2011 17:15

  Hi Hughes,
  
  thx for your reply.
  I need all Product Order numbers where all 1-5 Options are matching.
  
  for example:
  
  table:
  Order No. | Item No
  100 | 123
  100 | 124
  100 | 125
  101 | 567
  102 | 123
  
  Options:
  Item No.: 123
  Item No.: 124
  Item No.: 125
  
  I would like to see Order No. 100 as result.
  
  If i use the OR operator, Order NO. 102 will also be shown, or not?
  in my understanding i have to use a AND operator, but I didn't get it work.
  
  Thx in advance
  
  MG

• 0
  
  Jet Reports Historic Posts March 03, 2011 17:52

  Aaah, now I see your problem. This is actually a bit more complicated to do. You actually need to get the intersection of entries. So it would work something like this:
  
  C2: =NL("Filter";"Production Protocol Line";"Order No.";"DataSource=";"dabasebase";"No.";"123") C3: =NL("Filter";"Production Protocol Line";"Order No.";"DataSource=";"dabasebase";"No.";"124") C4: =NL("Filter";"Production Protocol Line";"Order No.";"DataSource=";"dabasebase";"No.";"125") C6: =NL("Rows";NP("Intersect";$C$2;$C$3;$C$4)) D6: =NL("First";"Production Protocol Line";;"Order No.";C6;"DataSource=";"dabasebase")
  So I believe this would give you the result you are looking for, which is the unique set of order numbers where there exists an entry with the item numbers 123, 124, and 125. Does that work as you want it to?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 07, 2011 14:38

  Today i've tried your example. I got an "Excel is unable to evaluate NP("Intersect";$D$3;$D$4;$D$5)" error.
  Would it be helpful if i attach the workbook?
  We are using NAV 5.01 and JR 10.1.11
  
  Greetings
  MG

• 0
  
  Jet Reports Historic Posts March 07, 2011 15:58

  Hmmm, I'm not sure what would cause that error. Sure you can post your workbook if you like.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 08, 2011 09:51

  Would you please have a look?

  • Intersect_test.xlsx