Hi all,

I've working on this for awhile.

The report output should look like something like this: (with subtotals on instructor, dept, co for hrs, enrl, gpapts, gpahrs, gpa, and also for the grade counts and credit hrs below each grade)

CO DEPT INSTRUCTOR CRN STITLE SUBJ CRSE SEC HRS ENRL GPAPTS GPAHRS GPA
AS MATH Doe, John 21897 College Alegbra 1 MATH 101 01 1 10 134.00 55 2.44

A B C D F S U I W AU NR
4 1 4 0 1 0 0 0 1 0 0
16 8 23 0 8 0 0 0 0 0 0


If I leave out the first sums (hrs, enrl, gpapts, gpahrs, gpa) and use a FOLD_LINE on my last BY, which in this case would be SEC, the across the across values are above the BY values, not good.

If I keep the sums(which is what I want in the report) and use the OVER command I get error FOC020 THE SETS OF SORT PHRASES ARE INCONSISTENT. But if I were to get that corrected I would still have the first issue to deal with.

I think I need a new perspective on this report. I'm using SQL passthru to get my data. Using a group by to get the grade counts and credit hours and union all to another query to get the sum gpapts and gpahrs. I could break the grade field into separate fields... cnt_a, sum_credits_a, cnt_b, sum_credits_b,...etc , but I found this takes my report way too long to run up to 12 minutes compared to a minute with the group by query.

Here's the code:

Thanks for any insight you might have.

  
TABLE FILE SQLOUT
SUM 
     MIN_CREDITS/D20 AS 'HRS'
     ACTUAL_ENROLLMENT/D20 AS 'ENRL'
     QUALITY_POINTS/D20 AS 'GPAPTS'
     CREDITS_FOR_GPA/D20 AS 'GPAHRS'
     COMPUTE gpa1/D12.3 = QUALITY_POINTS / CREDITS_FOR_GPA; 
     COMPUTE gpa2/D12.2 = INT(gpa1 * 100) / 100; AS 'GPA'
BY COLLEGE AS 'CO'
BY DEPARTMENT AS 'DEPT'
BY INSTRUCTOR
BY SUBJECT NOPRINT
BY COURSE_NUMBER NOPRINT
BY OFFERING_NUMBER NOPRINT
BY COURSE_REFERENCE_NUMBER AS 'CRN'
BY TITLE_SHORT_DESC AS 'TITLE'
BY SUBJECT AS 'SUBJ'
BY COURSE_NUMBER AS 'CRSE'
BY OFFERING_NUMBER AS 'SEC'
SUM 
     CNT_GRADE/D20 AS '' OVER 
     SUM_CREDITS/D20 AS ''
BY COLLEGE AS 'CO'
BY DEPARTMENT AS 'DEPT'
BY INSTRUCTOR
BY SUBJECT NOPRINT
BY COURSE_NUMBER NOPRINT
BY OFFERING_NUMBER NOPRINT
BY COURSE_REFERENCE_NUMBER AS 'CRN'
BY TITLE_SHORT_DESC AS 'TITLE'
BY SUBJECT AS 'SUBJ'
BY COURSE_NUMBER AS 'CRSE'
BY OFFERING_NUMBER AS 'SEC'
ACROSS FINAL_GRADE COLUMNS A AND B AND C AND D AND F AND S AND U AND I AND W AND AU AND NR
     
ON COLLEGE RECOMPUTE AS '*TOTAL COLLEGE'
     
ON DEPARTMENT RECOMPUTE AS '*TOTAL DEPARTMENT'
     
ON INSTRUCTOR RECOMPUTE AS '*TOTAL INSTRUCTOR'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE SUMMARIZE MIN_CREDITS ACTUAL_ENROLLMENT QUALITY_POINTS CREDITS_FOR_GPA gpa2 CNT_GRADE SUM_CREDITS AS 'TOTAL'
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
$
ENDSTYLE
END

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

Can you do all the summarizations just once using BY for all the sort fields, including FINAL_GRADE. Then HOLD. Then from the hold file, put the stuff that goes on a single line in a SUBHEAD and then do your ACROSS?

I'd be curious as to your student system database, to know what your SQL is looking like so to speak.

Ginny thanks for your advice. When doing the summarizations and subtotals, how do I mention them in the HOLD file?

Leah, we're using SungardHE's Operational Data Store.

Thanks,
Nate

The summarizations I'm talking about are the objects of the SUM verb. Why don't you try SUMing and BYing and HOLDing, then take a look at your hold file. You should see all the BY fields repeated on each line in the hold file. then when you report off of the hold file, you can put all of those columns in the heading as they should be the same for every student.

Just try doing this and see if the pattern of the data in the hold file leads you to a solution.

In the meantime, I'll try to do something with a demo file.

Ginny,

By only SUMming and BYing I get essentially the same data as I have in the SQL I started with.(For each course break down the grade count.)

I think the problem I'm running into is that I'm trying to summarize two different animals - GPA(which is a compute) (essentially by instructor by department by college) and the grade counts(which is across grade by instructor by department by college).



Nate

You need to do a BY on grade then create your hold file. Then in your report against the hold file, you can do the across on grade and put all of the constant stuff in the subhead.

According to your first post, all of your sort fields are in exactly the same order so you can do your multi-verb request but in the second one, do a BY on grade. Then look at your hold file.

I can't repro your request with the IBI test data though I have been trying.

Here is something very simplistic against the car file:

TABLE FILE CAR
SUM SALES RETAIL_COST DEALER_COST 
BY COUNTRY
SUM MPG
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE HOLD
SUM MPG
BY COUNTRY NOPRINT
ON COUNTRY SUBHEAD
" COUNTRY  SALES RETAIL COST DEALER COST"
"<COUNTRY <SALES <RETAIL_COST <DEALER_COST"
ACROSS BODYTYPE
END

Please use your imagination to transfer it to your code.

Looking at this and other posts, I have a related question. I'm trying to do something like this:

TABLE FILE CAR
SUM
	SALES OVER
	RETAIL_COST OVER
	DEALER_COST
BY BODYTYPE
SUM
	SALES OVER
	RETAIL_COST OVER
	DEALER_COST
BY BODYTYPE
ACROSS COUNTRY
END

I get "(FOC020) THE SETS OF SORT PHRASES ARE INCONSISTENT". How would I do this...or can can I do this?

There error message your getting is the answer. Any sort fields you have in the second verb phrase must also appear and in the same order in the first verb phrase. ACROSS is an additional sort and since you only have it in your second phrase, you get the error.

One approach to this, although there are probably many different ways to do what you want, would be to define fields which would calculate the totals you display in your first part and then just embed those caluclated fields in subheading of the second part.

quote:

ACROSS is an additional sort and since you only have it in your second phrase, you get the error.

Not entirely true actually, b/c the following works fine:

TABLE FILE CAR
SUM
	SALES
	RETAIL_COST
	DEALER_COST
BY BODYTYPE
SUM
	SALES
	RETAIL_COST
	DEALER_COST
BY BODYTYPE
ACROSS COUNTRY
END

Only difference is the "OVER"s are removed.