Supply subtotal and grand total rows in reports that use aggregate functions

[Mike Rylander]

NOTE: this change requires Postgres 9.5 or later be the minimum version for Evergreen!

In Postgres 9.5 and newer, it is simple to generate group subtotal and grand total rows for aggregate queries. The simplest thing would be to spell the grouping clause as

  GROUP BY ROLLUP (...)

rather than

  GROUP BY ...

when there are a mix of aggregate and non-aggregate columns in a report.

Branch coming soon to implement that...

[Mike Rylander (mrylander)]

Branch coordinates: http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/lp-1789679-report-rollup

[Galen Charlton (gmc)]

I like the idea, but I think there needs to be a bit of additional work so that users can signal in the template and/or when creating a report that they want this behavior. Otherwise, somebody who uses a given template for statistics that they generate reports from periodically may be in for an unwelcome surprise.

[Galen Charlton (gmc)]

Also, I'm going to take this bug as an opportunity to start a discussion on whether we want to bump up the minimum required Pg version for 3.4.

[Jeff Davis (jdavis-sitka)]

Note that Postgres 9.6+ is now required for EG 3.4 (bug 1830749), so GROUP BY ROLLUP should be supportable in EG 3.4+.

[Mike Rylander (mrylander)]

I've force-pushed a rebased and updated branch that makes this feature optional, via the report definition interface and a new "Calculate grouping subtotals" checkbox. As a side note, using this new feature with a pivot column is handy -- it provides a leading subtotal column for each combination of non-pivot columns.

[Chris Sharp (chrissharp123)]

I signed off on Mike's fix and added a commit tweaking whitespace (tabs over spaces to match the file formatting) and uncheck the new checkbox by default so people don't see things change unexpectedly.

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/csharp/lp-1789679-report-rollup

[Terran McCanna (tmccanna)]

This may need a rebase since it was signed off back in February.

[Chris Sharp (chrissharp123)]

Rebased branch available at the same location, with release notes. Needs a signoff on my second commit, at least.

[Lynn Floyd (lfloyd)]

I signoff of this commit.
Lynn Floyd
<email address hidden>

[Galen Charlton (gmc)]

Pushed to master for inclusion in 3.6, along with a follow-up that adds an illustrative example to the release notes. Thanks, Mike, Chris, and Lynn!