I'm trying to build a FOCUS report equivalent of what we are currently using form Oracle and MS-Access that will show overall totals by college and Average GPA by college for Applicants, Accepts, Enrolled, etc. So far, I can get either the correct count totals, or the correct Avg GPA but only for Applicants by adding an "AND gpa is not null" to the sql where clause. The totals should count everyone regardless of whether they have a GPA while the GPA should only be calculated if there is a GPA. I tried a few formulas as well as the AVE. option and got same result. I am getting the data via SQL statements as not too familiar with FOCUS yet. Eventually the report will be a drill down. Thanks for your help and ideas.

Here is a sample layout of output (all numbers made up so won't add up GPA wise ):

College   Applied   Accepted  Deposit  ApGPA  AcGPA DepGPA
AA         500      300         120    3.60    3.68  3.70
BB          25       18           5    3.00    3.25  3.26   
*Total     525      318         125    3.50    3.30  3.37
 

Not shown in above counts would be the missing gpa, let's say the missing counts are :

College   Applied   Accepted  Deposit  
AA         100       70          20    
BB          05       08           1     
*Total     105       78          21    
 

Here is the code listing

ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select distinct
        person_uid, academic_period, student_level,  stu_population, college,
               APPLIED,
               ACCEPTED,
               Deposit,
			   GPA
       from population_table a
       where SUBSTR(PROGRAM,1,2) <> 'ND'
	   AND college NOT IN ('00', 'LW')
       AND stu_population   not in ('J', 'S', 'V')
       AND academic_period between '201130' and '201140'
	   AND trunc(date_updated) = trunc(sysdate)
	   
;
END
TABLE FILE SQLOUT
PRINT
     COMPUTE TERMYR/A4 = EDIT(ACADEMIC_PERIOD, '9999$$');
     COMPUTE STU_LEVEL/A2 = IF STUDENT_LEVEL EQ 'GR' OR STUDENT_LEVEL EQ 'DR' THEN 'GR' ELSE STUDENT_LEVEL;
     COMPUTE STU_POP/A1 = IF STU_POPULATION EQ 'G' OR STU_POPULATION EQ 'D' OR STU_POPULATION EQ 'Y' OR STU_POPULATION EQ 'Z' THEN 'G' ELSE STU_POPULATION;
     COLLEGE
     APPLIED
     ACCEPTED
     COMPUTE DEP/D12.2 = DEPOSIT;
	 GPA
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS STATSLOTDETAIL FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
DEFINE FILE STATSLOTDETAIL ADD
GPANum/D12.2=ATODBL(GPA, '12', 2);
AvgGPA/D12.2=GPANum / APPLIED;
AvgAcGPA/D12.2=GPANum / ACCEPTED;
END
TABLE FILE STATSLOTDETAIL
SUM
     APPLIED/I5
     ACCEPTED/I5
     DEP/I5 AS 'DEPOSIT'
     GPA
     COMPUTE AplGPA/D12.2 = IF GPANum EQ 0 OR GPANum EQ MISSING THEN 1 / 0 ELSE GPANum / APPLIED;
     AVE.AvgGPA AS 'AVE,AvgGPA'
	 AVE.AvgAcGPA AS 'AVE,AvgAcGPA'
BY  LOWEST TERMYR
BY  HIGHEST STU_LEVEL AS 'STUDENT LEVEL'
BY  LOWEST STU_POP
BY  LOWEST COLLEGE

ON STU_LEVEL RECOMPUTE AS '*TOTAL'

ON STU_POP RECOMPUTE AS '*TOTAL STU_POP'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SUMMARIZE AvgGPA AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT,
     COLUMN=N4,
     SQUEEZE=0.500000,
$
ENDSTYLE
END

This message has been edited. Last edited by: sxschech,

Hi sxschech,

Since you are not too familiar with FOCUS, the choice would be to open a case with Customer Support Services and work through it with support's help. You may either call at 1-800-736-6130, or access online at InfoResponse.

Cheers,

Kerry

Hi Kerry,

Thank you for your suggestion to contact Customer Support Services. The technician pointed me to a website, which I think I had seen before, but still could not understand it. But in talking with him, he mentioned something about needing to calculate the counts for each type that I wanted to do the average on in order to exclude the 0's and blanks. After some further experimenting, I think I got it working. What I came up with was creating separate counts and averages and using the IS MISSING like:

COMPUTE APGPACount/I5 = IF GPA IS MISSING THEN 0 ELSE APPLIED;

Here is the revised code text:

ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select distinct
        person_uid, academic_period, student_level,  stu_population, college,
               APPLIED,
               ACCEPTED,
               Deposit,
			   GPA
       from population_table a
       where SUBSTR(PROGRAM,1,2) <> 'ND'
	   AND college NOT IN ('00', 'LW')
       AND stu_population   not in ('J', 'S', 'V')
       AND academic_period between '201130' and '201140'
	   AND trunc(date_updated) = trunc(sysdate)
	   
;
END
TABLE FILE SQLOUT
PRINT
     COMPUTE TERMYR/A4 = EDIT(ACADEMIC_PERIOD, '9999$$');
     COMPUTE STU_LEVEL/A2 = IF STUDENT_LEVEL EQ 'GR' OR STUDENT_LEVEL EQ 'DR' THEN 'GR' ELSE STUDENT_LEVEL;
     COMPUTE STU_POP/A1 = IF STU_POPULATION EQ 'G' OR STU_POPULATION EQ 'D' OR STU_POPULATION EQ 'Y' OR STU_POPULATION EQ 'Z' THEN 'G' ELSE STU_POPULATION;
     COLLEGE
     APPLIED
     ACCEPTED
     COMPUTE DEP/D12.2 = DEPOSIT;
	 GPA
	 COMPUTE APGPACount/I5 = IF GPA IS MISSING THEN 0 ELSE APPLIED;
	 COMPUTE ACGPACount/I5 = IF GPA IS MISSING THEN 0 ELSE ACCEPTED;
	 COMPUTE DEGPACount/I5 = IF GPA IS MISSING THEN 0 ELSE DEP;
	 
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS STATSLOTDETAIL FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
DEFINE FILE STATSLOTDETAIL ADD
GPANum/D12.2=ATODBL(GPA, '12', 2);
GPANumAcc/D12.2 = IF ACGPACount = 0 AND ACCEPTED = 0 THEN 0 ELSE GPANum;
GPANumDep/D12.2 = IF DEGPACount = 0 AND DEP = 0 THEN 0 ELSE GPANum;
END
TABLE FILE STATSLOTDETAIL
SUM 
     APPLIED/I5
     ACCEPTED/I5
     DEP/I5 AS 'DEPOSIT'
     COMPUTE AvGPAApplied/D12.2 = GPANum / APGPACount;
     COMPUTE AvGPAAccepted/D12.2 = GPANumAcc / ACGPACount;
     COMPUTE AvGPADeposited/D12.2 = GPANumDep / DEGPACount;
BY  LOWEST TERMYR
BY  HIGHEST STU_LEVEL AS 'STUDENT LEVEL'
BY  LOWEST STU_POP
BY  LOWEST COLLEGE
     
ON STU_LEVEL RECOMPUTE AS '*TOTAL'
     
ON STU_POP RECOMPUTE AS '*TOTAL STU_POP'
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT,
     COLUMN=N4,
     SQUEEZE=0.500000,
$
ENDSTYLE
END

How do I mark as solved?

Hi sxschech,

Thank you very much for confirmation. To update the subject line, you can go back to the very first post, click on the yellow "folder and pencil" icon underneath the message, edit the subject line in the new window, and click on Post after done with editing.

Hope this helps. Or if preferred, please let me know and I will do the updating over here.

Cheers,

Kerry

Hi Kerry,

Thanks for the instructions on editing the message header.