0

"Advanced" NL function with various filters

Jet Reports Historic Posts

November 25, 2009 10:42

None

Follow

Hi there,


I am having a bit trouble creating a JetReport and was hoping to get some help here ;-)


The problem is that I need to create a quite long NL function with lots of filters - more filters then JetReports is apparently able to process (at least the version we use here, which is 5.2.9 Build 1661 (Jan 30th 2007) on a Navision 3.7 environment).

There are 2 databases, one called Servicehead, the other Serviceitemline.

These are the fields / filters I need to use:

Servicehead:
- ID
- Serviceordertype
- Incoming date
- Outgoing date
- Warranty
- Startdate X

Serviceitemline:
- repID
- Repair Code (in first entry of the service case, based on Servicehead.ID)
- RowNo


With less filters I would use a "=LINK" function like:

In words: "Show me every entry in the servicehead with the outgoing date of November 2nd, the Service Order Type XYZ where there is an incoming date set, where there is warranty = yes and where the startdate X is empty. Additionally, the repair code, which is found in the table ServiceItemLine, must be 01 or 02. The connection between those 2 tables is ID and repID. repID can have several entries, so please only check the repair code in line 10.

=NL("ROWS","Servicehead",,+"Outgoing date";"021109";"Serviceordertype","XYZ","Incoming date",">''","Warranty","yes";"Startdate X","@@","LINK=","Serviceitemline","repID","=ID","RowNo",10,"Repair Code","<>01&<>02")


This NL function is working - theoretically. But unfortunately after the use of 5 or 6 (not sure about the absolute number) filters, the limit is reached and I only get an error.



After the standard NL function, I have tried several other ways to get the data.
One was to use the NP("UNION", , ,) function, the other was using a NL("FILTER",…) instead of the LINK.
But neither worked for me (maybe I was just doing it the wrong way !?)


This is how I have tried to used the UNION function:
Cell A1:
=NL("ROWS","Servicehead",,+"Outgoing date";"021109";"Serviceordertype","XYZ","Incoming date",">''","Warranty","yes";"Startdate X","@@")

Cell B1:
=NL("ROWS",Serviceitemline",,"RowNo",10,"Repair Code","<>01&<>02")

Cell C1:
=NP("UNION",A1,B1)

It did not show an error, but also the result was showing lines including repair code 01 or 02… and also it searches through the whole table, which is really slow and nothing I can do in this environment.


If there is someone out there willing to read through my problem and also maybe able to give me some hints, I would be very thankful ;-)


Thanks in advance

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts November 25, 2009 19:17

  This NL function is working - theoretically. But unfortunately after the use of 5 or 6 (not sure about the absolute number) filters, the limit is reached and I only get an error.

  What error are you getting?
• 0
  
  Jet Reports Historic Posts November 25, 2009 19:21

  well an Excel error #value (or whatever the exact translation from german to english in Excel is for that error ;-) )

• 0
  
  Jet Reports Historic Posts November 25, 2009 22:26

  What happens if you select the cell with the #Value (#WERT I think) and then select Jet >> Debug. You should get an error message.

• 0
  
  Jet Reports Historic Posts November 26, 2009 07:10

  Unfortunately there is no error message in the debug mode. I have read in a JetReports book that there is a filterlimit of max 9 filters for the NL function (don't know though whether they raised the limit in newer versions… but due to our relatively old version of Navision of 3.70 we can't upgrade JetReports as our it-manager told me).
  
  I am sure that there is no error in the filters itself, it's just the raw number of them in one NL function which I have to avoid / bypass somehow

• 0
  
  Jet Reports Historic Posts November 30, 2009 02:42

  Try
  
  Cell B1:
  =NL("Filter",Serviceitemline","RepID","RowNo",10,"Repair Code","<>01&<>02")
  
  Cell B2:
  =NL("Rows","Servicehead",,+"Outgoing date";"021109";"Serviceordertype","XYZ","Incoming date",">''","Warranty","yes";"Startdate X","@@","ID",$B$1)
  
  The Filter will do an internal Joined list (i.e. AAA|BBB|CCC)
  The rows will do rows of the ServiceHead according to your Filters where the ID is already filtered in B1

• 0
  
  Jet Reports Historic Posts November 30, 2009 08:37

  Hi there,
  
  thanks for your answer / help.
  
  
  it seems that the filter thing generally works - but the database is quite huge with several hundred thousand of entries and the filter first searches through every line which takes quite a while.
  
  I need to set some kind of prefilter for the filter (a date-range like a week / month), but this date is again back in the servicehead and apparently I can not set a LINK in the filter function referring to the servicehead and the dates…
  
  I have tried something similar with 2 filters and one combining INTERSECT function, but the same problem occured :(
  
  
  Any other idea would be appreciated
  
  
  
  By the way…
  Can anyone tell me whether this kind of limit in the NL function (I mean the maximum of 8 or 9 filters) is still existing in the latest versions of JetReports?

• 0
  
  Jet Reports Historic Posts November 30, 2009 09:02

  The current limit for Jet (in V7 and 2009) is 10 Filter pairs
  
  You will always need to look through the ServiceLine so this would most likely be a constant problem.
  
  Cell B1:
  =NL("Filter","Servicehead",,+"Outgoing date";"021109";"Serviceordertype","XYZ","Incoming date",">''","Warranty","yes";"Startdate X","@@")
  
  Cell B2:
  =NL("Filter","Serviceitemline","RepID","RowNo",10,"Repair Code","<>01&<>02","Document No.",B1)
  
  Cell B3:
  =NL("Rows","ServiceHead",,"ID",B2)