0

Table Builder

Jet Reports Historic Posts

December 21, 2016 16:37

None

Follow

This is probably very easy but I cannot figure it out. I just want to combine two tables in the table builder. I want to list all of the transactions for a specific time period in the Sales Invoice Header and the Sales Cr.Memo Header. I need these to be a table so I can put the info into a pivot table.

Any help is GREATLY appreciated

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Jet Reports Historic Posts December 21, 2016 18:48

  Hi -
  
  Here is a simple example that I put together.
  
  I used three NL(Table) functions to list my Customer, Sales Invoice Header, and Sales Cr.Memo Header tables:
  
  
  (I included the Customer table as… let's call it the "Key" table… so that I would have a field with unique values - something that we'll need in a moment)
  
  Next, I ensure that I have selected a cell in my "Key" table (in this case, Customer) and then click the PivotTable button on the Insert ribbon.
  
  
  The table name should match the name I supplied in my NL(Table) function:
  
  =NL("Table","Customer",$K$6:$L$6,"Headers=",$K$5:$L$5,"TableName=","Customer","IncludeDuplicates=","True")
  
  
  and I want to ensure that I check the box for Add this data to the Data Model.
  
  In the PivotTable Fields window, click All
  
  You will see all the available tables and field.
  
  
  I added the customer number and name. When I added the Amount field (from either the Sales_Invoice_Header or Sales_CR._Memo_Header), I was asked about defining the relationship between the tables:
  
  
  Excel was able to AUTO-DETECT this:
  
  
  and the correct data was displayed in the pivot table.
  
  I hope that helps.