Let me preface this by saying, I have done some research and have tried various techniques. I must be missing something.

Challenge: I have a master file. The tables contains all sales years (similar to fiscal years) for which we have data (2007-2015). I want to limit my data in a dynamic parameter/filter within a fex and html page to not allow particular calendar years. Allow me to explain.

First Quarter - July 1 to Sept 30, 2014
Second Quarter - Oct 1 - Dec 31, 2014
Third Quarter - Jan 1 - March 31, 2015
Fourth Quarter - April 1 - June 30, 2015

The master file has data for all quarters and years going to back to the first quarter of Sales Year 2008 which would be for the calendar period of July 1-Sept 30, 2007.

Right now, as per design requirements, I have four separate fexes and four separate html pages. This will not change as it is per provided specifications.

By way of example, in one fex and html combination, if I set the where clause equal to First Quarter, I'd like the dropdown (a dynamic parameter) for sales calendar year to include only 2007-2014. The year 2015 should not be included in the choices nor should it appear in the dropdown.

I tried doing the where in a hold file and then using the hold file to drive the parameters and graph. That did not seem to work.

I found something on the forum that discussed limiting a dynamic dropdown but it had to do with adding tables not data within a specific table.

Here's the code I'm trying to fix although I currently have it functioning with a static list for the years.

-SET &RPT_HEAD = ('First Quarter')||(' ' | &SALES_CALENDAR_YEAR);
-SET ®ION_HEAD = IF ®ION_NAME EQ '_FOC_NULL' AND &DEALERSHIP_NAME EQ '_FOC_NULL' THEN 'Companywide' ELSE
- IF ®ION_NAME EQ ®ION_NAME AND &DEALERSHIP_NAME EQ '_FOC_NULL' THEN ®ION_NAME ELSE
- IF &DEALERSHIP_NAME EQ &DEALERSHIP_NAME THEN &DEALERSHIP_NAME ELSE
- ®ION_NAME;

DEFINE FILE SALES_STAFF_STATUS
FTPT_STATUS/A20=
IF SALES_STAFF_STATUS.SALES_STAFF_STATUS.FTPT EQ 'FT' THEN 'Full-time'
ELSE IF SALES_STAFF_STATUS.SALES_STAFF_STATUS.FTPT EQ 'PT' THEN 'Part-time'
ELSE 'Unknown';
END

-*IA_GRAPH_BEGIN
-*Do not delete or modify the comments below
*-INTERNAL_COMMENT
-*Do not delete or modify the comments above
ENGINE INT CACHE SET ON
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';
GRAPH FILE SALES_STAFF_STATUS
-* Created by Info Assist for Graph
SUM PCT.SALES_STAFF_STATUS.SALES_STAFF_STATUS.COUNT/P8.1 AS ' '
BY SALES_STAFF_STATUS.SALES_STAFF_STATUS.FTPT_STATUS
WHERE SALES_STAFF_STATUS.SALES_STAFF_STATUS.REGION_NAME EQ '®ION_NAME.(,,,).Region:.';
WHERE SALES_STAFF_STATUS.SALES_STAFF_STATUS.DEALERSHIP_NAME EQ '&DEALERSHIP_NAME.(FIND SALES_STAFF_STATUS.SALES_STAFF_STATUS.DEALERSHIP_NAME IN SALES_STAFF_STATUS).Dealership:.';
WHERE SALES_STAFF_STATUS.SALES_STAFF_STATUS.SALES_TERM_NAME EQ 'First Quarter'
WHERE SALES_STAFF_STATUS.SALES_STAFF_STATUS.SALES_CALENDAR_YEAR EQ '&SALES_CALENDAR_YEAR.(FIND SALES_STAFF_STATUS.SALES_STAFF_STATUS.SALES_CALENDAR_YEAR IN SALES_STAFF_STATUS).SALES_CALENDAR_YEAR:.';
HEADING
"National"
"Sales Force by Full-time/Part-time Employment Status"
"&RPT_HEAD"
"®ION_HEAD"
FOOTING
"Source: Sales Staff Data Collected July 1 "
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET EMBEDHEADING ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 0
ON GRAPH SET LOOKGRAPH PIEMULTI
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
setPieFeelerTextDisplay(1);
*END
TYPE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
TYPE=HEADING, JUSTIFY=LEFT, FONT='Calibri', SIZE=10, COLOR=RGB(0 0 0), STYLE=BOLD, $
TYPE=FOOTING, JUSTIFY=RIGHT, FONT='Calibri', SIZE=8, COLOR=RGB(0 0 0), STYLE=NORMAL, $
*GRAPH_SCRIPT
setUseSeriesShapes(true);
setMarkerSizeDefault(50);
setPlace(false);
setPieRotate(0);
setToolTipDisplay(true);
setTextFormatPreset(getPieSliceFeelerPercent(),3);
setTextFormatPreset(getPieSliceFeelerValue(),-1);
setFontName(getPieLabel(),"Calibri");
setFontName(getPieBarLabel(),"Calibri");
setFontName(getPieRingLabel(),"Calibri");
setFontName(getPieSliceLabel(),"Calibri");
setFontName(getLegendText(),"Calibri");
setFontName(getO1Title(),"Calibri");
setFontName(getX1Title(),"Calibri");
setFontName(getY1Title(),"Calibri");
setFontName(getO2Title(),"Calibri");
setFontName(getY2Title(),"Calibri");
setFontName(getY3Title(),"Calibri");
setFontName(getY4Title(),"Calibri");
setFontName(getY5Title(),"Calibri");
setFontName(getTitle(),"Calibri");
setFontName(getSubtitle(),"Calibri");
setFontName(getFootnote(),"Calibri");
setFontName(getAllText(),"Calibri");
setFontSizeAbsolute(getPieSliceLabel(), true);
setPlaceResize(getPieSliceLabel(), 0);
setFontSizeAbsolute(getPieLabel(), true);
setFontSizeInPoints(getPieLabel(), 8);
setPlaceResize(getPieLabel(), 0);
setTextWrap(getPieLabel(),true);
setReportParsingErrors(false);
setSelectionEnableMove(false);
setPieTilt(0);
setPieDepth(0);
setDepthRadius(0);
setDepthAngle(0);
setTextFormatPattern(getPieSliceLabel(),"");
setTextJustHoriz(getPieLabel(),0);
setTextString(getAnnotation(0),"Unknown 0%");
setDisplay(getAnnotation(0),true);
setTextWrap(getAnnotation(0),true);
setRect(getPieFrame(),new Rectangle(-15000,-10000,30000,22000));
setLegendPosition(1);
setFillColor(getLegendText(),new Color(0,0,0));
setFontSizeAbsolute(getPieSliceLabelInside(),true);
setAutofit(getPieSliceLabelInside(),false);
setFontSizeInPoints(getPieSliceLabelInside(),10);
setFontSizeInPoints(getPieSliceLabel(),10);
setBorderColor(getChartBackground(),new Color(32,0,32));
setFillColor(getChartBackground(),new Color(255,255,255));
setDisplay(getLegendArea(),true);
setTransparentBorderColor(getChartBackground(),true);
setTextJustHoriz(getPieSliceLabelInside(),1);
setTextJustHoriz(getPieSliceLabel(0),1);
setSelectionEnableMove(false);
setPieFeelerTextDisplay(4);
setTextFormatPreset(getPieSliceLabel(),29);
setPieLabelDisplay(0);
setLegendReverse(true);
setReverseSeries(true);
setDisplay(getFootnote(),true);
setUserToolTip ('[SL]: [YV]' );
*END
ENDSTYLE
END
-*IA_GRAPH_FINISH

This message has been edited. Last edited by: <Emily McAllister>,

WebFocus 8.2.04
WebFocus 8.2.04

Hi,

first up. I would like to advice you to work with separate fexi to populate the dropdownlists in your HTML.

in the fex just go like this:

-PROMPT &PARAMETER_ONE
-PROMPT &PARAMETER_TWO
-PROMPT &PARAMETER_THREE

And use a fex to query the desired options. In a fex you have much more controle over what is happening.

TABLE FILE CAR
SUM CAR
BY CAR
ON TABLE PCHOLD FORMAT XML
END

The "SUM CAR" being what is shown to the user and the "BY CAR" what is send to the fex as actual value for the &VARIABLE.

Does that help?

Hi JulieA,
When you add code in your post, put your code between the code tag which is the last icon option < / >, it could make it easier to read since it respect the alignment added in you code.

You cannot filter an auto-prompting since the data is retrieve from the whole referenced data set.
To perform the thing that you are requesting, the table (data set) used by the auto-prompting must only contain the filtered data and MUST already exist, it cannot be defined at run time.

As per Dave mentioned, when you want to control what is displayed in a list box, do not use auto-prompting, use another fex that will populate the data set for the list.

MartinY:
Thank you for your answer about the table and autoprompting. That makes sense. I had wondered that myself. Also, thanks for the reminder about the code tag.


Dave:
If I understand correctly, I should use another fex to populate the data set for the list. I'll research that as it's not something I've done previously. It somewhat makes sense though. Thank you.

JulieA,

in the HTML editor. Just select the dropdown. In the "Properteis and settings" pan choose Data Type "Dynamic" and find the fex that will provide the data.

Good luck,
Dave

Dave:

The step you outlined worked well in the HTML editor. One additional question: The fex that provides the data feeds six charts on a HTML page, all controlled by six separate fexes. When a specific year or earlier is selected for one fex, I've programmed a specific error message (utilizing HTML form) to appear. That's the only component not working at this time. Any thoughts?

So are you saying that the fex with the HTML form is not working?

Try putting these two lines at the beginning of your fex to verify what is being sent to it:

-? &
-EXIT

...did you check in the "parameters" tab whether the HTML-input is correctly linked to the fex?

Easy check is to add this to the first lines of the fex. Just to see the parameter input.

-TYPE DEBUG:
-TYPE &YOUR_PARAMETER_NAME_HERE
-EXIT

should do the trick.

Thank you. The two debugging options helped me. The parameters were correct in the fex itself, but some fexes were not linked properly.