Discussions

Expand all | Collapse all

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

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

    Posted 06-20-2017 16:04
    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.


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

    Posted 06-21-2017 04:11
    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.


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

    Posted 06-22-2017 15:20
    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: http://underscorejs.org/

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