0

How to get jet reports to combine like items

Jet Reports Historic Posts

January 24, 2011 20:07

None

Follow

I run an inventory report and I have it grouped by item and then by variant code. How do I get jet reports to total up like items. please see the example below:

1460 3 PACK OF POUCHES
8008 CHEATER MULT 6
8008 CHEATER MULT 1
3027 BABY MULTI 1
3027 BABY MULTI 8
3027 BABY MULTI 1
8008 CHEATER MULT 1
3027 BABY MULTI 1
3027 BABY MULTI 1
3027 BABY MULTI 1
3027 BABY MULTI 1
8008 CHEATER MULT 2
So as you can see I have several codes that are the same and I would like for Jet Reports to combine those like items together to give me one total.

These are my NL functions:
=NL("Rows","Item Ledger Entry",,"Remaining Quantity",$C$4,"Location Code",$C$3,"Item No.",E9)
=NL("Rows=4","Item Ledger Entry","Item No.","Remaining Quantity",$C$4)
=NL("First","Item Variant","Description 2","Code","@@"&F10)
=NL("First","Item Variant","Description","Item No.",E9)

These are my nf functions:

=NF(D10,"Variant Code")
=NF($D10,"Remaining Quantity")

Any help on this would be greatly appreciated.

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts January 24, 2011 22:30

  When you give that list of data, are you saying that a single field (Variant Code?) has the entire line or are you combining multiple fields. So I guess I'm trying to ask, is the Variant Code 1460, 8008, etc. or is the Variant Code "1460 3 PACK OF POUCHES", "8008 CHEATER MULT 6", etc.? If the variant code is 1460,8008, etc., then it is easy because you can just do
  
  =NL("Rows","Item Ledger Entry","Variant Code",…)
  
  That gives you the unique list of codes, and from there you could do an NL(Sum) to get the sum of Remaining Quantity for that variant code.
  
  If, on the other hand, the Variant Code contains "1460 3 PACK OF POUCHES", etc. then it's a little more tricky. In this case, we would need to use a calculated field, something like this:
  
  =NL("Rows","Item Ledger Entry","=LEFT(NF(,""Variant Code""),4)",…)
  
  This would give us the unique combinations of the left 4 characters from the Variant Code field (1460, 8008, 3027, etc.). Then you could do an NL(Sum) for Remaining Quantity that looked something like this:
  
  =NL("Sum","Item Ledger Entry","Remaining Quantity","Variant Code",D3&"*",…)
  
  This would give us the sum of remaining quantities where the variant code starts with the value from the NL(Rows). Does this make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 25, 2011 13:45

  The variant code has 2 separate fields. so the variant code would be the variant code field and then the description of the variant code.
  for example variant 1460 would be in column let's say b4 and the description for that variant code would be in column b5.

• 0
  
  Jet Reports Historic Posts January 25, 2011 15:11

  I Have added a copy of the report that I use. So you can take a better look at what I am trying to do.
  
  Thank you Hughes

  • On Hands soho.xls
• 0
  
  Jet Reports Historic Posts January 25, 2011 17:07

  Aaah, in this case, it's relatively easy. I will attach your report with the subtotal. Does this work for you?
  
  Regards,
  Hughes

  • On Hands soho Subtotaled.xls
• 0
  
  Jet Reports Historic Posts January 26, 2011 12:33

  thank you Hughes for the edited worksheet but that didn't work out. It gives me an error message "the record key has been corrupted or is invalid" Also I don't want to do subtotal and then subtotal. If there is another way please let me know.
  
  Thank you.