creating fields based on summary total columns. Does anyone have a workaround with this limitation?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
We are facing consistent walls in getting some simple calculations from a summary total reporting level. We have created numerous summary reports but cannot do simple calcs using the summarised totals i.e. working out margins between two total values, subtracting one total value summary from another etc. It would be really great if we could create further summary fields calculating from summed totals. Has anyone got a workaround? we seem to be having to create many tables to get around this but now hitting walls as some calcs are just too deep nested at lower levels.
Photo of Paul


  • 40 Points

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,758 Points 20k badge 2x thumb
What are some of the 'preliminary' levels of summed groupings?  you might be able to inject a parent table somewhere in the mix, and summarize the date before your build the reports.

If you can provide some more details of the data you are dealing with, and the output desire, we should be able to come up with something.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
Use script. It is a four step procedure:

(1) use API_DoQuery to gather the raw un-aggregated data in XML

(2) convert XML to JSON

(3) use Underscore to aggregate / summarize / process / calculate with your JSON data.  Underscore is a Swiss army knife for processing data and includes methods for groupBy(), sortBy(), IndexBy(), CountBy() and every manner of filtering and aggregation you could think of. See:

(4) render your resulting JSON to HTML or post it to a table using API_AddRecord or API_ImportFromCSV