0

Back Orders

Jet Reports Historic Posts

May 10, 2009 01:43

None

Follow

Hi Guys,

I think this might be an easy one to do but I'm a bit stuck with how to do it.

In my report i'm trying to show how many back orders by a specific Salesperson.
but the formula is starting to get too long.

so if a customer has 3 different products on order and we only ship 1 (the other two are on back order) that would count as 1 Back order

i'm using the Sales Header filtered by :a specific Salesperson, :Status of Released
and ive got to somehow return the values from the Sales Line (Outstanding QTY field)

please help :|

-Bromy

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts June 02, 2009 06:03

  I haven't had any replies, but i'm sure Someone would have some ideas.
  
  -Bromy

• 0
  
  Jet Reports Historic Posts June 03, 2009 17:34

  Bromy,
  
  I'm not sure if this is what you are looking for but…
  
  =NL("Rows","Sales Header",,"Salesperson",$C$3,"Status","Released","Document No.",NL("Filter","Sales Line","Document No.","Outstanding Qty","0"),"Link=","Sales Line","Outstanding Qty","<>0","Document Type","=Document Type","Document No.","=No.")I think, I'm not at work today so I can't test.
  
  However, I wonder if this would work also
  
  =NL("Rows","Sales Header",,"Salesperson",$C$3,"Status","Released","Document No.",NL("Filter","Sales Line","Document No.","Outstanding Qty","0","Link=","Sales Line","Outstanding Qty","<>0","Document Type","=Document Type","Document No.","=No."))

• 0
  
  Jet Reports Historic Posts June 10, 2009 21:54

  Hi Bromy,
  
  If I understand correctly, you only want to count the number of open orders that have had SOME items ship, but still have some items outstanding, correct? You are on the right track with your thinking, but the solution to your problem is going to involve what is called a calculated filter. What we need to do is apply all of the other filters that you mentioned (Status=Released, Salesperson=x, etc.) but we only want to count the ones where the Quantity - Outstanding Quantity is greater than zero. For example, an item that hasn't shipped will look like this:
  
  Quantity: 10
  Outstanding Quantity: 10
  Difference: 0
  
  What you are looking for is an example like this:
  
  Quantity: 10
  Outstanding Quantity: 6
  Difference: 4
  
  The function that you are looking for is going to be this:
  
  =NL("CountUnique","Sales Line","Document NO.","=NF(,""Quantity"")-NF(,""Outstanding Quantity"")",">0","Link=","Sales Header","No.","=Document No.","Salesperson Code","*","Status","Released")
  Just replace the asterisk in the ("Salesperson Code","*") part with the actual salesperson code that you want to use.
  
  Does this answer your question?
  
  Brian

• 0
  
  Jet Reports Historic Posts June 11, 2009 01:26

  Hi Brianjet
  Quantity: 10
  Outstanding Quantity: 10
  Difference: 0
  this would still count as a backorder because we haven't sent it out yet.
  
  Thank you chuck, i used a bit of yours
  the formula I'm using
  =NL("Rows","Sales Header","Salesperson Code","+Salesperson Code",$B$2,"Status","Released","No.",NL("Filter","Sales Line","Document No.","Document Type","Order","Type","Item","Outstanding Quantity","<>0"))it took me a little bit to find the "Outstanding Quantity" Field
  
  -Bromy