I have a three column report:

Field 1 is the Plan Code (the group by field)

Field 2 is the number of employees in that plan code

Field 3 is the Percent of the TOTAL population that is in that specific plan code.

I saw this thread, but can not get it to apply to my report:

Calculating percentages off a subtotal line

Could some please point me in the right direction for any documentation on this?

My Code:

JOIN CLEAR *

JOIN
ACTIVEPOP.EMP_SSN
IN ACTIVEPOP TO ALL CEESTATUS.EMP_SSN
IN CEESTATUS
AS J1
END

TABLE FILE ACTIVEPOP
PRINT
ACTIVEPOP.PRD_DATE AS 'PRD_DATE'
ACTIVEPOP.EMP_SSN AS 'EMP_SSN'
ACTIVEPOP.PENSION_PLAN_NBR AS 'PENSION_PLAN_NBR'
ACTIVEPOP.EMPLOYEE_STATUS_CODE AS 'EMPLOYEE_STATUS_CODE'
ACTIVEPOP.BATCH_TERM_PROCESS_PENDING_IND AS 'BATCH_TERM_PROCESS_PENDING_IND'
ACTIVEPOP.PAYMENT_FREQUENCY AS 'PAYMENT_FREQUENCY'
ACTIVEPOP.BENEFIT_PAYMENT_OPTION AS 'BENEFIT_PAYMENT_OPTION'
CEESTATUS.ELIGIBILITY_STATUS_CODE AS 'ELIGIBILITY_STATUS_CODE'
CEESTATUS.GI_EMPLOYEE_STATUS_CODE AS 'GI_EMPLOYEE_STATUS_CODE'

ON TABLE HOLD AS ACTIVEPOP2
END

DEFINE FILE ACTIVEPOP2
CNTR/I8= WITH PRD_DATE = 1;
END

TABLE FILE ACTIVEPOP2
SUM
	TOT.ACTIVEPOP2.EMP_SSN
	ACTIVEPOP2.PENSION_PLAN_NBR AS 'Plan Code'
	CNT.ACTIVEPOP2.EMP_SSN AS '# of Emps'
	CNTR
COMPUTE PERCENT/D6.2% = (C3/(C1/C4))*100;
BY PRD_DATE RECOMPUTE
BY ACTIVEPOP2
END

BY ACTIVEPOP2.PENSION_PLAN_NBR NOPRINT

WHERE ACTIVEPOP2.ELIGIBILITY_STATUS_CODE NE 'D'
WHERE ACTIVEPOP2.GI_EMPLOYEE_STATUS_CODE NE 'D'

ON TABLE PCHOLD FORMAT EXL2K
END

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

Perhaps using WITHIN in a PCT calculation will do what you require.

Try searching for PCT WITHIN in this forum.

TABLE FILE ACTIVEPOP2
SUM
ACTIVEPOP2.PENSION_PLAN_NBR AS 'Plan Code'
CNT.ACTIVEPOP2.EMP_SSN AS '# of Emps'
PCT.EMP_SSN/D6% WITHIN ACTIVEPOP2 AS 'Pct%'

BY ACTIVEPOP2.PENSION_PLAN_NBR NOPRINT

ON TABLE RECOMPUTE

Gives me:


(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: EMP_SSN

How about PCT.CNT.

TABLE FILE EMPLOYEE
SUM CNT.EMP_ID
PCT.CNT.EMP_ID
BY DEPARTMENT
END

That worked! Thank you GinnyJakes!

My code:

JOIN CLEAR *

JOIN
ACTIVEPOP.EMP_SSN
IN ACTIVEPOP TO ALL CEESTATUS.EMP_SSN
IN CEESTATUS
AS J1
END

TABLE FILE ACTIVEPOP
PRINT
ACTIVEPOP.PRD_DATE AS 'PRD_DATE'
ACTIVEPOP.EMP_SSN AS 'EMP_SSN'
ACTIVEPOP.PENSION_PLAN_NBR AS 'PENSION_PLAN_NBR'
ACTIVEPOP.EMPLOYEE_STATUS_CODE AS 'EMPLOYEE_STATUS_CODE'
ACTIVEPOP.BATCH_TERM_PROCESS_PENDING_IND AS 'BATCH_TERM_PROCESS_PENDING_IND'
ACTIVEPOP.PAYMENT_FREQUENCY AS 'PAYMENT_FREQUENCY'
ACTIVEPOP.BENEFIT_PAYMENT_OPTION AS 'BENEFIT_PAYMENT_OPTION'
CEESTATUS.ELIGIBILITY_STATUS_CODE AS 'ELIGIBILITY_STATUS_CODE'
CEESTATUS.GI_EMPLOYEE_STATUS_CODE AS 'GI_EMPLOYEE_STATUS_CODE'

WHERE CEESTATUS.ELIGIBILITY_STATUS_CODE NE 'D'
WHERE CEESTATUS.GI_EMPLOYEE_STATUS_CODE NE 'D'

ON TABLE HOLD AS ACTIVEPOP2
END

-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Final - Active Population - Summary
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
TABLE FILE ACTIVEPOP2
SUM
ACTIVEPOP2.PENSION_PLAN_NBR AS 'Plan Code'
CNT.ACTIVEPOP2.EMP_SSN AS '# of Emps'
PCT.CNT.ACTIVEPOP2.EMP_SSN AS '# of Total'

BY ACTIVEPOP2.PENSION_PLAN_NBR NOPRINT

ON TABLE PCHOLD FORMAT EXL2K
END

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

In the line:

PCT.CNT.ACTIVEPOP2.EMP_SSN AS '# of Total'

The result displays as "21.00". Would I use the stylesheet to make that display as "21.00%"?

Put a slash after the EMP_SSN with the format that you want: EMP_SSN/D6.2%.

Just change the number format.

TABLE FILE CAR
SUM
CNT.MODEL AS 'Model Count'
PCT.CNT.MODEL/F6.2% AS 'Percentage'
BY COUNTRY
ON TABLE SUBTOTAL
END

Very cool!

Thank you both.