have a challenge. I need to revise my graph code to eliminat...

[Martin Yergeau]

Have you tried to perform the same thing with a tabular report

Have you tried to just produce the data as you want it and then perform the graph from the sub-query

[Julie Anderson]

have a challenge. I need to revise my graph code to eliminate instances where my total sales count LT 100 but then I need to graph the percentages of the resulting cases. I have some ideas Ive been working with this morning in 8.2.04M, but Ive not quite gotten the situation figured out yet.

Here is my code:

-*I need these defined fields.

DEFINE FILE JSALES

QUANTITY/P8.6 MISSING ON NEEDS SOME DATA=EDIT (JSALES.QUANTITY);

FLAG1/P8 MISSING ON NEEDS SOME DATA=IF CATEGORY EQ 7 THEN 0 ELSE 1;

FLAG2/P8=IF JSALES.QUANTITY EQ 0 THEN 0 ELSE 1;

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

-*SET PAGE-NUM=NOLEAD

-*SET ARGRAPHENGINE=JSCHART

-*SET EMBEDHEADING=ON

-*SET GRAPHDEFAULT=OFF

-DEFAULTH &WF_STYLE_UNITS=PIXELS;

-DEFAULTH &WF_STYLE_HEIGHT=405.0;

-DEFAULTH &WF_STYLE_WIDTH=770.0;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

GRAPH FILE JSALES

-* Created by Info Assist for Graph

SUM PCT.JSALES.JSALES.QUANTITY AS Quantity Rate

-*I have six categories. I need the total of those categories as a sales count.

-*Then, I need to eliminate sales counts totals LT 100 with a WHERE TOTAL statement.

SUM JSALES.JSALES.QUANTITY AS QTYCOUNT NOPRINT

-*Categories

BY JSALES.JSALES.CATEGORY

ACROSS JSALES.JSALES.SALES_YEAR AS Sales Year

-*I have some filters here.

WHERE TOTAL (JSALES.JSALES.FLAG1 GE 1) AND (JSALES.JSALES.FLAG2 GE 1);

-*WHERE TOTAL JSALES.JSALES.CATEGORY_SORT NE 7;

WHERE JSALES.JSALES.SALES_YEAR GE 2010;

-*My graph would continue as is using the PCT field.

ON GRAPH PCHOLD FORMAT JSCHART

ON GRAPH SET VZERO ON

ON GRAPH SET HTMLENCODE ON

ON GRAPH SET GRAPHDEFAULT OFF

ON GRAPH SET UNITS PIXELS

ON GRAPH SET HAXIS 975

ON GRAPH SET VAXIS 350

ON GRAPH SET GRMERGE ADVANCED

ON GRAPH SET GRMULTIGRAPH 0

ON GRAPH SET GRLEGEND 1

ON GRAPH SET GRXAXIS 1

ON GRAPH SET LOOKGRAPH HBRSTK1

ON GRAPH SET STYLE *

*GRAPH_SCRIPT

setPieDepth(0);

setPieTilt(0);

setDepthRadius(0);

setCurveFitEquationDisplay(false);

setPlace(true);

*END

-*My stylesheet is here.

*GRAPH_SCRIPT

setReportParsingErrors(false);

setSelectionEnableMove(false);

setDisplay(getDataTextStackedTotalOnTop(),true);

setZeroValueDataTextStackedTotalOnTopDisplay(false);

setZeroValueDataTextDisplay(false);

setScaleMinAuto(getY1Axis(),false);

setScaleMaxAuto(getY1Axis(),false);

setExcludeMaxLabel(getY1Label(),true);

setScaleMin(getY1Axis(),0.0);

setScaleMax(getY1Axis(),120.0);

setGridStepAuto(getY1MajorGrid(),false);

setDisplay(getY1MajorGrid(),true);

setGridStyle(getY1MajorGrid(),2);

setFontName(getDataTextStackedTotalOnTop(),CALIBRI);

setFontSizeAbsolute(getDataTextStackedTotalOnTop(),true);

setAutofit(getDataTextStackedTotalOnTop(),false);

setPlaceResize(getDataTextStackedTotalOnTop(),0);

setY1MustIncludeZero(false);

setScaleMinAuto(getY1Axis(),false);

setScaleMin(getY1Axis(),0.01);

setDisplayOffScale(getY1Axis(),false);

setZeroValueDataTextDisplay(false);

setTransparentFillColor(getFrame(),true);

setGridStep(getY1MajorGrid(),20.0);

setDataTextDisplay(true);

setDisplay(getDataText(),false);

setDataTextPosition(2);

setDataTextRadiusDefault(10);

setFontName(getDataText(),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(getAllText(),Calibri);

setDisplay(getFootnote(),false);

setDisplay(getO1MajorGrid(),false);

setTextFormatPreset(getY1Label(),28);

setTextFormatPreset(getDataTextStackedTotalOnTop(),29);

setFontSizeAbsolute(getDataTextStackedTotalOnTop(), true);

setFontSizeInPoints(getDataTextStackedTotalOnTop(),12);

setFontStyle(getDataTextStackedTotalOnTop(),0);

setDataTextAngleDefault(90);

setDataTextRadiusDefault(0);

setToolTipFontName(Calibri);

setToolTipFontSize(11);

setRect(getAnnotation(0),new Rectangle(-16000.0,9600.0,32000.0,6400.0));

setFrameAutoShade(false);

setTransparentFillColor(getFrameSide(),true);

setTransparentBorderColor(getFrameSide(),true);

setTransparentFillColor(getFrameBottom(),true);

setTransparentBorderColor(getFrameBottom(),true);

setFillColor(getChartBackground(),new Color(255,255,255,0));

setUserToolTip ([sL]: [YV]% );

*END

ENDSTYLE

END

-*IA_GRAPH_FINISH

-RUN

Share

[Julie Anderson]

MartinY:

Thank you for the reply.

As to your suggestion, I created the exact desired output in a tablular report yesterday. I think you are right. The correct solution is to graph from the hold file used to create my tabular report; however, that brings me to a question. Is it possible to take a single field, separate it into six or seven fields, and then reassemble them to graph

I can post my code after a bit in order to better illustrate the question.

The other approach I have thought of is to create defined computed fields ahead of the graph function. Again, I can post my code in a little while.

In the meantime, thank you for your current suggestion. I appreciate it.

[Manoj Chaurasia]

Julie

You are on the right track you can use the EDIT function to break the single field into separate other fields in a DEFINE or COMPUTE.

[Julie Anderson]

Everyone,

Ive returned to this challenge, and Im posting some code I got from IBI.

The first section works beautifully for me in my environment with my fields.

The second section needs a little tweaking.

At a minimum, I think I need to join two fields. I have several rows of data for each year and each entity combination.

Ill admit Ive not worked with JOINs much. Ive been reading up on them in the documentation, and I learned why the BINARY format is necessary in this instance. Ive also learned about the difference between UNIQUE and MULTIPLE. I think I need multiple as this is a one to many relationship for at the minimum of two fields.

Thoughts

-*ENGINE INT CACHE SET ON

 

*-HOLD_SOURCE

-DEFAULTH &WF_SUMMARY=Summary;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

TABLE FILE IBISAMP/SALES

SUM SALES.PRODUCT.UNIT_SOLD AS (UNIT_SOLD_TOTAL, SALES.PRODUCT.UNIT_SOLD, UNIT_SOLD_TOTAL )

BY SALES.DATE_SEG.DATE

ON TABLE HOLD AS File1 FORMAT BINARY

ON TABLE NOTOTAL

ON TABLE SET CACHELINES 100

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET SQUEEZE ON

ON TABLE SET HTMLCSS ON

ON TABLE SET HTMLENCODE ON

ON TABLE SET EMPTYREPORT ON

ON TABLE SET GRWIDTH 1

ON TABLE SET ASNAMES MIXED

ON TABLE SET HOLDATTRS ON

ON TABLE SET HOLDLIST PRINTONLY

ON TABLE SET ASNAMES MIXED

ON TABLE SET HOLDATTRS ON

ON TABLE SET HOLDLIST PRINTONLY

ON TABLE SET STYLE *

INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$

TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, $

ENDSTYLE

END

-RUN

-*COMPONENT=Join_J001

JOIN FILE File1 AT FILE1.FILE1.DATE

TO UNIQUE FILE IBISAMP/SALES AT SALES.DATE_SEG.DATE TAG J001 AS J001

WHERE FILE1.FILE1.DATE EQ SALES.DATE_SEG.DATE;

END

-DEFAULTH &WF_STYLE_UNITS=PIXELS;

-DEFAULTH &WF_STYLE_HEIGHT=405.0;

-DEFAULTH &WF_STYLE_WIDTH=770.0;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

GRAPH FILE File1

-* Created by Info Assist for Graph

SUM J001.PRODUCT.UNIT_SOLD

BY J001.PRODUCT.PROD_CODE

ACROSS J001.DATE_SEG.DATE

WHERE FILE1.FILE1.UNIT_SOLD_TOTAL GT 50;

ON GRAPH PCHOLD FORMAT JSCHART

ON GRAPH SET HTMLENCODE ON

ON GRAPH SET GRAPHDEFAULT OFF

ON GRAPH SET ARGRAPHENGIN JSCHART

ON GRAPH SET VZERO 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 HBRSTK1

ON GRAPH SET AUTOFIT ON

ON GRAPH SET STYLE *

*GRAPH_SCRIPT

setPieDepth(0);

setPieTilt(0);

setDepthRadius(0);

setCurveFitEquationDisplay(false);

setPlace(true);

*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

[Manoj Chaurasia]

Hi Julie

What issue are you experiencing in the second section. UNIQUE vs MULTIPLE is a bit tricky to explain but in most cases UNIQUE should do the trick even in a 1 to many relationship. It is a quick thing to change to see differences in results against your data.

[Julie Anderson]

The unique did not work in that the equivalent of what I have for prod_code should result in seven different bar segments. When I changed to multiple, I got the the bar segments I expected but when I add in my filters to the code in the table section, it seems that no matter what I select, the data is the same in the graph section. So, thats why I have narrowed down the issue to the join. I have not done a lot of joins, and I have never had to do a join with a graph.

[Manoj Chaurasia]

Please post your code with the where conditions and we might be able to see the issue.

[Julie Anderson]

ENGINE INT CACHE SET ON

 

*-HOLD_SOURCE

-DEFAULTH &WF_SUMMARY=Summary;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

DEFINE FILE SALES

SALESCOUNT/I5 MISSING ON NEEDS SOME DATA=EDIT (SALES.SALES.SALESCOUNT);

SALES_STATUS/A10 MISSING ON NEEDS SOME DATA=DECODE SALES.SALES.SALES_STATUS (Full, Part);

END

TABLE FILE SALES

SUM SALES.SALESCOUNT AS (SALESCOUNT_TOTAL, SALES.SALESCOUNT, SALESCOUNT_TOTAL )

BY SALES.SALES.SALES_YEAR

BY SALES.SALES.PROD_TYPE

WHERE SALES.SALES.STORE_NAME EQ &STORE_NAME.(FIND SALES.SALES.STORE_NAME IN SALES|FORMAT=A66V).STORE_NAME:.QUOTEDSTRING;

WHERE SALES.SALES.STATUS EQ &STATUS.(FIND SALES.SALES.STATUS IN SALES).Salesperson Type:.;

WHERE SALES.SALES.TRACK EQ &TRACK.(FIND SALES.SALES.TRACK IN SALES).TRACK:.;

WHERE SALES.SALES.SALES_STATUS EQ &SALES_STATUS.(,).Sales Status:.;

WHERE SALES.SALES.SALES_DESC EQ &SALES_DESC.(FIND SALES.SALES.SALES_DESC IN SALES).Sales Desc:.;

ON TABLE HOLD AS File1 FORMAT BINARY

ON TABLE NOTOTAL

ON TABLE SET CACHELINES 100

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET SQUEEZE ON

ON TABLE SET HTMLCSS ON

ON TABLE SET HTMLENCODE ON

ON TABLE SET EMPTYREPORT ON

ON TABLE SET GRWIDTH 1

ON TABLE SET ASNAMES MIXED

ON TABLE SET HOLDATTRS ON

ON TABLE SET HOLDLIST ALLKEYS

ON TABLE SET ASNAMES MIXED

ON TABLE SET HOLDATTRS ON

ON TABLE SET HOLDLIST PRINTONLY

ON TABLE SET STYLE *

TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, $

ENDSTYLE

END

-RUN

-*COMPONENT=Join_J001

JOIN FILE File1 AT FILE1.FILE1.SALES_YEAR

TO MULTIPLE FILE SALES AT SALES.SALES_YEAR TAG J001 AS J001

WHERE FILE1.FILE1.SALES_YEAR EQ SALES.SALES_YEAR;

END

-DEFAULTH &WF_STYLE_UNITS=PIXELS;

-DEFAULTH &WF_STYLE_HEIGHT=405.0;

-DEFAULTH &WF_STYLE_WIDTH=770.0;

-DEFAULTH &WF_TITLE=WebFOCUS Report;

GRAPH FILE File1

-* Created by Info Assist for Graph

SUM J001.SALESCOUNT/I5 AS SALESCOUNT

BY J001.PROD_TYPE

ACROSS J001.SALES_YEAR AS Sales Year

WHERE FILE1.FILE1.SALESCOUNT_TOTAL GE 30;

ON GRAPH PCHOLD FORMAT JSCHART

ON GRAPH SET HTMLENCODE ON

ON GRAPH SET GRAPHDEFAULT OFF

ON GRAPH SET ARGRAPHENGIN JSCHART

ON GRAPH SET VZERO 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 HBRSTK1

ON GRAPH SET AUTOFIT ON

ON GRAPH SET STYLE *

*GRAPH_SCRIPT

setPieDepth(0);

setPieTilt(0);

setDepthRadius(0);

setCurveFitEquationDisplay(false);

setPlace(true);

*END

TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $

*GRAPH_SCRIPT

setReportParsingErrors(false);

setSelectionEnableMove(false);

*END

ENDSTYLE

END

-RUN

[Manoj Chaurasia]

Julie

Sorry it took so long to get back, and Im sorry nothing jumps out that looks like it would cause the problem. A join is a join it doesnt matter if it is a graph or a report they behave the same so maybe create a report from the join and look at the data in that form

[Julie Anderson]

Thank you, Chuck.

As it turns out, I had a case going at the same time. Don T. provided me just enough sample code and a quick conversation the other day that resulted in me experimenting with the code a little bit more on Wednesday. I was finally able to determine the proper order in which to place my defines, where statements, joins and hold files. I will change this post to solved. Thank you for the response. I appreciate it.