Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem getting multi drill downs working
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Problem getting multi drill downs working
 Login/Join
 
Member
posted
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
-*******************************************************************************************************************************************

-*************************************************************************************************************************
-* FILENAME= APSASB
-* ID E01 P20
-* POID E02 A20
-* MIGRATED_ID E03 A60
-* TITLE E04 A800V
-* OFFICER_NAME E05 A35
-* DIVISION E06 A40
-* AWARDDT E07 YYMD
-* AWARD_VALUE E08 D20.2M
-* MTHEXPENDITURE E09 D20.2M
-* YTDEXPENDITURE E10 D20.2M
-* TOTEXPENDITURE E11 D20.2M
-* NEXTPOPDATE E12 YYMD
-* FINALEXPDTE E13 YYMD
-* OVERSIGHT_MANAGER E14 A140V
-* VENDOR_CODE E15 A40V
-* VENDOR_NAME E16 A300V
-* STATUS E17 A20
-* STATUSPARM E18 A6
-* NFE_BUSINESSUNIT E19 A5
-* FAILED_INSTITUTION E20 P20
-* SE_STATUS E21 A3V
-* MWOB E22 A4
-* WO E23 A2V
-* MO E24 A2V
-* VO E25 A2V
-* SDB E26 A3V
-* SDVOSB E27 A6V
-* SAAO E28 A4V
-* APAO E29 A4V
-* BAO E30 A3V
-* NAO E31 A3V
-* HAO E32 A3V
-* OTHER E33 A5V
-* SOURCE_GOODS E34 A3V
-* BV_METHOD E35 A3V
-* PLACE_PERFORMANCE E36 A800V
-* RISK_LEVEL E37 A800V
-* NAICS E38 A6
-* NAICSDESC E39 A200
-* LOCATION E40 A5
-* FISCALYR E41 A4
-* YEARLYID E42 P20
-* ACTIONTYPE E43 A1
-* TASK_ORDER_COUNT E44 P20
-* PROCESS_ID E45 P20
-* SOURCE E46 A8
-* CATCODE E47 P20
-* CATDESC E48 A800V

-***************************************************************************************************************

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';

-*CATEGDRC/A400=CATDESC;
-*T_CATEGORY/A400 = EDIT(CATEGDRC, '9999$$$$$');


END
TABLE FILE APSASB
PRINT

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'

WHERE AWARDDT LE &LST_MONTH

WHERE STATUSPARM EQ 'Active'

WHERE DIVISION EQ 'DRR'
-*WHERE &STATUS

-*IF &PERIOD EQ 'All' GOTO ALLCURMONTH;
-*-ALLCURMONTH

-*-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, $

TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
LEFTGAP=.03778,
RIGHTGAP=.03778,
$
TYPE=TABHEADING,
SIZE=9,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=9,
STYLE=BOLD,
$
TYPE=TITLE,
COLOR=WHITE,
-IF &OUTPUT EQ 'HTML' GOTO HTDOC ;
BACKCOLOR=RGB(0 68 136),
-GOTO TTLSTYL
-HTDOC
BACKCOLOR=RGB(26 50 89),
-TTLSTYL
STYLE=+BOLD-UNDERLINE,
JUSTIFY=CENTER,
$
-IF &OUTPUT EQ 'EXL2K' GOTO NODRILL ;

TYPE=DATA,
BACKCOLOR=( RGB(238 238 238) 'WHITE' ),
$
-IF &OUTPUT EQ 'PDF' GOTO NODRILL ;
TYPE=TABHEADING,
IMAGE=app/excel.jpg,
IMAGEALIGN=LEFT,
DRILLMENUITEM='Output to Excel',
FOCEXEC=app/awards_aps(OUTPUT='EXL2K'),
TARGET='_blank',
$
TYPE=TABHEADING,
IMAGE=app/pdf.jpg,
IMAGEALIGN=LEFT,
DRILLMENUITEM='Output to PDF',
FOCEXEC=app/awards_aps(OUTPUT='PDF'),
TARGET='_blank',
$

TYPE=TABHEADING,
LINE=2,
ITEM=2,
DRILLMENUITEM='Active',
FOCEXEC=app/awards_aps(RLEVEL='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
DRILLMENUITEM='Closed',
FOCEXEC=app/awards_aps (RLEVEL='&RLEVEL' ORDER='&ORDER' STATUS='Closed'),
TARGET='_self',
$
TYPE=TABHEADING,
LINE=3,
ITEM=2,
DRILLMENUITEM='High',
FOCEXEC=app/awards_aps (PERIOD='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
DRILLMENUITEM='Moderate',
FOCEXEC=app/awards_aps (PERIOD='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
DRILLMENUITEM='Low',
FOCEXEC=app/awards_aps (PERIOD='&RLEVEL' ORDER='&ORDER' STATUS='Active'),
TARGET='_self',
$
-NODRILL
TYPE=GRANDTOTAL,
STYLE=BOLD,
BACKCOLOR=SILVER,
$
-GOTO ENDSTYLING
-PDFSTYLE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.1700,
RIGHTMARGIN=0.000,
TOPMARGIN=0.0125,
BOTTOMMARGIN=0.0125,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT, COLUMN=APSTITLE, WRAP=4, $
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=8,
COLOR='BLACK',
LEFTGAP=.0377,
RIGHTGAP=.0366,
$
TYPE=TABHEADING,
SIZE=9,
STYLE=BOLD,
JUSTIFY=LEFT,
$
TYPE=HEADING,
SIZE=9,
-* JUSTIFY=CENTER,
STYLE=BOLD,
$
TYPE=TITLE,
BORDER=LIGHT,
BORDER-COLOR=RGB(0 68 136),
COLOR='WHITE',
BACKCOLOR=RGB(0 68 136),
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=DATA,
BACKCOLOR=( RGB(238 238 238) 'WHITE' ),
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
BACKCOLOR=SILVER,
$
-ENDSTYLING
ENDSTYLE
END

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


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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


8007 FR, Windows 2008 R2 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Puteaux | Registered: August 26, 2010Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Patlechat



Thanks a million for your help Patlechat!!!! Wink 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
 
Posts: 20 | Registered: January 22, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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]


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem getting multi drill downs working

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.