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

Bug #1789679 reported by Mike Rylander on 2018-08-29
14
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Evergreen
Wishlist
Unassigned

Bug Description

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...

Jane Sandberg (sandbej) on 2018-12-12
tags: added: needsreleasenote
Dan Wells (dbw2) on 2019-02-06
Changed in evergreen:
milestone: 3.next → 3.3-beta1
Changed in evergreen:
milestone: 3.3-beta1 → 3.3-rc
Changed in evergreen:
milestone: 3.3-rc → 3.next
Galen Charlton (gmc) wrote :

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.

Changed in evergreen:
status: New → Confirmed
Galen Charlton (gmc) wrote :

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) wrote :

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) wrote :

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) wrote :

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) wrote :

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

tags: added: signedoff
Chris Sharp (chrissharp123) wrote :

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

tags: removed: needsreleasenote signedoff
Changed in evergreen:
milestone: 3.next → 3.6-beta
Lynn Floyd (lfloyd) wrote :

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

Michele Morgan (mmorgan) on 2020-08-20
tags: added: signedoff
Galen Charlton (gmc) on 2020-08-24
Changed in evergreen:
assignee: nobody → Galen Charlton (gmc)
Galen Charlton (gmc) wrote :

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!

Changed in evergreen:
assignee: Galen Charlton (gmc) → nobody
status: Confirmed → Fix Committed
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers