I'm working on a report that needs to show the total number of 'active' apps, total number of apps 'on hold' and then the % of 'on hold'. The details of the report should only show the apps that are 'on hold'. This report is then bursted on area_id. I'm having trouble getting the counts to display correctly. Currently, I am using st.fieldname to get the subtotal of the field once the report is bursted. This is making it difficult for me to compute the % field and also displaying the data in the subhead instead of the heading above the detail of the report. Here is the code so far:

SET ASNAMES = ON
JOIN CLEAR *
JOIN PRJCT_MGR_PRSN_SEQ_NUM IN ISIT_PROJECT_INFO_MV TO PRSN_SEQ_NUM IN AIM_PERSON AS J0
END

-********************************************************************************
-*SUM ACTIVE APPS AND APPS ON HOLD
-********************************************************************************
DEFINE FILE ISIT_PROJECT_INFO_MV
OH_IND/I8=IF PRJCT_STATS_LKUP_CODE EQ 'OH' THEN 1 ELSE 0;
ACTIVE/I8=IF PRJCT_STATS_LKUP_CODE EQ 'A' OR 'OH' THEN 1 ELSE 0;
END

TABLE FILE ISIT_PROJECT_INFO_MV
PRINT
CTRY_ID
AREA_ID
RGN_ID AS 'DIST'
COMPUTE PM/A250 = LAST_NAME || ', ' | FIRST_NAME;
PRJCT_PRNT_ORG_NAME AS 'CUSTOMER'
BUS_UNIT AS 'BUN'
LATEST_ON_HOLD_START_DATE AS 'DATE_OH'
TOTAL_ON_HOLD_NUM AS 'DAYS_OH'
LATEST_ON_HOLD_FOLLOWUP_DATE AS 'FOLLOW_UP'
PRJCT_SEQ_NUM
PRJCT_STATS_LKUP_CODE AS 'STATUS'
ACTIVE
OH_IND
BY ENG_APP_ID
WHERE ENG_APP_ID IS NOT MISSING
AND PRJCT_STATS_LKUP_CODE EQ 'OH' OR 'A'
ON TABLE HOLD AS AA_DATA
END
-*
TABLE FILE AA_DATA
PRINT ACTIVE STATUS
BY AREA_ID
BY ENG_APP_ID
ON TABLE HOLD AS HLDACTX
END
DEFINE FILE HLDACTX
A_ENG_APP_ID/A8=IF STATUS EQ 'OH' THEN EDIT(ENG_APP_ID) ELSE
IF AREA_ID EQ LAST AREA_ID THEN A_ENG_APP_ID ELSE ' ';
END
TABLE FILE HLDACTX
SUM ACTIVE A_ENG_APP_ID
BY AREA_ID
ON TABLE HOLD AS HLDACT0
END
DEFINE FILE HLDACT0
IENG_APP_ID/I8=EDIT(A_ENG_APP_ID);
DENG_APP_ID/D8C=IENG_APP_ID;
END
TABLE FILE HLDACT0
PRINT ACTIVE
BY DENG_APP_ID
ON TABLE HOLD AS HLDACT1 FORMAT FOCUS INDEX DENG_APP_ID
END
-*
-********************************************************************************
-*GATHER ON HOLD COMMENTS
-********************************************************************************
SQL SQLORA SET SERVER isitdev
SQL SQLORA
select p.eng_app_id
,p.prjct_seq_num
,p.prjct_stats_lkup_code
,p.bus_unit
,c.cmmnt_text
,p.ctry_id
from isite.project_on_hold t, isite.comments c, project_info_mv p
where p.prjct_seq_num = t.prjct_seq_num
and t.prjct_seq_num = c.prnt_table_row_seq_num
and p.prjct_stats_lkup_code = 'OH'
and c.cmmnt_text is not null
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS CMMNTS
END

DEFINE FILE CMMNTS
COMMENT_TEXT/A4000=CMMNT_TEXT;
ENG_APP_ID/D8c=ENG_APP_ID;
END

TABLE FILE CMMNTS
PRINT CTRY_ID
PRJCT_SEQ_NUM
PRJCT_STATS_LKUP_CODE
BUS_UNIT
COMMENT_TEXT
BY ENG_APP_ID
ON TABLE HOLD AS AA_CMMNTS FORMAT ALPHA
END
-*? HOLD AA_CMMNTS
-********************************************************************************
-*ISOLATE OH APPS BEFORE JOINING TO COMMENTS
-********************************************************************************

DEFINE FILE AA_DATA
APP_ID/D8c = ENG_APP_ID;
END
-*
END
-*
TABLE FILE AA_DATA
PRINT
CTRY_ID
DIST
AREA_ID
DIST
PM
CUSTOMER
BUN
DATE_OH
DAYS_OH
FOLLOW_UP
PRJCT_SEQ_NUM
ACTIVE
OH_IND
STATUS
BY APP_ID
BY AREA_ID
ON TABLE HOLD AS FNLDATA
END
-*? HOLD FNLDATA
-*EXIT
-********************************************************************************
-*JOIN TO COMMENTS TABLE
-********************************************************************************
JOIN CLEAR *
JOIN APP_ID IN FNLDATA TO ENG_APP_ID IN AA_CMMNTS AS J1
JOIN APP_ID IN FNLDATA TO DENG_APP_ID IN HLDACT1 AS JJ2
-********************************************************************************
-* FINAL REPORT - CURRENT ON HOLD APPS ONLY WITH COMMENTS
-********************************************************************************
TABLE FILE FNLDATA
HEADING
"Application On Hold Report"
"For: "As of: <+0>&DATETDMYY <+0> "
" "
SUM
HLDACT1.ACTIVE NOPRINT
OH_IND NOPRINT
PRINT
APP_ID
DIST
PM
CUSTOMER
BUN
DATE_OH
DAYS_OH
FOLLOW_UP
PRJCT_SEQ_NUM
COMMENT_TEXT AS 'COMMENTS'
COMPUTE PCT_OH/D20.2% = (OH_IND*100)/ HLDACT1.ACTIVE; NOPRINT
BY AREA_ID PAGE-BREAK NOPRINT
ON TABLE RECOMPUTE
ON AREA_ID SUBFOOT
"Total Applications in OH status: "Total Active Applications: "% Application OH: " "
BY DIST NOPRINT
BY APP_ID NOPRINT
WHERE STATUS EQ 'OH'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=0.625000,
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=1.388889,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=1.500000,
$
-* ****************************************************
-* DRILL DOWN FUNCTIONS
-* ****************************************************
-* Drill Down to 20/20 Appliation...
TYPE=DATA, COLUMN=N2,TARGET=_blank,
URL=&&CC_SITE/engapplic/projinfo/(ProjectDetail.do?command='load'&|retLoc='milestoneTable'&|fromLoc='projInfo'&|projId=PRJCT_SEQ_NUM),$
ENDSTYLE
END

This message has been edited. Last edited by: Selena B,

Any input is greatly appreciated!

heading should include:
< AREA_ID after "For:
Trying to get output to display like the following

Application On Hold Report
For: MWA
As of: 6 AUG, 2008
Total Applications in OH status: 11
Total Active Applications: 225632
% Application OH: .05%
AREA_OH APP_ID DIST PM CUSTOMER BUN DATE_OH DAYS_OH FOLLOW_UP PRJCT_SEQ_NUM COMMENTS

This message has been edited. Last edited by: Selena B,

Here's how. Use two verbs (SUM and PRINT). Base your percent on the verb objects of the first verb; apply a WHERE TOTAL to exclude detail lines, screening on the appropriate verb object of the PRINT. All of the detail rows will be accounted for in the SUM value; but the unwanted rows will be excluded from printing.

DEFINE FILE CAR
  ACTIVE/I3 = (SEATS EQ 4);
  ON_HOLD/I3= NOT ACTIVE;
END
TABLE FILE CAR
SUM SUM.ACTIVE SUM.ON_HOLD 
COMPUTE PCT_HOLD/D6.2 = 100 * SUM.ON_HOLD/(SUM.ACTIVE + SUM.ON_HOLD);
  BY COUNTRY
PRINT 
  ACTIVE ON_HOLD
  SEATS 
  BY COUNTRY    BY CAR   BY MODEL 
WHERE TOTAL ON_HOLD EQ 1;
END 

Since ON_HOLD is an object of both verbs (the first as a summary value, the second as a detail value), I prefixed the first with a redundent SUM., to enable the WHERE TOTAL to reference the second one.

This message has been edited. Last edited by: j.gross,

Thank you for your response. I will try that. What does the "Not Active" in the define do? And I'm not sure I understand this line:
ACTIVE/I3 = (SEATS EQ 4);

What is the purpose of setting it to 4?

ACTIVE/I3 = (SEATS EQ 4);
doesn't *assign* the value 4 -- It *tests* whether SEATS has the value 4. The line is equivalent to
ACTIVE/I3 = IF (SEATS EQ 4) THEN 1 ELSE 0;

I was looking to convert CAR into a specimen of ACTIVE and ON_HOLD detail rows to illustrate the method. I arbitrarily made seats = 4 characterize an "active" item.

Since I have only two possible states, I can define ON_HOLD as the opposite of ACTIVE: "ON_HOLD = NOT ACTIVE;"
(note that "NOT x" evaluates to 1 if x is zero, and 0 otherwise.)

You would probably use something like

  ACTIVE/I5  = (somefield EQ 'ACTV'); 
  ON_HOLD/I5 = (somefield EQ 'HOLD');

ACTIVE and ON_HOLD, while "boolean" in their definition at the row level, can serve as counters: The sum (over a set of rows) of the 0-or-1 values of ACTIVE and ON_HOLD yields the count of Active and On-Hold items, respectivly.

OK?

This message has been edited. Last edited by: j.gross,

Jack,

Very elegant!
However, if you want to have totals for ACTIVE and ON_HOLD in the HEADING, you will need to have different field names.

This message has been edited. Last edited by: Danny-SRL,

It's working now. Thanks for your posts!

quote:

However, if you want to have totals for ACTIVE and ON_HOLD in the HEADING, you will need to have different field names.

Dan -

Actually, Heading and Subhead pick up the first like-named column, ignoring the prefix (makes some sense, since they don't allow the prefix in a reference -- whereas Compute and Where Total which allow the prefix pay attention to it)

But in real life I would use a defined clone field -- why venture out on a thin limb.

This message has been edited. Last edited by: j.gross,