TABLE FILE CAR
PRINT *
WHERE SEATS EQ '&SEATS.(, ,,).SEATS.';
END

WHEN THE ABOUVE REPORT IS RUN, THE AUTO PROMPT FACILITY POPS UP A DROP DOWN LIST WITH ALL AS DEFAULT SELECTION CRITERIA... AND RUN OPTION UNDER IT..

I WOULD LIKE TO RUN THE REPORT DIRECTLY FOR DEFAULT ALL FOR &SEATS... AND THEN THE USER SELECTS THE FILTER FOR NUMBER OF SEATS AND RE RUN THE REPORT.

TECHNICALLY RUNNING THE REPORT TWICE IS ILLOGICAL... BUT THE BUSINESS REQUIREMENT I HAVE.. NEEDS THIS FUNCTIONALITY.

This message has been edited. Last edited by: Kerry,

7.7.02, sql server
Windows
all output (Excel, HTML, PDF)

Prasad, I don't think you will be able to accomplish that with Auto-Prompt.

You most likely will need to use the HTML Composer (or Layout Painter in previous versions) to add the drop-down list so the user can select what he or she needs and (re)run the report.

Please take a look at Designing a User Interface for a Web Application With the HTML Composer 7.6.9 & Higher (DN 4500976.1209) document for extensive explanation and examples of how to accomplish exactly what you want. You will find that document in the Technical Library section of Information Builder's Support website.

- Neftali.

If it's HTML output use the multi drill method, if the drilldowns are dynamic generate them as an include
-DEFAULTH supresses the auto prompt

-DEFAULTH &SEATS='GT 0'
TABLE FILE CAR
SUM
COMPUTE PSEATS/P3=FST.SEATS; NOPRINT
COMPUTE DITEM/A100='DRILLMENUITEM=''' ||PTOA(PSEATS, '(P3)', 'A3') || ' Seats'',FOCEXEC=fexname(SEATS=''EQ ' |PTOA(PSEATS, '(P3)', 'A3')|'''),';
BY SEATS NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS DITEMS
END
-RUN
TABLE FILE CAR
PRINT COUNTRY CAR MODEL SEATS
WHERE SEATS &SEATS
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=DATA,
COLUMN=N1,
COLOR='BLUE',
STYLE=UNDERLINE,
-INCLUDE DITEMS
$
END

This expands on JG's post above. I use FOCCACHE so the drill-down menu and data extract steps need be run only once. This allows the focexec (TEST2.fex in this case) to call itself for each new request. Set "Prompt for Parameters" off in the Properties for the focexec.

-SET &RNDM = EDIT(HHMMSS('A8'),'99$99$99');
-DEFAULT &SEATS = FOC_NONE
-DEFAULT &GOTO = '-*';
-*
-* For subsequent executions, skip creation of the drill-down menu and data hold file.
&GOTO.EVAL
-*
-* Create drill-down menu.
TABLE FILE CAR
 SUM
 COMPUTE PSEATS/F3  = FST.SEATS; NOPRINT
 COMPUTE DITEM/A100 = 'DRILLMENUITEM=''' || FTOA(PSEATS,'(F3)','A3') ||
                      ' Seats'',FOCEXEC=TEST2(GOTO=''-GOTO HOLD1'' RNDM=&RNDM SEATS=' | FTOA(PSEATS,'(F3)','A3') | '),';
 BY SEATS NOPRINT
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE AS FOCCACHE/DITEMS
END
-*
-* Add an 'All' option to the drill-down menu.
FILEDEF DITEMS DISK FOCCACHE/DITEMS.FTM (APPEND
-RUN
-SET &COMMA = ',';
-WRITE DITEMS DRILLMENUITEM='All Seats',FOCEXEC=TEST2(GOTO='-GOTO HOLD1' RNDM=&RNDM SEATS='FOC_NONE')&COMMA
-RUN
-*
-* Extract all necessary data to hold file.
TABLEF FILE CAR
 PRINT COUNTRY CAR MODEL SEATS
 ON TABLE HOLD AS FOCCACHE/HOLD1
END
-*
-HOLD1
-* Display report with drill down.
TABLE FILE HOLD1
 PRINT SEATS
 BY COUNTRY
 BY CAR
 BY MODEL
 WHERE SEATS EQ &SEATS
 ON TABLE SET BYDISPLAY ON
 ON TABLE SET ONLINE-FMT HTML
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  TYPE=TITLE,COLUMN=SEATS,COLOR='BLUE',STYLE=UNDERLINE,
-MRNOEDIT -INCLUDE FOCCACHE/DITEMS.FTM
  $
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

JG:
WHEN I RUN THE WAHT U POSTED IT GIVES ME THIS ERROR :ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key DITEMS.fex.

I ACUTALLY TRIED THE OPTION OF USING "-DEFAULTH"
IT RUNS THE REPORT WITH SELECTED DEFAULT "FOC_NONE" FOR &SEATS.... BUT LATER IT DOESNT SHOW ME THE OPTIONS OF AUTO PROMPTING...

i ACTUALLY WANTED THIS FUNCTIONALITY... RUNTHE REPORT WITH DEFAULT SETTINGS ....AND THEN GIVEN OPTION TO SELECT NUMBER OF "SEATS" AND RE REUN THE REPORT...


DAN:I ACTUALLY RUN THE CODE YOU POSTED ... BUT ITS STILL PROMPTING HTE VARIABLE "&SEATS" AND "&GOTO" WITH VALUES IN IT....I NEED THE ABOVE MENTIONED FUNCTIONALITY...

PLEASE HELP ME WITH THIS

quote:

DAN:I ACTUALLY RUN THE CODE YOU POSTED ... BUT ITS STILL PROMPTING HTE VARIABLE "&SEATS" AND "&GOTO" WITH VALUES IN IT..

As I mentioned in my post, you need to change the properties of the focexec to turn off "Prompt for Parameters".

looks great...Thank you vey much...

That solves my problem partially...
Actually i have two prompting varibles one for the format(html, ahtml, pdf, excel) and as i said another for the number of seats...

How can this work for two amper variables...

Unfortunately, as JG also mentioed in his post, this technique will only work for HTML output.

Ok thank you guys very much

Here's a new version of my earlier code that allows the user to select an output format type for the report currently being displayed in HTML. However, if anything but HTML is selected, the drill-down links are disabled because they won't work in the other formats.

-SET &THISEXEC = EDIT(&FOCFOCEXEC,'$99999999');
-SET &RNDM = EDIT(HHMMSS('A8'),'99$99$99');
-DEFAULT &SEATS = 'FOC_NONE'
-DEFAULT &OUTFORM = 'HTML';
-DEFAULT &GOTO = '-*';
-*
-* For subsequent executions, skip creation of the drill-down menu and data hold file.
&GOTO.EVAL
-*
-* Create drill-down menu.
TABLE FILE CAR
 SUM
 COMPUTE PSEATS/F3  = FST.SEATS; NOPRINT
 COMPUTE DITEM/A100 = 'DRILLMENUITEM=''' || FTOA(PSEATS,'(F3)','A3') ||
                      ' Seats'',FOCEXEC=&THISEXEC(GOTO=''-GOTO HOLD1'' RNDM=&RNDM SEATS=' | FTOA(PSEATS,'(F3)','A3') | '),';
 BY SEATS NOPRINT
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE AS FOCCACHE/DRILLS
END
-*
-* Add an 'All' option to the drill-down menu.
FILEDEF DRILLS DISK FOCCACHE/DRILLS.FTM (APPEND
-RUN
-SET &COMMA = ',';
-WRITE DRILLS DRILLMENUITEM='All Seats',FOCEXEC=&THISEXEC(GOTO='-GOTO HOLD1' RNDM=&RNDM SEATS='FOC_NONE')&COMMA.$
-RUN
-*
-* Extract all necessary data to hold file.
TABLEF FILE CAR
 PRINT COUNTRY CAR MODEL SEATS
 ON TABLE HOLD AS FOCCACHE/HOLD1
END
-*
-HOLD1
-* Display report with drill down.
DEFINE FILE HOLD1
 OUTFORM/A5 = '&OUTFORM';
 XSEATS/A8  = '&SEATS.EVAL';
END
-*
TABLE FILE HOLD1
-* Skip output menu if output is not HTML.
-IF &OUTFORM NE 'HTML' GOTO SKIPHEAD ;
 HEADING
  "Select Output Format: <OUTFORM "
-SKIPHEAD
 PRINT SEATS
       XSEATS NOPRINT
 BY COUNTRY
 BY CAR
 BY MODEL
 WHERE SEATS EQ &SEATS ;
 ON TABLE SET BYDISPLAY ON
 ON TABLE SET ONLINE-FMT &OUTFORM
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
-* Skip drilldown menus if output is not HTML.
-IF &OUTFORM NE 'HTML' GOTO SKIPDRILLS ;
  TYPE=HEADING, LINE=1, JUSTIFY=RIGHT, $
  TYPE=HEADING, LINE=1, OBJECT=FIELD, COLOR='BLUE',STYLE=UNDERLINE,
   DRILLMENUITEM='HTML ',FOCEXEC=&THISEXEC(GOTO='-GOTO HOLD1' RNDM=&RNDM SEATS=XSEATS OUTFORM='HTML'),
   DRILLMENUITEM='PDF  ',FOCEXEC=&THISEXEC(GOTO='-GOTO HOLD1' RNDM=&RNDM SEATS=XSEATS OUTFORM='PDF'),
   DRILLMENUITEM='EXL2K',FOCEXEC=&THISEXEC(GOTO='-GOTO HOLD1' RNDM=&RNDM SEATS=XSEATS OUTFORM='EXL2K'),$
  TYPE=TITLE,COLUMN=SEATS,COLOR='BLUE',STYLE=UNDERLINE,
-MRNOEDIT -INCLUDE DRILLS.FTM
-SKIPDRILLS
 ENDSTYLE
END

Excellent:

IT EXACTLY WORKS THE WAY I WANT,
THANK YOU VERY MUCH DAN