Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Need to display total active count in heading and only on hold in detail

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Need to display total active count in heading and only on hold in detail
 Login/Join
 
Member
posted
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,


7.1.7
Vista
 
Posts: 29 | Registered: July 05, 2007Report This Post
Member
posted Hide Post
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,


7.1.7
Vista
 
Posts: 29 | Registered: July 05, 2007Report This Post
Virtuoso
posted Hide Post
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,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
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?


7.1.7
Vista
 
Posts: 29 | Registered: July 05, 2007Report This Post
Virtuoso
posted Hide Post
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 Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
It's working now. Thanks for your posts!


7.1.7
Vista
 
Posts: 29 | Registered: July 05, 2007Report This Post
Virtuoso
posted Hide Post
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,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Need to display total active count in heading and only on hold in detail

Copyright © 1996-2020 Information Builders