0

Using a NL Filter within a sort of an NL Sum Calc field

Jet Reports Historic Posts

August 17, 2011 16:14

None

Follow

Hi everyone,

I am writing a report to look at the top ten customers outstanding items on current sales orders on backorder status:

=NL("Rows","Customer",,"Blocked","<>All","-=NL(""Sum"",""Sales Line"",""Outstanding Amount (LCY)"",""Document Type"",$B$2,""Bill-to Customer No."",NF(,""No.""),""No."",$B$5,""Outstanding Quantity"",""<>0"",""Link="",""Sales Header"",""No."",""=Document No."",""Order Status"",$B$3)","*","Limit=",10)
(Below is to shorten the NL function to under 255 chars:)

B2: Order B3: On Back Order B4: Item B5: =NL("Filter","Item","No.","Category","A|B","Blocked",FALSE)

B5 is aimed at only considering Sales Lines where the Item on the line belongs to Categories "A" or "B" ('Category' is a field on the Item table).

My problem is that the Filter in B8 slows the report down to over 12 minutes! Leaving this filter out completely has the report running at under a minute!

Is there any way I could optimise this filter or the NL function itself?

FYI, below are the keys on the Sales Line table, if they are of any use:

Enabled Key SumIndexFields
Yes Document Type,Document No.,Line No. Amount,Amount Including VAT,Outstanding Amount,Shipped Not Invoiced,Outstanding Amount (LCY),Shipped Not Invoiced (LCY)
Yes Document No.,Line No.,Document Type
Yes Document Type,Type,No.,Variant Code,Drop Shipment,Location Code,Shipment Date,Deleted Outstanding Qty. (Base)
Yes Document Type,Bill-to Customer No.,Currency Code Outstanding Amount,Shipped Not Invoiced,Outstanding Amount (LCY),Shipped Not Invoiced (LCY),Return Rcd. Not Invd. (LCY),Return Rcd. Not Invd.
No Document Type,Type,No.,Variant Code,Drop Shipment,Shortcut Dimension 1 Code,Shortcut Dimension 2 Code,Location Code,Shipment Date Outstanding Qty. (Base)
No Document Type,Bill-to Customer No.,Shortcut Dimension 1 Code,Shortcut Dimension 2 Code,Currency Code Outstanding Amount,Shipped Not Invoiced,Outstanding Amount (LCY),Shipped Not Invoiced (LCY)
Yes Document Type,Blanket Order No.,Blanket Order Line No.
Yes Document Type,Document No.,Location Code
Yes Document Type,Shipment No.,Shipment Line No.
Yes Type,No.,Variant Code,Drop Shipment,Location Code,Document Type,Shipment Date
Yes Document Type,Sell-to Customer No.
Yes Job Contract Entry No.
Yes Document Type,Document No.,Bin Code
Yes Type,No.,Variant Code,Drop Shipment,Location Code,Bin Code,Document Type,Shipment Date
Yes Sort Bin
Yes Type,No.

I am using Jet Reports 2010 R2 with NAV 5.0 SP1 Update 2. Have used Jet Reports since version 7, and have not seen anything like this before. It should be easy, shouldn't it? :evil:

Thanks guys and gals!

Stephen

• Facebook
• Twitter
• LinkedIn

3 comments

• 
  Jet Reports Historic Posts August 17, 2011 20:59 Official comment

  Hi Stephen,
  
  Hmmm, sort by sum taking 12 minutes is really not that bad depending on how much data there is in the Sales Line table, but maybe we can make it faster by changing your NL(Filter) to a Link=. It would look something like this:
  
  =NL("Rows","Customer",,"Blocked","<>All","-=NL(""Sum"",""Sales Line"",""Outstanding Amount (LCY)"",""Document Type"",$B$2,""Bill-to Customer No."",NF(,""No.""),""Outstanding Quantity"",""<>0"",""Link="",$B$5,""Link=Sales Line"",""Sales Header"",""No."",""=Document No."",""Order Status"",$B$3)","*","Limit=",10) Then in cell B5:
  
  =NL("Link","Item",,"No.","=No.","Category","A|B","Blocked",FALSE)
  NL(Link) is just a shortcut because I'm guessing you'll run out of arguments in the NL function if you try and embed the Link= in it the way you embedded the other one. You could also convert your Link= to the Sales Header to use an NL(Link) formula if you want to make it a bit easier to read. Notice that I changed the 2nd "Link=" to "Link=Sales Line" which makes it link from the primary table (the Sales Line) instead of being a nested link from the Item table. If you want multiple links from the primary table, you have do this or else they get treated as nested links.
  
  Also note that with sort by sum, the sum must be calculated for EVERY customer so that they can be sorted before the limit is applied. This is one reason that sort by sum is generally pretty slow. Does this perform any faster for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 18, 2011 14:42

  Hi Hughes,
  
  REALLY appreciate the time-out you took to examine this.
  
  I had thought to make it into a Link rather than a Filter, but I did not know you could use =NL("Link",… as it is "Link=" in all of the documentation I see where it is nested inside an NL function. I have never seen =NL("Link",… in my life! I tried many many other was of taking the Link out of the larger NL function, as I hit the Formula=255 character limit.
  
  Anyway - MUCH faster! Less than a minute! I knew there had to be a way around this using links! :D
  
  And I agree with the sorting by sum needing to be done for every customer - I can see that when running the report. Even with =NL("Rows","Customer",,"Blocked","<>All",… I see customers that I know are definitely locked but the sum is still being calculated for them. Surely though in the larger NL function, it should pass only those customer no. to the nested functions if indeed "Blocked" ISS "<>ALL", should it not? :shock:
  
  Anyway, thanks again for your help. Just love this community.
  
  Regards,
  Stephen

• 0
  
  Jet Reports Historic Posts August 18, 2011 17:24

  Hi Stephen,
  
  No problem at all! I'm glad the performance is good again. :-)
  
  Any regular string filters you apply to the NL(Rows) function should get applied first (such as "Blocked","<>All", etc.). So Jet would actually tell Nav to apply those filters so it would only get back the records from Nav that matched those filters. Then Jet would spin through whatever records it got back and calculate the sums for each one, adding the results to a sorted set. Then at the end Jet would take the top 10 records from the sorted set because of the limit= and return those. I'm not sure what you're seeing that makes you think the sum is calculated for records where "Blocked" is "All", but the progress window can be somewhat deceiving in the way it presents information sometimes, so it may not be what you think.
  
  Regards,
  Hughes