I may be missing something simple here so I apologize in advance. I am creating some simple charts by using the SQL wizard and pasting in a SQL statement. I then apply a few dynamic Prompts for further narrowing the result set. The problem that I have is that the dynamic prompt is only pulling a select few of the potential results. Let's say that my data is a monthly snapshot for the year, it will only show maybe 2 months in my available values in the prompt whereas it should be 12. My initial thought was that when WF is determining the unique value set to display it was out of order therefore I needed to sort it for it to find the unique list. I applied an order by to the SQL command to no avail. Below is what I believe is used to build the data for the prompt. Thoughts?

WHERE SQLOUT.SQLOUT.CAPTURE_DATE EQ DT('&CAPTURE_DATE.(FIND SQLOUT.SQLOUT.CAPTURE_DATE IN SQLOUT |FORMAT=HYYMDS).CAPTURE_DATE:.');

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

Auto-prompting does only display existing values from selected source. It cannot display values that it can't find :

(FIND SQLOUT.SQLOUT.CAPTURE_DATE IN SQLOUT

It's not displaying POTENTIAL values, it display EXISTING values from the data set.

Understandable, but when I initially run the report nothing is selected therefore I would assume that all of the possibilities should be available since nothing has been filtered yet. It is as if it does not find all of the unique values at initial execution. Am I lost?

quote:

all of the possibilities should be available

Do all of the possibilities exist as rows in the table that you read to create SQLOUT? Quite often the fact table may not have data for all dates, people often use a calendar table as the source for date controls on parameter screens...

Thank you for your response Francis! Yes it does have every entry. I re-created the table with 5 columns and then re-created a new scatter chart with a Prompt for date. All dates would show up. Worked perfectly as expected. Then I added another column to the table in the DB, but did not change the chart. I explicitly select my columns in the query in the chart therefore the newly added column is irrelevant to my current design. It now only shows a subset of the dates when I run it. The column I added is populated for every row with the same value. I am so confused as to why modifying an attribute of the table would change how WF understands the available values.

newbie2 if we're not all confused by your last post, I have to confess that I am.

Auto-prompting retrieve distinct row value from a column of the data source.

So adding a new column to your data source will not affect the result of the auto-prompting if nothing is changed to it.

Adding ROWS may affect the result if new distinct data (not previously existing) do now exist in the column that is used for the auto-prompting.

Providing us with sample data and code may help us helping you.

Ha! Thank you for the reply MartinY! I am happy to hear that you are just as confused as I am... The path that I have been starting with is based around creating a SQL chart. I paste my query into the wizard, bada bing bada boom I have a chart. Everything worked great upon the initial build. Once I changed the table structure things went crazy. Since my last post I decided to create a view in the DB with the query that I started with before and then abstract that into a synonym. So far that method seems to be working correctly. I have modified the base table multiple times with no ill effect. I still do not understand why the other method gives me inconsistent results. The below bits of code are for the two charts that I am playing with. I have excluded the DB bits. One is based on a Synonym and the other is based on the pasted SQL. They are identical other than the breadcrumbs for source of the data. The SQL behind the Synonym and the SQLOUT are identical. It reminds me of an issue that I have seen with sampling. Basically instead of getting the distinct list it is sampling the data to produce the assumed possible outcomes. It is my understanding that WF does not function that way, the example is merely for frame of reference.

BASED ON A Synonym

 
ENGINE INT CACHE SET ON
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
GRAPH FILE kpi_basic_data
-* Created by Info Assist for Graph
COUNT KPI_BASIC_DATA.KPI_BASIC_DATA.ATTRITIONPERC
BY KPI_BASIC_DATA.KPI_BASIC_DATA.CAPTURE_DATE
ACROSS KPI_BASIC_DATA.KPI_BASIC_DATA.ATTRITIONPERC
WHERE KPI_BASIC_DATA.KPI_BASIC_DATA.CAPTURE_DATE EQ &CAPTURE_DATE.(OR(FIND KPI_BASIC_DATA.KPI_BASIC_DATA.CAPTURE_DATE IN KPI_BASIC_DATA |FORMAT=HYYMDS)).CAPTURE_DATE:.;
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE 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 1
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
setUseSeriesShapes(true);
setMarkerSizeDefault(50);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*END
ENDSTYLE
END
-RUN

-*IA_GRAPH_FINISH 

EMBEDDED SQL

ENGINE INT CACHE SET ON
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
GRAPH FILE SQLOUT
-* Created by Info Assist for Graph
COUNT SQLOUT.SQLOUT.ATTRITIONPERC
BY SQLOUT.SQLOUT.CAPTURE_DATE
ACROSS SQLOUT.SQLOUT.ATTRITIONPERC
WHERE SQLOUT.SQLOUT.CAPTURE_DATE EQ &CAPTURE_DATE.(OR(FIND SQLOUT.SQLOUT.CAPTURE_DATE IN SQLOUT |FORMAT=HYYMDS)).CAPTURE_DATE:.;
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE 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 1
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
setUseSeriesShapes(true);
setMarkerSizeDefault(50);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*END
ENDSTYLE
END
-RUN

-*IA_GRAPH_FINISH  

I don't see any SQL.