0

Table Builder: Not allowing sum of Amount column

Jet Reports Historic Posts

February 26, 2015 19:20

None

Follow

I am joining 4 tables via the Table Builder:

GL Entry
->GL - Item Ledger Relation
—>Value Entry
—–>Item Ledger Entry

I need to sum the Amount field in GL Entry, but don't see a "Sum Fields" section on the left hand pane where I would usually see it. I see "Flow Fields" instead. How can I create a sum on this field? I do see "Sum Fields" section for all the other tables.

I am ultimately needing to sum Amount in GL Entry as well as Cost Amount (Actual) in Value Entry. I have successful summed Cost Amount (Actual) via the "Sum Fields" section.

Perhaps it has to do with the order of joining the tables, however I will have the same problem if I stared with Value Entry as I need to sum there also.

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts February 26, 2015 23:37

  Because you are initially looking at the GL entry table as your base table, there is only one "Amount" associated with each record in the G/L Entry table. If you were to hypothetically try to sum one single value, it would be the same as looking that number. (i.e. the sum of 13.45 is 13.45, there is nothing to add to it)
  
  You can see that you can reverse it and start at the Item Ledger Entry table and work backwards. If you do this, you will be able to sum up amounts because there may be multiple G/L Entries associated with one Item Ledger Entry record.

• 0
  
  Jet Reports Historic Posts February 27, 2015 18:28

  If I flip the tables around it does seem to run, but takes nearly 1 hour as opposed to 10 seconds if I start with GL Entry. I spent some time in SQL and can get the answer starting with GL Entry if I am able to do something akin to the MAX() function on Cost Amount (Actual). Do you know how to do this as opposed to a SUM?
  
  Basically, I would like to take the maximum value for Cost Amount (Actual) instead of SUM. Any ideas?

• 0
  
  Jet Reports Historic Posts February 27, 2015 21:28

  I ask the same question as I did in a previous post, does it need to be built with the Table Builder? Since I don't know your report, here is one where I listed all the GL Account numbers in the GL Entry table, then got the sum of the amounts for each account and the sum of the Cost Amount associated with that GL account.

  • sum-amount-cost.xlsx