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

Revision history for this message
Mike Rylander (mrylander) wrote :
tags: added: pullrequest
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
Revision history for this message
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
Revision history for this message
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.

Revision history for this message
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+.

Revision history for this message
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.

Revision history for this message
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

Revision history for this message
Terran McCanna (tmccanna) wrote :

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

tags: added: signedoff
Revision history for this message
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
Revision history for this message
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)
Revision history for this message
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