0

to group multiple rows as 1 row

Jet Reports Historic Posts

July 02, 2014 17:13

None

Follow

Hi all

How do i group multiple rows as 1 row?
For example,

a 1
a 2
a 3
b 1

The desired result should be
a 6
b 1

Please help!

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts July 02, 2014 18:39

  I think we're going to need more information to be able to really understand this question….
  
  But, I'll take a stab at it. Assuming that your "A" is returning from a 'Header' table (like Invoice Header), and the numbers are returning from a 'Line' table (like Invoice Line). Also assuming that "A" is the Invoice number, and the numbers 1,2 & 3 are quantity.
  
  in cell D5: =NL("ROWS","INVOICE HEADER","NO.","SALES DATE",date filter from elsewhere in report,…)
  in cell E5 = NL("SUM","INVOICE LINE","QUANTITY","INVOICE HEADER NO.",D5)
  
  so the rows are returning all invoice headers, but the quantity is summing from the invoice line… does this help?

• 0
  
  Jet Reports Historic Posts July 03, 2014 02:16

  Hi
  
  I am using one table to get the informations, value entry table. I grouped the records by item no. and variant code.
  This is what i get.
  
  Item No. Variant Code Document no. Item Quantity
  abc 12344 Test 1
  Test 1
  
  The desired result i need is :
  
  Item No. Variant Code Document no. Item Quantity
  abc 12344 Test 2
  
  
  
  Please advise.

  • Sample help.xlsx
• 0
  
  Jet Reports Historic Posts July 04, 2014 13:53

  hi,
  
  in this case I would try to work with Excel-Formular "SUMIFS", so you don't have to redesign the Report.
  There is certainly an other way using Jet, but i don't really understand the example you sent. :oops: , so "SUMIFS" is my "quick&dirty"-Version.
  
  regards
  jetsetter

• 0
  
  Jet Reports Historic Posts July 07, 2014 22:02

  looking at your sample, I have to admit to confusion…
  
  I see the following columns:
  
  Item No. - NL ROWS - pulling from the Value Entry Table
  Variant Code - NL ROWS - pulling from the Value Entry Table
  Document No. - NL ROWS - pulling from the Value Entry Table
  and so forth - and you want all of those columns in the new report as well….
  
  Except it looks to me like you could have multiple Document No. for a single Item No. (otherwise why use a "ROWS" command to return that?)
  
  So - eliminate the Document No. column, and use an NL SUM command like this:
  
  NL("Sum","Value Entry","Item Ledger Entry Quantity","Item No.","@@"&$F10,"Variant Code","@@"&$H10,"Posting Date",$C$3,"Link=","Barcodes","Item No.","=Item No.","Variant Code","=Variant Code","Barcode No.",$C$5)
  then do the same for Invoiced Quantity and the other Sum columns - if that will work for you.
  
  please let us know if you need additional assistance.