Hello all,

I'm designing my 2nd pivot grid, and the counts on the pivot grid are not the same as the raw data.  The query I have is a grade distribution by division, term, subject, catalog, instructor, session, and mode.  I then threw in the section number, hoping that would solve the problem. The SQL is below. 

An example of one that is correct is PSY on the pivot grid has 1025 grades, which matches my raw data.  But then ECON has 1002 for a count on the pivot grid, but the raw data is 873.  I thought that it was a meeting pattern that was duplicating grades, so I limited the query to meeting pattern = 1, but then other numbers were different on other subjects.  I also just noticed that the count for the term has a total of 8514, but the raw data is 8159.

Has anybody encountered their raw data being one number, but the pivot grid calculates a different number?  Any suggestions on how I can get the pivot grid to calculate correctly?

Thank you!

Lee

SELECT DISTINCT A.EMPLID, C.DESCR, B.SUBJECT, B.CATALOG_NBR, E.NAME, B.SESSION_CODE, A.CRSE_GRADE_OFF, B.INSTRUCTION_MODE, B.CLASS_SECTION
  FROM PS_STDNT_ENRL A, PS_CLASS_TBL B, PS_TERM_TBL C, PS_CLASS_INSTR_VW D, PS_PEOPLE_SRCH E
  WHERE ( C.STRM = A.STRM
    AND C.ACAD_CAREER = A.ACAD_CAREER
    AND C.INSTITUTION = A.INSTITUTION
    AND E.OPRCLASS = 'HCPPALL'
    AND ( A.ACAD_CAREER = 'UGRD'
     AND A.INSTITUTION = 'TMCC1'
     AND A.STRM = '2252'
     AND A.STDNT_ENRL_STATUS = 'E'
     AND A.GRADING_BASIS_ENRL NOT IN ('NON','NOG','TEC')
     AND A.ENRL_STATUS_REASON IN ('ENRL','EWAT','WDRW')
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.INSTITUTION = B.INSTITUTION
     AND A.STRM = B.STRM
     AND A.CLASS_NBR = B.CLASS_NBR
     AND B.SESSION_CODE = A.SESSION_CODE
     AND B.CRSE_ID = D.CRSE_ID
     AND B.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR
     AND B.STRM = D.STRM
     AND B.SESSION_CODE = D.SESSION_CODE
     AND B.CLASS_SECTION = D.CLASS_SECTION
     AND B.ACAD_GROUP IN ('T1540','T154U')
     AND D.INSTR_ROLE = 'PI'
     AND E.EMPLID = D.EMPLID
     AND E.NAME_TYPE = 'PRI' ))

Hello all,

An update on this.  It looks like the "Distinct" is not obeying when the pivot grid is generating.  Also, when I do a "Count" with the Distinct on inside the normal query in the fields tab, it isn't calculating correctly.  Here is the SQL when I do the count.  Do you think this is a bug or is there a good way to get around this issue?

Hello all,

I'm designing my 2nd pivot grid, and the counts on the pivot grid are not the same as the raw data.  The query I have is a grade distribution by division, term, subject, catalog, instructor, session, and mode.  I then threw in the section number, hoping that would solve the problem. The SQL is below. 

An example of one that is correct is PSY on the pivot grid has 1025 grades, which matches my raw data.  But then ECON has 1002 for a count on the pivot grid, but the raw data is 873.  I thought that it was a meeting pattern that was duplicating grades, so I limited the query to meeting pattern = 1, but then other numbers were different on other subjects.  I also just noticed that the count for the term has a total of 8514, but the raw data is 8159.

Has anybody encountered their raw data being one number, but the pivot grid calculates a different number?  Any suggestions on how I can get the pivot grid to calculate correctly?

Thank you!

Lee

SELECT DISTINCT A.EMPLID, C.DESCR, B.SUBJECT, B.CATALOG_NBR, E.NAME, B.SESSION_CODE, A.CRSE_GRADE_OFF, B.INSTRUCTION_MODE, B.CLASS_SECTION
  FROM PS_STDNT_ENRL A, PS_CLASS_TBL B, PS_TERM_TBL C, PS_CLASS_INSTR_VW D, PS_PEOPLE_SRCH E
  WHERE ( C.STRM = A.STRM
    AND C.ACAD_CAREER = A.ACAD_CAREER
    AND C.INSTITUTION = A.INSTITUTION
    AND E.OPRCLASS = 'HCPPALL'
    AND ( A.ACAD_CAREER = 'UGRD'
     AND A.INSTITUTION = 'TMCC1'
     AND A.STRM = '2252'
     AND A.STDNT_ENRL_STATUS = 'E'
     AND A.GRADING_BASIS_ENRL NOT IN ('NON','NOG','TEC')
     AND A.ENRL_STATUS_REASON IN ('ENRL','EWAT','WDRW')
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.INSTITUTION = B.INSTITUTION
     AND A.STRM = B.STRM
     AND A.CLASS_NBR = B.CLASS_NBR
     AND B.SESSION_CODE = A.SESSION_CODE
     AND B.CRSE_ID = D.CRSE_ID
     AND B.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR
     AND B.STRM = D.STRM
     AND B.SESSION_CODE = D.SESSION_CODE
     AND B.CLASS_SECTION = D.CLASS_SECTION
     AND B.ACAD_GROUP IN ('T1540','T154U')
     AND D.INSTR_ROLE = 'PI'
     AND E.EMPLID = D.EMPLID
     AND E.NAME_TYPE = 'PRI' ))

You are right, the D table is the culprit.  When I add that record, that is when I get more rows to be "counted" upon. I did find the pivot grid in PS that has "Count Distinct", but I can't get that to work either! First screenshot. 

I did find a PP from 2006 that says I can write an expression.  But I am trying to avoid expressions because Pivot Grids in PS don't like them.  I can go after a view and try to build my Pivot Grid from the view. But I'm trying to build this pivot grid without any help from the outside, just to challenge myself, and this isn't a priority.  2nd screenshot and attaching the PP. 

I'm playing with Subqueries to see if I can get that to work to make it distinct, but I think I've worn out my brain at this time and need to get some lunch. Just in case you are interested in Pivot Grids in PS, attaching that PP too from 2017.

First Screenshot:

Second Screenshot:

Thank you!

Lee

 

----------------------------------------

Lee Raubolt, M.S. 

Truckee Meadows Community College

Associate Director of Admissions & Enrollment Services

7000 Dandini Blvd.

RDMT 319

Reno, NV 89512 USA

775-674-7624

lraubolt@tmcc.edu 

On Thu, Aug 7, 2025 at 10:25 AM Jeffrie Brooks via Higher Education User Group <Mail@heug.org> wrote:

Hi Lee, I'll preface by saying that these tables are bit outside my wheelhouse, and I have 0 experience working with pivot grids - pivot tables in...

PS Query & SQL Post New Discussion Re: Pivot Grid not calculating correctly for Grade Distribution Reply to Thread Aug 7, 2025 1:23 PM Jeffrie Brooks Hi Lee, I'll preface by saying that these tables are bit outside my wheelhouse, and I have 0 experience working with pivot grids - pivot tables in Excel have been sufficient for my business needs so far ��. That said, I think the issue that you are seeing is a universal 'gotcha' that often surfaces when working with aggregates in PS Query and SQL; Any key fields that aren't appropriately accounted for will create inflated numbers in your aggregates. In your case, my hunch is that PS_CLASS_INSTR is the culprit. If you run your SQL again but without tables D and E, does that give you the expected number? If so, then I think you will need to take a look at the key fields, CLASS_MTG_NBR, and INSTR_ASSIGN_SEQ. They will need to be set to some criteria, or returned in your SELECT and GROUP BY statements. Hopefully that helps! Jeffrie -- Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu   Reply to Community   Reply to Sender via Email   View Thread   Recommend   Original Message: Sent: 8/7/2025 12:22:00 PM You are receiving this notification because you followed the 'Pivot Grid not calculating correctly for Grade Distribution' message thread. If you do not wish to follow this, please click here. Update your email preferences to choose the types of email you receive Unsubscribe from all participation emails

Hello all,

An update on this.  It looks like the "Distinct" is not obeying when the pivot grid is generating.  Also, when I do a "Count" with the Distinct on inside the normal query in the fields tab, it isn't calculating correctly.  Here is the SQL when I do the count.  Do you think this is a bug or is there a good way to get around this issue?