PS Query & SQL

 View Only
Alliance 2026 | March 8-11, 2026 | Orlando, FL
Back to discussions
Expand all | Collapse all

Subtotals/Totals in PS/Query

  • 1.  Subtotals/Totals in PS/Query

    Posted 02-12-2025 04:42 PM

    Hi,

    I'm sure someone has figured out a good way to do this...

    We have a query that we regularly run, export to Excel, and do subtotals/totals.

    I'd love to be able to fudge this in PS/Query.

    I've seen presentations mentioning ROLLUP and CUBE, but I'm not sure how to make that work in PS/Query...those would be the ideal but I would welcome any solution that makes this happen.

    Thanks,

    Nicole



    ------------------------------
    Nicole Apostola
    Payroll and Financial Systems Analyst
    Princeton University
    na2344@princeton.edu
    ------------------------------
    Alliance 2026 Registration is Open!


  • 2.  RE: Subtotals/Totals in PS/Query

    Posted 02-12-2025 05:15 PM

    There was a presentation back in 2019 (I attended it but never implemented) that detailed out how to do this in PS Query.

     

    https://www.heug.org/blogs/longin-gogu1/2020/12/31/did-you-know-ps-query-can-execute-advanced-analytics

     

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 3.  RE: Subtotals/Totals in PS/Query

    Posted 02-12-2025 05:53 PM

    Hello,

    There is a presentation in the Resource Center called Aggregate reporting in multiple dimensions using PS Query. This may be a good place to start.  It was from Alliance 2019.  Shareen reposted a blog link above however the links on that page are dead except for the Database Data Warehousing Guide.



    ------------------------------
    Daniel Labrecque
    Senior 2 Business Systems Analyst/Functional Architect
    University of Nevada, Las Vegas
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 4.  RE: Subtotals/Totals in PS/Query

    Posted 02-12-2025 05:56 PM

    These two links on the 2019 presentation are giving me a page not found error.

    Aggregate reporting in multiple dimensions using PS Query

    Alliance 2019 – Reporting and BI Presentations



    ------------------------------
    Sandra Asebedo
    Senior ERP Programmer
    University of Texas At Arlington
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 5.  RE: Subtotals/Totals in PS/Query

    Posted 02-12-2025 06:04 PM

    Sorry!  Didn't check the links!!  My fault. 

     




    Original Message
    Alliance 2026 Registration is Open!


  • 6.  RE: Subtotals/Totals in PS/Query

    Posted 02-13-2025 08:23 AM
      |   view attached

    Hi,

    I appreciate everyone's quick responses.  I was able to use the presentation from 2019 and then put my own spin on it.  I really wanted an as-close-as-I-can-get-to-Excel-subtotals experience as I could get.

    I'm attaching a document that outlines how I was able to take the GROUP BY ROLLUP and then use a GROUPING ID to only select the 'levels' to display in the query results, as well as to clearly label subtotals and totals.

    If this is something you'd like to do, you can follow along with what I did (trust me, there was much trial and error before I got to this point!) and I also welcome questions. :-)

    -Nicole



    ------------------------------
    Nicole Apostola
    Payroll and Financial Systems Analyst
    Princeton University
    na2344@princeton.edu
    ------------------------------

    Attachment(s)

    docx
    Query with subtotals.docx   952 KB 1 version
    Original Message
    Alliance 2026 Registration is Open!


  • 7.  RE: Subtotals/Totals in PS/Query

    Posted 02-13-2025 08:41 AM

    Nicole,

    It's kind of amazing that you figured this out so quickly. I'll start by admitting that I haven't reviewed the presentation and only scanned through your documentation. But it's great to have these here for others now and especially those searching in the future! I'll add this off the top of my head idea that I'll MAYBE experiment with later. My idea is to use a Union for the subtotals and another Union for the totals. You could then use expressions to accurately label the subtotals and totals. Now that I'm writing this, I'm both interested to try it out myself, and hoping that this isn't the solution from the presentation I didn't review before responding. :)

    Cheers all! John at Miami



    ------------------------------
    John Eggenton
    Senior Software Architect
    University of Miami
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 8.  RE: Subtotals/Totals in PS/Query

    Posted 02-13-2025 10:52 AM
      |   view attached

    I went ahead and tried out the idea of using Unions. It worked well and was easy. Here are instructions to show what I did. This isn't as sophisticated as using analytic functions, but it's pretty easy to do.

    Cheers, John at Miami



    ------------------------------
    John Eggenton
    Senior Software Architect
    University of Miami
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Attachment(s)

    pdf
    Subtotals and Totals in PS Query Using Unions.pdf   145 KB 1 version
    Original Message
    Alliance 2026 Registration is Open!


  • 9.  RE: Subtotals/Totals in PS/Query

    Posted 02-14-2025 08:52 AM

    Hi @John Eggenton - Thanks so much, that was more of what I was thinking of...I could have sworn I'd seen it done (and probably done it myself) in the past.

    What I like about your solution is that it's not Oracle database specific, and that someone who doesn't want to be finicky with formulas can follow it quite well.  (However, I rather dislike doing UNIONS so I may follow the other method at the moment.)

    Thanks,

    Nicole



    ------------------------------
    Nicole Apostola
    Payroll and Financial Systems Analyst
    Princeton University
    na2344@princeton.edu
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 10.  RE: Subtotals/Totals in PS/Query

    Posted 02-13-2025 10:30 AM

    Good morning Nicole,

    The Alliance19 presentation was Session Number 5691: Aggregate reporting in multiple dimensions using PS Query. It was presented by Longin Gogu.

    We were never able to successfully use the CUBE and ROLLUP operators here at University of Colorado. I spoke to Longin about this last year in Phoenix. He said that Oracle had removed the CUBE and ROLLUP functionality either with HCM 9.3, or in a subsequent PUM. 

    However, the OVER (PARTITION BY) operator does still work in expressions, including nested CASE statements within a single expression to perform complex calculations. I have several huge queries that are using it now.

    FYI: For those Members that have access to the Alliance19 presentations, I highly recommend downloading this one. Even though the second half is obsolete, the first half is still very useful.



    ------------------------------
    Scott Frey
    Senior Data Analyst - Human Resource Information Systems
    University of Colorado
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 11.  RE: Subtotals/Totals in PS/Query

    Posted 02-13-2025 10:36 AM

    Even if you didn't go to Alliance 2019, it's in the library.  You have to drill down to get to it.  Click on the discussion tab, then double-click on Resource Center, then PS Query, then Presentations.  There are folders out there for a bunch of Alliance years, plus some regional conferences.  I recommend looking at all of them and download what you think sounds interesting.


    Also, if you think something is missing, please reach out to Dan (daniel.labrecque@unlv.edu) or myself (pawlowdj@ucmail.uc.edu) and we'd be happy to get it into the library.  There are 6 presentations from Alliance 2019 in the library and this one is included.

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 12.  RE: Subtotals/Totals in PS/Query

    Posted 02-13-2025 10:39 AM

    Oh, good grief.  Click on the library tab, not discussions.  I'm sorry.

     

    Click on the library tab, then double-click on Resource Center, then PS Query, then Presentations.  There are folders out there for a bunch of Alliance years, plus some regional conferences.  I recommend looking at all of them and download what you think sounds interesting.


    Also, if you think something is missing, please reach out to Dan (daniel.labrecque@unlv.edu) or myself (pawlowdj@ucmail.uc.edu) and we'd be happy to get it into the library.  There are 6 presentations from Alliance 2019 in the library and this one is included.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 13.  RE: Subtotals/Totals in PS/Query

    Posted 02-14-2025 12:05 PM
    Edited by Anna Nunez 02-14-2025 12:15 PM

    I may be missing some nuances since I didn't dig deeply into your solutions, but my first thought is that you could create an aggregate expression like SUM(TABLE.FIELD), then use this expression as a field. If you include the field you want broken down in your query, PSQuery should generate a row for each unique value, effectively creating subtotals.

    Ignore this if it's not relevant to your case-I just wanted to share in case it helps someone else reading.



    ------------------------------
    Anna Nunez
    Assistant Registrar for Reporting
    University of Colorado
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 14.  RE: Subtotals/Totals in PS/Query

    Posted 03-06-2025 11:39 AM

    Hello!

    Not sure if this is what you're looking for, but - we have a query that keeps a running subtotal with each row, using the expression SUM(field) OVER (ORDER BY). It's a query of ITEM_LINE_SF arranged in order of posted datetime, so it's essentially a record of every change on the student's account and a subtotal of what their new balance would have been as of that datetime.

    SUM(A.LINE_AMT) OVER(ORDER BY A.POSTED_DATETIME RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    We left the aggregate box unchecked.

    I believe you could do something similar with SUM OVER PARTITION BY, potentially grouping different types of rows and subtotaling them... I haven't tried that yet.

    Our result looks like this: 

    Good luck! :)



    ------------------------------
    Sam Kunz
    Systems Analyst
    Boise State University
    samkunz@boisestate.edu
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


Related Content