Hello,

This is my only my second thread and its also my 2nd related to reports that where working fine with version 7.6.1.1 but are not with version 7.7.0.3. I apologize for the redundancy and this info may possibly be somewhere in the forums but my searches arent finding me a solution.

My issue is a report that uses a drilldown, no longer shows the hyperlink to get to the new report on this line:

withDrillDown/A3=IF reviews GT 0 THEN 'YES' ELSE 'NO';

$
TYPE=DATA,
COLUMN=N2,
FOCEXEC=ntisdqbd(dqbDesc=N2 YEAR=&YEAR.QUOTEDSTRING MONTH=&MONTH.QUOTEDSTRING SAMPLE=&SAMPLE.QUOTEDSTRING APP_URL=&APP_URL.QUOTEDSTRING), TARGET=_blank, WHEN=withDrillDown EQ 'YES',

Is there new command in v7703 for FOCEXEC in a column chart? Do I need to change something for conditional hyperlinking in new version?

I can attach more code if necessary.

Thanks

This message has been edited. Last edited by: <Kathryn Henning>,

Seems to work:

-SET &YEAR = '1993';
-SET &MONTH = '03';
-SET &SAMPLE = 'KOREA FFFDGFDGD DGGFDGFD FDGFDGFDGFD DFGFDGDFGFD DFGFDGFDSFDDSFFDS DSFDSFDSFDSF SDFDSFDSFDSF SDFDSFSDFDS SDFDSFDSFDSFDSF SDFDSFDSFDSF DSTTRTRETRET ERTRETRETRET ERTRETRETGFDGFD';
-SET &APP_URL = 'HTTP://LOCALHOST:8080/APPROOT/BASEAPP/WFDD77.HTML FDSGSDSDFFDS SDFSDFD SDFSDFDSF SDFSD REWTTER TERTRETRET ERTRETRET ERTRETRETFSDF SDFDSFDSFDS'; 
TABLE FILE CAR
PRINT
*
COMPUTE withDrillDown/A3 = IF SEATS EQ 4 THEN 'YES' ELSE 'NO'; NOPRINT

HEADING
"WEBFOCUS REPORT"
WHERE READLIMIT EQ 100
WHERE RECORDLIMIT EQ 100
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON,
FONT='ARIAL', SIZE=9,
BORDER=1, BORDER-COLOR=SILVER,
$
TYPE=DATA, 
COLUMN=N2,
FOCEXEC=NTISDQBD ( DQBDESC=N2 YEAR=&YEAR.QUOTEDSTRING MONTH=&MONTH.QUOTEDSTRING  SAMPLE=&SAMPLE.QUOTEDSTRING  APP_URL=&APP_URL.QUOTEDSTRING ), 
TARGET=_BLANK, WHEN= withDrillDown EQ 'YES', $
ENDSTYLE
END

It's possible some the values cause the line of code to become too long, so you could it this way:

TYPE=DATA, 
COLUMN=N2,
FOCEXEC=NTISDQBD ( \
DQBDESC=N2 \
YEAR=&YEAR.QUOTEDSTRING \
MONTH=&MONTH.QUOTEDSTRING \
SAMPLE=&SAMPLE.QUOTEDSTRING \
APP_URL=&APP_URL.QUOTEDSTRING ), 
TARGET=_BLANK, WHEN= withDrillDown EQ 'YES', $

If

withDrillDown/A3=IF reviews GT 0 THEN 'YES' ELSE 'NO';

is in a DEFINE, you must reference it in the report

Thank you Francis.

I tried the multi-line approach and still no link.

Also the withDrillDown is referenced in the report with a NOPRINT for the final report result as follows

TABLE FILE NATIONAL_SUM
PRINT
dqbDescSum AS ''
dqbDescSum AS ''
reviews AS ''
noIssuesCnt AS ''
missingIssuesCnt AS ''
incorrectIssuesCnt AS ''
incompleteIssuesCnt AS ''
onsetInaccurateIssuesCnt AS ''
addressIssuesCnt AS ''
sourceIssuesCnt AS ''
totalIssuesCnt AS ''
percentAlpha AS ''
COMPUTE DUMMY1/A1 = ''; AS ''
withDrillDown NOPRINT

Please provide the complete code for the report step of the fex.

Please edit the post with your code and put code tags around the code:

[code]

your code here
your code here
your code here

[/code]

This prevents the HTML in the code from rendering in the post and causing display problems.

Sorry about that

-* File noticeissuebydqb_summary.fex
-SET &ECHO=ALL;
-SET &HEAD1 = '<H3>Office of Quality Performance';
-SET &HEAD2 = 'Notice Issues by DQB Processed during ';
-SET &HEAD3 = '</H3>';
-SET &HEAD4 = '</TD><TH COLSPAN=3 CLASS=THEADSUMMARY1> </TH><TH CLASS=THEADSUMMARY2>Required<BR>but Missing</TH><TH CLASS=THEADSUMMARY1>Incorrect<BR>Notice<BR>Prepared</TH><TH CLASS=THEADSUMMARY2>Expl of Decision<BR>Inacc/Incmplt</TH><TH CLASS=THEADSUMMARY1>Expl of Onset<BR>Inacc/Incmplt</TH><TH CLASS=THEADSUMMARY2>Did not<BR>Address All<BR>Allgn</TH><TH CLASS=THEADSUMMARY1>Did not List<BR>All Sources</TH><TH COLSPAN=2 CLASS=THEADSUMMARY1> </TH><TD>';
-SET &HEAD5 = '</TD><TH CLASS=THEAD>DQB</TH><TH CLASS=THEAD>Reviews</TH><TH CLASS=THEAD>No<BR>Issues</TH><TH CLASS=THEAD COLSPAN=6>Issues</TH><TH CLASS=THEAD>Total<BR>Cases<BR>With<BR>Issues</TH><TH CLASS=THEAD>%</TH><TD>';
-SET &EMPTYHEAD = '</TD><TD WIDTH=800></TD>';
-SET &MONTHT=DECODE &MONTH (1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December );
-SET &SAMPLET=DECODE &SAMPLE (100 'QA Samples' 200 'Per-Title II Samples' 300 'PER-Title XVI Samples' 400 'Targeted Denial Review Samples' pertot 'Per-Title II and Per-Title XVI Samples' iro 'Random IR Samples' all 'All Samples');


-INCLUDE URLICLD

SET ALL = ON
SET NODATA = .
SET EMPTYREPORT = ON
SET ASNAMES = ON
SET HOLDATTR = ON

-* Join RSLTS with CASE to get DDS_ST_CD and ASGND_USER_UID fields
JOIN
  RSLTS.RSLTS.DQR_CASE_UID IN RSLTS TO UNIQUE CASE.CASE.DQR_CASE_UID IN CASE
  AS J0
END

-* Get targeted fields from joined RSLTS file and create hold file NEWRSLTS
TABLE FILE RSLTS
PRINT
  RSLTS.RSLTS.DQR_CASE_UID
  CASE.CASE.DDS_ST_CD
  RSLTS.RSLTS.NTC_ISUS_CD
  RSLTS.RSLTS.LAST_CLRD_TS
  CASE.CASE.ASGND_USER_UID
  CASE.CASE.QASMP_TYP
ON TABLE HOLD AS NEWRSLTS
END

-* union CASE and NEWRSLTS to create NEWCASE
TABLE FILE CASE
PRINT
  DQR_CASE_UID
  DDS_ST_CD
  NTC_ISUS_CD
  LAST_CLRD_TS
  ASGND_USER_UID
  QASMP_TYP
ON TABLE HOLD AS NEWCASE
MORE
FILE NEWRSLTS
END

-* Try conditional join and failed; returned multiple rows incorrectly
-*JOIN FILE NEWCASE AT DQR_CASE_UID TO MULTIPLE FILE CAOTNTIS AT DQR_CASE_UID AS J0
-*WHERE ( YEAR EQ &YEAR.(<2010,2010>,<2011,2011>,<2012,2012>,<2013,2013>,<2014,2014>).Year. ) AND ( MONTH EQ &MONTH.(<January,1>,<February,2>,<March,3>,<April,4>,<May,5>,<June,6>,<July,7>,<August,8>,<September,9>,<October,10>,<November,11>,<December,12>).MONTH. );
-*END

-* join NEWCASE with DQRUSERS, DQBCD to get the sum of distinct DQR_CASE_UID
-* (count of Reviews);
-* sum of distinct NTC_ISUS_CD with value 'N' (count of No Issues);
-* sum of distinct NTC_ISUS_CD with value 'P' (count of Missing Issues); and
-* sum of distinct NTC_ISUS_CD with value 'O' or 'P' (count of Case with Issues)
JOIN ASGND_USER_UID IN NEWCASE TO UNIQUE USER_UID IN DQRUSERS AS J2
END

JOIN OCD IN NEWCASE TO UNIQUE DQB IN DQBCD AS J3
END


DEFINE FILE NEWCASE

-SET &WHERE1 = IF &SAMPLE EQ '100'  THEN 'QASMP_TYP EQ ''100'' '
-ELSE IF &SAMPLE EQ '200' THEN 'QASMP_TYP EQ ''200'' '
-ELSE IF &SAMPLE EQ '300' THEN 'QASMP_TYP EQ ''300'' '
-ELSE IF &SAMPLE EQ '400' THEN 'QASMP_TYP EQ ''400'' '
-ELSE IF &SAMPLE EQ 'pertot' THEN 'QASMP_TYP IN (''200'',''300'') '
-ELSE IF &SAMPLE EQ 'iro' THEN 'QASMP_TYP EQ ''IRO'' '
-ELSE IF &SAMPLE EQ 'all' THEN 'QASMP_TYP IN (''100'',''200'',''300'',''400'',''IRO'')'
-ELSE '';

noIssuesCnt/I4=IF NTC_ISUS_CD EQ 'N' THEN 1 ELSE 0;
missingIssuesCnt/I4=IF NTC_ISUS_CD EQ 'P' THEN 1 ELSE 0;
totalIssuesCnt/I4=IF NTC_ISUS_CD EQ 'P' OR NTC_ISUS_CD EQ 'O' THEN 1 ELSE 0;
YEAR/I4=HPART(LAST_CLRD_TS, 'YEAR', 'I4');
MONTH/I2=HPART(LAST_CLRD_TS,'MONTH', 'I2');
dqbDescSum/A20='National';
END

-* create case level national summary hold file
TABLE FILE NEWCASE
SUM
     COMPUTE reviews/I5 = CNT.DQR_CASE_UID;
     noIssuesCnt
     missingIssuesCnt
     totalIssuesCnt
     COMPUTE percentage/D6.1 = totalIssuesCnt * 100 / reviews;
BY dqbDescSum
WHERE ( YEAR EQ &YEAR.(<2010,2010>,<2011,2011>,<2012,2012>,<2013,2013>,<2014,2014>).Year. ) AND ( MONTH EQ &MONTH.(<January,1>,<February,2>,<March,3>,<April,4>,<May,5>,<June,6>,<July,7>,<August,8>,<September,9>,<October,10>,<November,11>,<December,12>).MONTH. ) AND (&WHERE1);
ON TABLE HOLD AS NATIONAL_SUM_CASE
END

-* create case level summary by dqb hold file
TABLE FILE NEWCASE
SUM
     COMPUTE reviews/I5 = CNT.DQR_CASE_UID;
     noIssuesCnt
     missingIssuesCnt
     totalIssuesCnt
     COMPUTE percentage/D6.1 = totalIssuesCnt * 100 / reviews;
BY DQB_NM
WHERE ( YEAR EQ &YEAR.(<2010,2010>,<2011,2011>,<2012,2012>,<2013,2013>,<2014,2014>).Year. ) AND ( MONTH EQ &MONTH.(<January,1>,<February,2>,<March,3>,<April,4>,<May,5>,<June,6>,<July,7>,<August,8>,<September,9>,<October,10>,<November,11>,<December,12>).MONTH. ) AND (&WHERE1);
ON TABLE HOLD AS EXIST_DQB_CASE
END

-*TABLE FILE CAOTNTIS
-*PRINT *
-*END

-* join newCASE with CAOTNTIS to get the counts in CAOTNTIS (notices) table:
-* sum of OTH_NTC_ISUS_CD with value 'I' (Incorrect Issues);
-* sum of OTH_NTC_ISUS_CD with value 'D' (Decision Inacc/Incmplt);
-* sum of OTH_NTC_ISUS_CD with value 'A' (Onset Inacc/Incmplt);
-* sum of OTH_NTC_ISUS_CD with value 'O' (Not Address Align);
-* sum of OTH_NTC_ISUS_CD with value 'S' (Not List sources);
JOIN DQR_CASE_UID IN NEWCASE TO MULTIPLE DQR_CASE_UID IN CAOTNTIS AS J4
-* JOIN DQR_CASE_UID IN NEWCASE TO UNIQUE DQR_CASE_UID IN CAOTNTIS AS J4
END

DEFINE FILE NEWCASE

incorrectIssuesCnt/I4=IF OTH_NTC_ISUS_CD EQ 'I' THEN 1 ELSE 0;
incompleteIssuesCnt/I4=IF OTH_NTC_ISUS_CD EQ 'D' THEN 1 ELSE 0;
onsetInaccurateIssuesCnt/I4=IF OTH_NTC_ISUS_CD EQ 'A' THEN 1 ELSE 0;
addressIssuesCnt/I4=IF OTH_NTC_ISUS_CD EQ 'O' THEN 1 ELSE 0;
sourceIssuesCnt/I4=IF OTH_NTC_ISUS_CD EQ 'S' THEN 1 ELSE 0;
YEAR/I4=HPART(LAST_CLRD_TS, 'YEAR', 'I4');
MONTH/I2=HPART(LAST_CLRD_TS,'MONTH', 'I2');
dqbDescSum/A20='National';
END

-* create notice level national summary
TABLE FILE NEWCASE
SUM
     incorrectIssuesCnt
     incompleteIssuesCnt
     onsetInaccurateIssuesCnt
     addressIssuesCnt
     sourceIssuesCnt
BY dqbDescSum
WHERE ( YEAR EQ &YEAR.(<2010,2010>,<2011,2011>,<2012,2012>,<2013,2013>,<2014,2014>).Year. ) AND ( MONTH EQ &MONTH.(<January,1>,<February,2>,<March,3>,<April,4>,<May,5>,<June,6>,<July,7>,<August,8>,<September,9>,<October,10>,<November,11>,<December,12>).MONTH. )AND (&WHERE1);
ON TABLE HOLD AS NATIONAL_SUM_NOTICE
END

-* join case and notice level national summary together and create hold file NATIONAL_SUM
JOIN dqbDescSum IN NATIONAL_SUM_CASE TO UNIQUE dqbDescSum IN NATIONAL_SUM_NOTICE AS J5

TABLE FILE NATIONAL_SUM_CASE
PRINT
     dqbDescSum
     reviews
     noIssuesCnt
     missingIssuesCnt
     incorrectIssuesCnt
     incompleteIssuesCnt
     onsetInaccurateIssuesCnt
     addressIssuesCnt
     sourceIssuesCnt
     totalIssuesCnt
     percentage
ON TABLE HOLD AS NATIONAL_SUM
END

-* create notice level summary by dqb
TABLE FILE NEWCASE
SUM
     incorrectIssuesCnt
     incompleteIssuesCnt
     onsetInaccurateIssuesCnt
     addressIssuesCnt
     sourceIssuesCnt
BY DQB_NM
WHERE ( YEAR EQ &YEAR.(<2010,2010>,<2011,2011>,<2012,2012>,<2013,2013>,<2014,2014>).Year. ) AND ( MONTH EQ &MONTH.(<January,1>,<February,2>,<March,3>,<April,4>,<May,5>,<June,6>,<July,7>,<August,8>,<September,9>,<October,10>,<November,11>,<December,12>).MONTH. ) AND (&WHERE1);

ON TABLE HOLD AS EXIST_DQB_NOTICE
END

-* join case and notice level summary by DQB together and create hold file EXIST_DQB
JOIN DQB_NM IN EXIST_DQB_CASE TO UNIQUE DQB_NM IN EXIST_DQB_NOTICE AS J6

TABLE FILE EXIST_DQB_CASE
PRINT
     DQB_NM
     reviews
     noIssuesCnt
     missingIssuesCnt
     incorrectIssuesCnt
     incompleteIssuesCnt
     onsetInaccurateIssuesCnt
     addressIssuesCnt
     sourceIssuesCnt
     totalIssuesCnt
     percentage
ON TABLE HOLD AS EXIST_DQB
END
-RUN
-SET &TOTAL=&RECORDS;

-* join with DQB table to get all DQB's
MATCH FILE DQBCD
PRINT DQB
      DQB_NUM
BY DQB_NM
WHERE DQB NE 'R51'
RUN

FILE EXIST_DQB
SUM
     reviews
     noIssuesCnt
     missingIssuesCnt
     incorrectIssuesCnt
     incompleteIssuesCnt
     onsetInaccurateIssuesCnt
     addressIssuesCnt
     sourceIssuesCnt
     totalIssuesCnt
     percentage
BY DQB_NM
AFTER MATCH HOLD AS ALL_DQB OLD-OR-NEW
END

-* define withDrillDown flag
DEFINE FILE ALL_DQB
     withDrillDown/A3=IF reviews GT 0 THEN 'YES' ELSE 'NO';
     percentAlpha/A8 =  IF percentage = 0 THEN '0' ELSE FTOA(percentage, '(D6.1)', percentAlpha);
END

-* sort ALL_DQB by DQB_NUM to get correct order of DQB's and save to hold file SORTED_ALL_DQB
TABLE FILE ALL_DQB
PRINT
     DQB_NM
     reviews
     noIssuesCnt
     missingIssuesCnt
     incorrectIssuesCnt
     incompleteIssuesCnt
     onsetInaccurateIssuesCnt
     addressIssuesCnt
     sourceIssuesCnt
     totalIssuesCnt
     percentAlpha
     withDrillDown
BY DQB_NUM NOPRINT
ON TABLE HOLD AS SORTED_ALL_DQB
END

-* define default withDrillDown flag for national summary as NO
DEFINE FILE NATIONAL_SUM
     withDrillDown/A3='NO';
     percentAlpha/A8 =  IF percentage = 0 THEN '0' ELSE FTOA(percentage, '(D6.1)', percentAlpha);
END

-* union NATIONAL_SUM and SORTED_ALL_DQB to get final result
TABLE FILE NATIONAL_SUM
PRINT
     dqbDescSum AS ''
     dqbDescSum AS ''
     reviews AS ''
     noIssuesCnt AS ''
     missingIssuesCnt AS ''
     incorrectIssuesCnt AS ''
     incompleteIssuesCnt AS ''
     onsetInaccurateIssuesCnt AS ''
     addressIssuesCnt AS ''
     sourceIssuesCnt AS ''
     totalIssuesCnt AS ''
     percentAlpha AS ''
     COMPUTE DUMMY1/A1 = ''; AS ''
     withDrillDown NOPRINT
     COMPUTE CLASSFLAG/A1 = IF CLASSFLAG EQ '2' THEN '1' ELSE '2'; NOPRINT
HEADING
"!IBI.AMP.HEAD1;"
"!IBI.AMP.HEAD2; &MONTHT &YEAR !IBI.AMP.HEAD3; For &SAMPLET "
""
-IF &TOTAL GT 0 GOTO WITH_DATA;
"No records to display."
"!IBI.AMP.EMPTYHEAD;"
-GOTO HEAD_DONE
-WITH_DATA
"!IBI.AMP.HEAD4;"
"!IBI.AMP.HEAD5;"
-HEAD_DONE
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTMTABLE AS H1
ON TABLE SET HTMLCSS ON
ON TABLE SET CSSURL &CSS_URL
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     SUMMARY='This table shows Notice Issues by DQB Processed during &MONTHT &YEAR',
$
TYPE=REPORT,
     BACKCOLOR=WHITE,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=DATA,
     column=N1,
     CLASS=INVISIBLE,
$
TYPE=DATA,
     column=N13,
     CLASS=INVISIBLE,
$
TYPE=DATA,
     COLUMN=N2,
     SQUEEZE=OFF,
$
TYPE=DATA,
    CLASS=TR, JUSTIFY=CENTER, WHEN=CLASSFLAG EQ '2',
$
TYPE=DATA,
    CLASS=TRALT, JUSTIFY=CENTER, WHEN=CLASSFLAG EQ '1',
$
TYPE=DATA,
    COLUMN=N2,
    CLASS=MAINTD, WHEN=CLASSFLAG EQ '2',
$
TYPE=DATA,
    COLUMN=N2,
    CLASS=MAINTDALT, WHEN=CLASSFLAG EQ '1',
$
TYPE=DATA,
     COLUMN=N2,
     FOCEXEC=ntisdqbd(dqbDesc=N2 YEAR=&YEAR.QUOTEDSTRING MONTH=&MONTH.QUOTEDSTRING SAMPLE=&SAMPLE.QUOTEDSTRING DQR_URL=&DQR_URL.QUOTEDSTRING), TARGET=_blank, WHEN=withDrillDown EQ 'YES',
$
TYPE=HEADING,
     HEADALIGN = BODY,
$
TYPE=HEADING,
     LINE=1,
     JUSTIFY=CENTER,
     SIZE=12,
     STYLE=BOLD,
     COLSPAN=18,
$
TYPE=HEADING,
     LINE=2,
     SIZE=12,
     STYLE=BOLD,
     JUSTIFY=CENTER,
     COLSPAN=18,
$
-IF &TOTAL EQ 0 GOTO NO_DATA;
ENDSTYLE
MORE
FILE SORTED_ALL_DQB
-GOTO THE_END
-NO_DATA
TYPE=HEADING,
     LINE=4,
     SIZE=11,
     STYLE=BOLD,
     JUSTIFY=CENTER,
     COLSPAN=18,
$
ENDSTYLE
-THE_END
END

-HTMLFORM BEGIN
<HTML>
<BODY>
<DIV align="center">
  !IBI.FIL.H1;
</DIV>
</BODY>
</HTML>
-HTMLFORM END

You have withDrillDown defined twice, once with an IF statement and once with a direct set to NO. You then use MORE to "union" two hold files. Have you tried removing the NOPRINT on withDrillDown so you can see the values?

I removed the NOPRINT and the withDrillDown values are correct (Anywhere there are reviews/data in the count it would display yes). Here is a screenshot:



The second withDrillDown definition is to set the drilldown to NO automatically in the national sum. When I remove either of the 2 withDrillDown definitions, that produces an error (FOC003) saying THE FIELDNAME IS NOT RECOGNIZED: withDrillDown
BYPASSING TO END OF COMMAND. The original developer who coded this must have had a reason for the multiple definitions, but unfortanetly Im unable to get in touch with them to ask why. Reverting back to 7.6.11 it does work with the multiple withDrillDown definitions.



just cannot figure out why the FOCEXEC link will not activate if the withDrillDown clause of being YES is true

Yes, I cannot see anything wrong with the code. I never came across this issue when in v7703. I would open a case.

I suspect that you have several columns withDrillDown in your table matrix as a result of re-defining it in your final table request. You could verify whether that's the case by writing your report output to a hold file and inspecting the resulting master with ?FF

In that case, you can reference the one you want in your stylesheet by using "withDrillDown (n)" for the nth version of that field.

Open a case anyway if you didn't already, as this may reeks of a bug. Those fields should be at the same level for both tables.