How to combine two charts with different BY field into one graphic?

[Emily Lee]

How to combine two charts with different BY field into one graphic?

 I use WF 9.0.3 DESIGNER to crate charts.

I like to show chart-1 (ave car sale by country) with bar chart, and place the value of "ave car sales world-wide" as a reference line in chart-1?

          -Chart-1 is "ave car sales by Country".

           -Chart-2 is "ave car sales world-wide".

I have hard time of doing it, because chart-1 has BY COUNTRY as X axis, and chart-2 do not use the BY field "by Country". Although the "ave car sales world-wide" is a constant, but it will be different by YEAR.  The IBI sample data table CAR does not have the DATE dimension, but in my real database, we have "Year" column, I will use the "YEAR" as a filter for user to choose the "ave car sale by country" graphic.

Or if I can't combine these two charts, do you have other suggestion?

-***** avg. sales by Country******

ENGINE INT CACHE SET ON

SET PAGE-NUM=NOLEAD

SET SQUEEZE=ON

-DEFAULTH &WF_HTMLENCODE=ON;

SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON

-DEFAULTH &WF_EMPTYREPORT=ON;

SET EMPTYREPORT=&WF_EMPTYREPORT

SET EMBEDHEADING=ON

SET GRAPHDEFAULT=OFF

SET ARVERSION=2

-DEFAULTH &WF_TITLE='WebFOCUS Report';

GRAPH FILE ssa_co_sys_disability_development/base/common/car

-* Created by Designer for Graph

SUM AVE.CAR.BODY.SALES/D6:C

BY CAR.ORIGIN.COUNTRY

ON GRAPH PCHOLD FORMAT JSCHART

ON GRAPH SET VZERO OFF

ON GRAPH SET GRWIDTH 1

ON GRAPH SET HAXIS 770.0

ON GRAPH SET VAXIS 405.0

ON GRAPH SET LOOKGRAPH BAR

ON GRAPH SET EMBEDHEADING ON

ON GRAPH SET AUTOFIT ON

ON GRAPH SET STYLE *

INCLUDE=IBFS:/WFC/Global/Themes/Standard/Default/theme.sty,$

TYPE=REPORT, TITLETEXT='Chart1', ARREPORTSIZE=DIMENSION, ARFILTER_TARGET='*', ARGRAPHENGINE=JSCHART, $

TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $

TYPE=DATA, COLUMN=N2, BUCKET=y-axis(1), $

*GRAPH_SCRIPT

*GRAPH_JS_FINAL

"series":    [

       {

           "dataLabels": {

               "visible": true,

               "content": "{{metadata_value("default", "dataLabels")}}"

           },

           "series": 0

       }

   ],

"blaProperties": {

   "seriesLayout": "stacked"

}

*END

ENDSTYLE

END

-****** avg. CAR Sales world-wide ******

Same as above, the only difference is "no BY field":

GRAPH FILE ssa_co_sys_disability_development/base/common/car

-* Created by Designer for Graph

SUM AVE.CAR.BODY.SALES/D6:C

ON GRAPH PCHOLD FORMAT JSCHART

[David Beagan 2]

Would the technique documented here:

Developing Reporting Applications - Including Two HTML5 Charts in an HTML Page, page 229

work for you?

[David Beagan 2]

Another approach as documented on page 265 of the above mentioned manual:

-TYPE WEBFOCUS CGIVAR IBIWF_mreports=FRAME -TYPE WEBFOCUS CGIVAR IBIWF_mrcolumns=2  GRAPH FILE carSUM RPMBY COUNTRYON GRAPH PCHOLD FORMAT JSCHARTON GRAPH SET LOOKGRAPH BARON GRAPH SET AUTOFIT ONON GRAPH SET STYLE *INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $TYPE=DATA, COLUMN=N2, BUCKET=y-axis, $ENDSTYLEEND GRAPH FILE carSUM ACCELBY COUNTRYON GRAPH PCHOLD FORMAT JSCHARTON GRAPH SET LOOKGRAPH BARON GRAPH SET AUTOFIT ONON GRAPH SET STYLE *INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $TYPE=DATA, COLUMN=N2, BUCKET=y-axis, $ENDSTYLEEND

[Emily Lee]

Hi David,

I added more information to my original posted question.

I will try to test it with the instruction of page 229 and 265 of the manual.

I will let you know. thanks.

Emily Lee

[David Beagan 2]

Hi Emily, the code below would be one way to get the worldwide average as a reference line.

-SET &SALES=0; TABLE FILE CAR SUM AVE.SALES ON TABLE HOLD AS AVERAGE END-RUN-READFILE AVERAGE GRAPH FILE carSUM AVE.SALESBY COUNTRYON GRAPH PCHOLD FORMAT JSCHARTON GRAPH SET LOOKGRAPH BARON GRAPH SET AUTOFIT ONON GRAPH SET STYLE *INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $TYPE=DATA, COLUMN=N2, BUCKET=y-axis, $*GRAPH_JS"referenceLines": [ {"value": &SALES, "axis": "y", "line": {"color": "green", "width": 3, "dash": ""}, "label": {"text": "Average:", "color":"green", "font": "bold 10pt Sans-Serif"}, "anchor": "start", "showValue": true },]*ENDENDSTYLEEND

[Emily Lee]

Daivd,

thank you so much. I will give it a try.

We start the graphic with Designer, I guess I have to modify the graphic "edit with text" afterword.

Will get back to you.

[David Beagan 2]

Yes, I think this one requires at lease some coding in the text editor.

[Emily Lee]

David,

Your suggestion of "Read in the World-avg as a value and add JS code - 'reference line' into the style sheet" works like a charm.

I am able to use the "Designer" to create my original two charts with exact same filter selections, but one is nation-wide avg ( without the by field), one is region-wide avg (by Region), then modify the fex programs with your suggestions by the "editor". I get them all work now.

Thank you for this simple and great solution.

Emily

[David Beagan 2]

Good to hear that it worked out for you.

[Emily Lee]

HI David and All,

Sorry, I encounter another issue:

When a graphic created with Designer, it has the auto drill function before adding a reference line;

but after adding a reference line by “editing with text”. The graphic loses the “auto drill down” function.

It seems I can only choose one way to the other, cannot have both?

See picture.

thanks,

Emily

[David Beagan 2]

Could you post your code for the fex and business view?

[Emily Lee]

please see the attachment. thanks.

Emily

[David Beagan 2]

In WebFOCUS 9.1.0 I reproduced your issue with the Auto Drill feature not working with the Reference Line. I doesn't make sense to me, seems like a bug. You could put in a case on this, get ibi's take on it. Seems like something that should be fixed.

There might be an alternative workaround using a different coding technique. I will see what I can find.

[David Beagan 2]

The other technique for coding Reference Lines as documented in WebFOCUS Graphics manual won't work either. I have discovered that it is not actually the Reference Line that is the problem. It is the amper variable in the style sheet that causes the Auto Drill to go away.

There may be another workaround, kind of messy, to write the needed style sheet code for the Reference Line to a file instead of creating the variable. Then the style file would be included by your report.

[Emily Lee]

David,

Greatly appreciate your time and efforts.

Meanwhile I will use less desirable alternative. I may open a case with IBI later.

thank you,

Emily Lee