PS Query & SQL

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

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:
Sent: 02-12-2025 05:14 PM
From: Shareen Thewke
Subject: Subtotals/Totals in PS/Query

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

 

 

 

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:
Sent: 2/12/2025 5:56:00 PM
From: Sandra Asebedo
Subject: RE: Subtotals/Totals in PS/Query

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

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

Original Message:
Sent: 02-12-2025 05:55 PM
From: Sandra Asebedo
Subject: Subtotals/Totals in PS/Query

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

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:
Sent: 02-13-2025 08:22 AM
From: Nicole Apostola
Subject: Subtotals/Totals in PS/Query

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


Original Message:
Sent: 02-12-2025 05:55 PM
From: Sandra Asebedo
Subject: Subtotals/Totals in PS/Query

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.

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

Original Message:
Sent: 02-13-2025 08:40 AM
From: John Eggenton
Subject: Subtotals/Totals in PS/Query

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:
Sent: 02-13-2025 08:22 AM
From: Nicole Apostola
Subject: Subtotals/Totals in PS/Query

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


Original Message:
Sent: 02-12-2025 05:55 PM
From: Sandra Asebedo
Subject: Subtotals/Totals in PS/Query

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.

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:
Sent: 02-13-2025 10:52 AM
From: John Eggenton
Subject: Subtotals/Totals in PS/Query

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


Original Message:
Sent: 02-13-2025 08:40 AM
From: John Eggenton
Subject: Subtotals/Totals in PS/Query

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:
Sent: 02-13-2025 08:22 AM
From: Nicole Apostola
Subject: Subtotals/Totals in PS/Query

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


Original Message:
Sent: 02-12-2025 05:55 PM
From: Sandra Asebedo
Subject: Subtotals/Totals in PS/Query

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.

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:
Sent: 2/13/2025 10:30:00 AM
From: Scott Frey
Subject: RE: Subtotals/Totals in PS/Query

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:
Sent: 2/13/2025 10:36:00 AM
From: Dana Pawlowicz
Subject: RE: Subtotals/Totals in PS/Query

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.

 

 

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

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