***CLOSED Graph followed by Excel Sheet on drill down
I have a drill down that renders a graph. I created a separate report that opens in Excel. When I copied the code directly following the graph code, I essentailly get an HTML graph with the data immediately thereafter. I really wanted the data to open in Excel.
Is there a way around this...I assume the Excel 2000 setting in the data report I selected is being ignored.
Note it opens in Excel fine if I comment out the graph.This message has been edited. Last edited by: RobertF,
WebFOCUS 8206.08 Windows, All Outputs
March 27, 2015, 06:31 PM
<nick z>
Hi, Can you please post your code? Are you not able to run Graphs in Excel format? Does the following work for you?
GRAPH FILE CAR
SUM CAR.BODY.SALES
BY CAR.ORIGIN.COUNTRY
ON GRAPH PCHOLD FORMAT EXL2K
ON GRAPH SET LOOKGRAPH VBAR
END
Thanks. Nick.
March 28, 2015, 09:30 PM
RobertF
the code works but its not what I'd like. I'd like the graph to open in HTML followed by the data opening in Excel...using the car file as an example...teh same sitiation arises..the graph opens but there is no site of the excel sheet...unless I comment out the graph.
-*IA_GRAPH_BEGIN ENGINE INT CACHE SET ON -DEFAULTH &WF_STYLE_UNITS='PIXELS'; -DEFAULTH &WF_STYLE_HEIGHT='405.0'; -DEFAULTH &WF_STYLE_WIDTH='770.0'; GRAPH FILE CAR -* Created by Info Assist for Graph SUM CAR.BODY.SALES BY CAR.ORIGIN.COUNTRY ON GRAPH PCHOLD FORMAT JSCHART ON GRAPH SET VZERO OFF ON GRAPH SET HTMLENCODE ON 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 0 ON GRAPH SET GRXAXIS 1 ON GRAPH SET LOOKGRAPH VBAR 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='WebFOCUS Report', $ *GRAPH_SCRIPT setReportParsingErrors(false); setSelectionEnableMove(false); *END ENDSTYLE END -*IA_GRAPH_FINISH
TABLE FILE CAR PRINT CAR.COMP.CAR CAR.CARREC.MODEL CAR.BODY.DEALER_COST CAR.BODY.RETAIL_COST ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty, $ ENDSTYLE END
WebFOCUS 8206.08 Windows, All Outputs
March 30, 2015, 07:32 AM
MartinY
As far as I know it's not possible to have two different format on the same output (and if it's possible I'll learn something new once again).
To display data from a drill down it must not be in the same fex.
Frist the graph:
-* File graphcntry.fex
GRAPH FILE CAR
SUM CAR.BODY.RETAIL_COST
BY CAR.ORIGIN.COUNTRY
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, COLUMN=N2, FOCEXEC=drillcntry(CNTRY=CAR.ORIGIN.COUNTRY), $
ENDSTYLE
END
Second the drill:
-* File drillcntry.fex
TABLE FILE CAR
SUM
CAR.BODY.RETAIL_COST
BY LOWEST CAR.ORIGIN.COUNTRY
BY LOWEST CAR.COMP.CAR
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ '&CNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
-* Option A
ON TABLE PCHOLD FORMAT &WFFMT.(<Excel,EXL2K>,<HTML,HTML>).Format.
-* Option B
-*ON TABLE PCHOLD FORMAT EXL2K
END
Using Option A from the drill, it gives option to open data in HTML or Excel, option B... in Excel by default.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
March 30, 2015, 08:09 AM
RobertF
...but you can only set up a drill to call one fex...correct? So it would be one or the other?
WebFOCUS 8206.08 Windows, All Outputs
March 30, 2015, 08:17 AM
RobertF
further..when i change my graph to type: Excel in the GUI and run it...nothing shows up in Excel.
WebFOCUS 8206.08 Windows, All Outputs
March 30, 2015, 08:21 AM
RobertF
could you somehow prompt in the called fex: 'Do you want a graph or data' and then evaluate the response to branch to either the Graph or Data code in the fex.
How would I define the prompt?
WebFOCUS 8206.08 Windows, All Outputs
March 30, 2015, 08:44 AM
MartinY
If you have multiple measure, you can have a different fex for each.
GRAPH FILE CAR
SUM CAR.BODY.RETAIL_COST
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, COLUMN=N2, FOCEXEC=drillcntry(CNTRY=CAR.ORIGIN.COUNTRY), $
TYPE=DATA, COLUMN=N3, FOCEXEC=drillcntry2(CNTRY=CAR.ORIGIN.COUNTRY), $
ENDSTYLE
END
-* File drillcntry.fex
TABLE FILE CAR
SUM
CAR.BODY.RETAIL_COST
BY LOWEST CAR.ORIGIN.COUNTRY
BY LOWEST CAR.COMP.CAR
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ '&CNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
-* Option A
ON TABLE PCHOLD FORMAT &WFFMT.(<Excel,EXL2K>,<HTML,HTML>).Format.
-* Option B
-*ON TABLE PCHOLD FORMAT EXL2K
END
-* File drillcntry2.fex
TABLE FILE CAR
SUM CAR.BODY.DEALER_COST
BY LOWEST CAR.ORIGIN.COUNTRY
BY LOWEST CAR.COMP.CAR
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ '&CNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
-* Option A
ON TABLE PCHOLD FORMAT &WFFMT.(<Excel,EXL2K>,<HTML,HTML>).Format.
-* Option B
-*ON TABLE PCHOLD FORMAT EXL2K
END
But not sure to understand:
quote:
So it would be one or the other?
If you mean: "one click (drill) will call one fex", as far as I know, yes. Except if you add some DM code to perform something else than just one fex once the drill is performed or if you drill to a Compound document, then you will have "multiple" fex invoked.
Share your graph code about next question...
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
March 30, 2015, 08:56 AM
MartinY
I thought that your request was to go from a graph and drill to an Excel.
Now you have a fex that will drill either to a graph or an Excel ?
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
March 30, 2015, 09:13 AM
RobertF
What I ideally want is to be able to click an existing graph and drill to another HTML5 graph but also immediately thereafter bring up the data for the graph in Excel. I thought I merely needed to add a report following my graph and set the output to Excel.
Well, you can not have two output modes in one fex.
Next I set my graph type to EXCEL thinking I would get the graph followed by the data...both in Excel.. The graph never appears...perhaps because its using some HTML5 features not sure.
I am now thinking the solution is to drill down to the graph and then add another drill just to bring up the data.
WebFOCUS 8206.08 Windows, All Outputs
March 30, 2015, 09:28 AM
MartinY
You can do the following, which is one solution:
-* File cntrysummary.fex
TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=DATA,
COLUMN=N1,
DRILLMENUITEM='See Graph',FOCEXEC=graphcntry (CNTRY=N1),
DRILLMENUITEM='See Data' ,FOCEXEC=drillcntry (CNTRY=N1),
$
ENDSTYLE
END
-* File drillcntry.fex
TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ '&CNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
-* Option A
ON TABLE PCHOLD FORMAT &WFFMT.(<Excel,EXL2K>,<HTML,HTML>).Format.
-* Option B
-*ON TABLE PCHOLD FORMAT EXL2K
END
-* File drillcntry2.fex
TABLE FILE CAR
SUM CAR.BODY.DEALER_COST
BY LOWEST CAR.ORIGIN.COUNTRY
BY LOWEST CAR.COMP.CAR
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ '&CNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
-* Option A
ON TABLE PCHOLD FORMAT &WFFMT.(<Excel,EXL2K>,<HTML,HTML>).Format.
-* Option B
-*ON TABLE PCHOLD FORMAT EXL2K
END
-* File graphcntry.fex
GRAPH FILE CAR
SUM CAR.BODY.RETAIL_COST
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
WHERE CAR.ORIGIN.COUNTRY EQ '&CNTRY';
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, COLUMN=N2, FOCEXEC=drillcntry(CNTRY=CAR.ORIGIN.COUNTRY), $
TYPE=DATA, COLUMN=N3, FOCEXEC=drillcntry2(CNTRY=CAR.ORIGIN.COUNTRY), $
ENDSTYLE
END
Where you're giving option to see graph or data.
But as I already mentioned, to have a graph and the data on the same page, I suggest to create a compound document where you can put both. Many discussion and documentation on Compound.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
March 30, 2015, 11:54 AM
RobertF
I think a smoother solution is to have the main graph drill to a sub graph which drills to data if the user so desires.
WebFOCUS 8206.08 Windows, All Outputs
March 30, 2015, 01:25 PM
MartinY
You can drill down to where you want into the format you want until you pass the proper parameters : grand-parent / parent / child.
I was just showing some possibilities...
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007