How does Ellucian Banner calculate GPA?

Question

I am working with Ellucian Banner, and I am having a hard time calculating the GPA. (Banner is an ERP used at academic institutions.)

The GPA can be found in many of the views that are built into Banner, including AS_STUDENT_DATA. However, these views are very slow and we have a few reports that are run several times where only the GPA is needed. I am trying to extract the GPA, but the values that I am getting don't all match what is in the views.

Please note that I am able to calculate a GPA using one of many sources on the web, however my values don't perfectly match values in Banner views. (In other words, I am not asking how to calculate a general GPA, which is easy and well documented, but asking how this is done in Banner.)

I have this, which gives values that are close but not all correct:

    SELECT PIDM, 
        round(sum(TOTAL_POINTS)/sum(TOTAL_CREDITS), 2) AS GPA, 
        round(TOTAL_POINTS, 2) AS TOTAL_POINTS, TOTAL_CREDITS, LEVL_CODE   
    FROM (
        SELECT 
            SFRSTCR.SFRSTCR_PIDM AS PIDM, 
            sum(SHRGRDE.SHRGRDE_QUALITY_POINTS * SFRSTCR.SFRSTCR_CREDIT_HR) AS TOTAL_POINTS,
            sum(SFRSTCR.SFRSTCR_CREDIT_HR) AS TOTAL_CREDITS,
            SHRGRDE_LEVL_CODE AS LEVL_CODE
        FROM 
        INNER JOIN SHRGRDE ON SFRSTCR.SFRSTCR_GRDE_CODE = SHRGRDE.SHRGRDE_CODE AND SHRGRDE.SHRGRDE_GPA_IND = 'Y'
        WHERE SHRGRDE_GPA_IND = 'Y'   
          AND SFRSTCR.SFRSTCR_RSTS_CODE IN ('RE', 'RW', 'RR')
        GROUP BY SFRSTCR.SFRSTCR_PIDM, SHRGRDE_LEVL_CODE -- , SFRSTCR.SFRSTCR_CRN, SFRSTCR_TERM_CODE
      ) GT
      WHERE GT.TOTAL_CREDITS > 0 -- Prevent x/0 errors
      GROUP BY PIDM, TOTAL_POINTS, TOTAL_CREDITS, LEVL_CODE

Has anyone tackled this problem? Any idea how Banner does it?

I can tell you that grouping on columns and then doing aggregations on those columns doesn't make any sense at all. It's probably a big part of your problem. — shawnt00, Aug 14 '15 at 16:52
Er... why are you asking us instead of your SIS provider? Assuming you've purchased the product, you can usually get someone to tell you how the calculations work. You might have to fight a bit to get to an actual engineer, but they will be able to do so. Offer to sign an NDA and they'll usually provide you the source code. — Bacon Bits, Aug 14 '15 at 16:57

score 1 · Answer 1 · answered Oct 24 '16 at 18:47

you can use the built-in function for banner. its under a package called SHKSELS. the function is called F_SHRLGPA_VALUE. the owner of SHKSELS is BANINST1. the inputs for the function are pidm, credit level, indicator type, GPA type, type of request, campus type, term.

here is a break down and then an example use.

input 1 - pidm --self explanatory

input 2 - credit level value -- options are found by using

    select * from stvlevl;

input 3 - indicator type -- Options are GPA (calculated GPA) or QP (Quality Points)

input 4 - GPA Type -- Options are found using

    select distinct shrlgpa_gpa_type_ind from shrlgpa;

input 5 - type of request -- Options are V (value of input 3) or HA (Hours Attempted) or HE (Hours Earned) or HP (Hours Passed) or H (Hours toward GPA)

input 6 - campus type -- options are found by using

    select * from stvcamp;

input 7 - term -- self explanatory

Most inputs can be NULL if you dont want to be that specific.

EXAMPLE:

    SELECT  SPRIDEN_ID as IS_NUMBER,
            SHKSELS.F_SHRLGPA_VALUE(SPRIDEN_PIDM,'01','GPA','I','V',NULL,NULL) as GPA
    FROM    SPRIDEN
    WHERE   SPRIDEN_CHANGE_IND IS NULL;

Hope that helps.

score 1 · Answer 2 · edited Apr 17 '18 at 00:03

Over release banner8..x if the real or final GPA already was calculated, then it was got the final grades from Academic History tables ( SHRTCKN, SHRTCKG, SHRTCKL) then you can get the GPA from SHRTGPA and SHRLGPA tables (calculated at term and level respectly)

If you need to recalculates the GPA then you will use the shkcgpa.p_term_gpa with pidm and term by parameters. Therefore both GPA are recalculated.

shawnt00 · Answer 3 · 2015-08-14T17:15:57.510

Here's a guess. Hopefully it's closer.

SELECT
    PIDM, LEVL_CODE,
    round(sum(TOTAL_POINTS) / sum(TOTAL_CREDITS), 2) AS GPA, 
    sum(TOTAL_POINTS) AS TOTAL_POINTS, sum(TOTAL_CREDITS) AS TOTAL_CREDITS
FROM (
    SELECT 
        SFRSTCR.SFRSTCR_PIDM AS PIDM, SHRGRDE_LEVL_CODE AS LEVL_CODE,
        sum(SHRGRDE.SHRGRDE_QUALITY_POINTS * SFRSTCR.SFRSTCR_CREDIT_HR) AS TOTAL_POINTS,
        sum(SFRSTCR.SFRSTCR_CREDIT_HR) AS TOTAL_CREDITS
    FROM
        SFRSTCR INNER JOIN SHRGRDE ON SFRSTCR.SFRSTCR_GRDE_CODE = SHRGRDE.SHRGRDE_CODE
    WHERE
        SHRGRDE_GPA_IND = 'Y' AND SFRSTCR.SFRSTCR_RSTS_CODE IN ('RE', 'RW', 'RR')
    GROUP BY
        SFRSTCR.SFRSTCR_PIDM, SHRGRDE_LEVL_CODE
  ) GT
  WHERE TOTAL_CREDITS > 0 -- Prevent x/0 errors
  GROUP BY PIDM, LEVL_CODE

How does Ellucian Banner calculate GPA?

3 Answers3