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?

Answer 1

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

Answer 2

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.

Answer 3

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.