Jump to content

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


Emily Lee
Go to solution Solved by David Beagan ,

Recommended Posts

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • Solution

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

-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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...