[SOLVED] Problem getting multi drill downs working
Hello, I am using WF version 7.7.03, on a Oracle platform.
I have created a report that will probably be run monthly or on demand for the month that has multi drill options. I can get my excel and pdf output to work. My main issue is getting my html drills to work properly. I have separate drills for html reports. One hyperlink is called Active which is set as DEFAULTH and the link underneath is called Closed (this is not working)
My next set of hyperlinks start with All (DEFAULTH), followed by "High", "Moderate" and "Low" (none are currently working properly). I have been trying different methods and I could really use some help! This is my first time working with a report like this one. I have pasted my code below. Thanks in advance for taking a look at my code!!! :-)
DEFAULTH &STATUS = 'Active' -DEFAULTH &RLEVEL = 'All' -DEFAULTH &OUTPUT = 'HTML' -DEFAULTH &ORDER = 'High' SET BYDISPLAY = ON SET SHOWBLANKS = ON SET HDAY = CURR SET BUSDAYS = _MTWTF_
-SET &LST_MONTH = DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'M', -1)),'YYMD','I8YYMD'); -SET &LST_DOM = DATECVT(DATEMOV(DATECVT(&LST_MONTH,'I8YYMD', 'YYMD'),'EOM'), 'YYMD', 'I8YYMD') ; -SET &BEG_MONTH = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD', 'YYMD'),'BOM'), 'YYMD', 'I8YYMD') ; -SET &BEG_YEAR = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD', 'YYMD'),'BOY'), 'YYMD', 'I8YYMD') ; -SET &DISP_BEGYR = EDIT(&BEG_YEAR,'$$$$99/99/') || EDIT(&BEG_YEAR,'9999') ; -SET &DISP_BEGMO = EDIT(&BEG_MONTH,'$$$$99/99/')|| EDIT(&BEG_MONTH,'9999') ; -SET &DISP_LDOM = EDIT(&LST_DOM,'$$$$99/99/') || EDIT(&LST_DOM,'9999') ; -SET &YEAR = EDIT(&LST_MONTH,'9999') ; -SET &BOYEAR = EDIT(&BEG_YEAR,'9999'); -SET &LST_YEAR = &BOYEAR - 1 ; -SET &LMONTH = EDIT(&LST_MONTH,'$$$$99') ; -SET &PERIOD = IF &LMONTH LT 10 THEN EDIT(&LMONTH,'$9') ELSE &LMONTH ; -SET &MONTH = DECODE &LMONTH(01 January 02 February 03 March 04 April 05 May 06 June 07 July 08 August - 09 September 10 October 11 November 12 December) ; -SET &WHERESTATUS = IF &STATUS EQ 'Active' THEN 'WHERE STATUS EQ ''Active''' - ELSE IF &STATUS EQ 'Closed' THEN 'WHERE STATUS EQ ''Closed''' ; - -SET &THITYDYSAGO = DATECVT((DATEADD((DATECVT(&YYMD,I8YYMD, YYMD)), D, -30)) , YYMD, I8YYMD) ; -SET &1YEARAGO = DATECVT((DATEADD((DATECVT(&YYMD,I8YYMD, YYMD)), D, -365)), YYMD, I8YYMD) ; -SET &2YEARAGO = DATECVT((DATEADD((DATECVT(&YYMD,I8YYMD, YYMD)), D, -730)), YYMD, I8YYMD) ; -*-IF &RLEVEL EQ 'HIGHEST' THEN RISKLEVEL2 EQ 'HIGHEST' -* ELSE IF RISKLEVEL2 EQ 'MODERATE' THEN RISKLEVEL EQ 'MODERATE' -* ELSE IF RISKLEVEL2 EQ 'LOW' THEN RISKLEVEL2 EQ 'LOW' -* ELSE IF RISKLEVEL2 EQ 'N/A (Only Goods)' RIS.KLEVEL2 EQ 'N/A (Only Goods)' -* ELSE IF RISKLEVEL2 EQ '' THEN '' ELSE ''; -*-SET &ALL = DATECVT((DATEADD((DATECVT(&YYMD,I8YYMD, YYMD)), M, -144)), YYMD, I8YYMD) ; -*-SET &TIME = IF &PERIOD EQ 'Year to Date' THEN &BEGYEAR -*- ELSE IF &PERIOD EQ '1 Year' THEN &1YEARAGO -*- ELSE IF &PERIOD EQ '2 Year' THEN &2YEARAGO ; -*-TYPE &WHERESTATUS -*-EXIT -*******************************************************************************************************************************************
DEFINE FILE APSASB ACNT/I6C = IF ID NE LAST ID THEN 1 ELSE 0 ; APSTITLE/A800 = TITLE ; MOD_POID/A20 = EDIT(POID, '99999$99$9$9999$$999');
RISKLEVEL2/A8 = IF EDIT(RISK_LEVEL, '9999') EQ 'High' THEN 'High' ELSE IF EDIT(RISK_LEVEL, '999') EQ 'Low' THEN 'Low' ELSE IF EDIT(RISK_LEVEL, '99999999') EQ 'Moderate' THEN 'Moderate' ELSE 'X';
APSTITLE AS 'TITLE' OFFICER_NAME AS 'OFFICER,NAME' AWARDDT AS 'AWARD,DATE' AWARD_VALUE AS 'AWARD VALUE' YTDEXPENDITURE AS 'YTD,EXPENDITURE' TOTEXPENDITURE AS 'TOTAL,EXPENDITURE' FINALEXPDTE AS 'FINAL EXP,DATE' OVERSIGHT_MANAGER AS 'OVERSIGHT,MANAGER' VENDOR_NAME AS 'VENDOR NAME' STATUS STATUSPARM AS 'AWARD,STATUS' NFE_BUSINESSUNIT AS 'NFE,BUSINESS UNIT' MWOB WO MO VO SDB SDVOSB SAAO APAO BAO NAO HAO OTHER RISK_LEVEL AS 'RISK LEVEL' NAICSDESC AS 'NAIC, DESCR' TASK_ORDER_COUNT AS 'TASK,ORDER,COUNT' CATDESC AS 'CATAGORY,DESCR' -* T_CATEGORY AS 'CATAGORY,DESCR' -*BY POID BY MOD_POID AS 'POID' -*&WHERESTATUS -*IF &STATUS EQ 'Active' GOTO MONTHACTIVE1; -*IF &PERIOD EQ 'All' GOTO ALLCURMONTH; -*-MONTHACTIVE1 -*WHERE AWARDDT LE &LST_MONTH AND STATUSPARM EQ 'Active'
-*-RUN HEADING " Award Risk Rating" " Total Active Awards - " " ON TABLE SUBHEAD " " "Award Status: <+3 &STATUS" "Award Rating: <+3 &RLEVEL" " " " "
FOOTING BOTTOM " " "Prepared by: " "Reporting" "Program : awards_aps" "RUN DATE: &DATE &TOD" -IF &OUTPUT NE 'HTML' GOTO SKIPLINES ; ON TABLE SET LINES 999999 -SKIPLINES ON TABLE COLUMN-TOTAL AS 'TOTAL' AWARD_VALUE MTHEXPENDITURE YTDEXPENDITURE TOTEXPENDITURE
ON TABLE SET PAGE-NUM TOP -*ON TABLE SET HTMLCSS ON ON TABLE PCHOLD FORMAT &OUTPUT -IF &OUTPUT EQ 'PDF' GOTO PDFSTYLE ; ON TABLE SET STYLE * UNITS=IN, LEFTMARGIN=0.0, TOPMARGIN=.0125, SQUEEZE=ON, ORIENTATION=LANDSCAPE, $ TYPE=REPORT, COLUMN=APSTITLE, WRAP=4, $
hello, here is a repro based on car database but it works fine.
-SET &ECHO=ALL;
-RUN
-SET &OUTPUT='HTML';
-DEFAULTH &STATUS = 'Active' ;
-DEFAULTH &RLEVEL = 'All' ;
-DEFAULTH &ORDER = 'High' ;
TABLE FILE CAR
HEADING
" Award Risk Rating"
" Total Active Awards - " "
ON TABLE SUBHEAD
" "
"Award Status: <+3 aa"
"Award Rating: <+3 bb"
" "
" "
SUM SALES
BY COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT &OUTPUT
ON TABLE SET STYLE *
-IF &OUTPUT EQ 'EXL2K' GOTO NODRILL ;
TYPE=DATA,
BACKCOLOR=( RGB(238 238 238) 'WHITE' ),
$
-IF &OUTPUT EQ 'PDF' GOTO NODRILL ;
TYPE=TABHEADING,
IMAGE=excel.jpg,
-*IMAGEALIGN=LEFT,
POSITION=(+0.000000 +0.000000),
DRILLMENUITEM='Output to Excel',
FOCEXEC=carinst(OUTPUT='EXL2K'),
TARGET='_blank',
$
TYPE=TABHEADING,
IMAGE=pdf.jpg,
-*IMAGEALIGN=LEFT,
POSITION=(+0.300000 +0.000000),
DRILLMENUITEM='Output to PDF',
FOCEXEC=carinst(OUTPUT='PDF'),
TARGET='_blank',
$
TYPE=TABHEADING,
LINE=2,
ITEM=2,
DRILLMENUITEM='Active',
FOCEXEC=test(RLEVEL='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
DRILLMENUITEM='Closed',
FOCEXEC=test(RLEVEL='&RLEVEL' ORDER='&ORDER' STATUS='Closed'),
TARGET='_self',
$
TYPE=TABHEADING,
LINE=3,
ITEM=2,
DRILLMENUITEM='High',
FOCEXEC=test (PERIOD='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
DRILLMENUITEM='Moderate',
FOCEXEC=test (PERIOD='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
DRILLMENUITEM='Low',
FOCEXEC=test (PERIOD='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
$
-NODRILL
ENDSTYLE
END
I call test.fex as a drilldown link for Award Status and Award Rating. here is the code :
-DEFAULTH &PERIOD = '';
-DEFAULTH &RLEVEL='' ;
TABLE FILE CAR
HEADING
"RLEVEL : &RLEVEL"
"ORDER : &ORDER"
"STATUS : &STATUS"
"PERIOD : &PERIOD"
SUM SALES
END
I add POSITION= to your PDF and EXCEL Icon and I comment IMAGEALIGN.
With EXL2K output format, nothing to drill. With PDF output format, nothing to drill
WITH HTML, on each image, you can drill and see the menu when you clic on Award Status, you can see the Active or Close value When you clic on Award rating, you a a 3 drill menu items, on each you'll always have the same values as you initialize you variables with constant.
Try this
Rgds
8207, Windows 2016 64b, HTML, AHTML, PDF, EXL07...
August 08, 2014, 12:53 PM
JRS
quote:
Patlechat
Thanks a million for your help Patlechat!!!! I will try this approach now and see if I can incorporate the code into my report to get it working the way I want it too. I'll let you know if I'm successful.
7.7.03
OS/Oracle
Windows Output:All
August 13, 2014, 03:54 PM
JRS
Hello Patlechat!
Again, I appreciate your quick response and help with my issue. I'm still having some issues with my report but I'm going to close out this request as [SOLVED]
I will try this approach now and see if I can incorporate the code into my report to get it working the way I want it too. I'll let you know if I'm successful.