Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to show graphs in Excel in another tab

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to show graphs in Excel in another tab
 Login/Join
 
Gold member
posted
Hello, I have a WF report I created with graphs in it. I want it to show in Excel worksheet as a separate tab for the report and both graphs in the next tab, but I am not able to show the graphs when I run it...I'm not sure how to do that...can someone help me understand it?

The query is below...
-* File fltscorecard.fex
ENGINE SQLSYB SET DEFAULT_CONNECTION MARTEN
SQL SQLSYB
EX mastersys.dbo.WinnieFleetScoreCardWF '&beginDate', '&endDate',&runType
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
SET EMPTYREPORT=ON

TABLE FILE SQLOUT
PRINT
Fleet
FleetManagerName AS 'Fleet Manager'
FleetDescription AS 'Fleet Description'
emptyTrucks AS 'Empty Trucks'
staffedTrucks AS 'Staffed Trucks'
totalTrucks AS 'Total Trucks'
RevTruck/I11M AS 'Revenue'
Turnover
Hr11Drive AS 'Hr 11 Drive'
OnTimePct AS 'On Time %'
WeekdayVacPct AS 'Weekday Off %'
WeekendVacPct AS 'Weekend Off %'
beginDate/HMDYY AS 'Begin Date'
HEADING
"Fleet Manager Scorecard"
"&DATEtMDYY <+0> "
" "
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
TITLETEXT='Fleet Scorecard',
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N10,
SQUEEZE=0.805556,
$
TYPE=REPORT,
COLUMN=N11,
SQUEEZE=0.930556,
$
TYPE=REPORT,
COLUMN=N12,
SQUEEZE=0.930556,
$
ENDSTYLE
END
GRAPH FILE SQLOUT
SUM RevTruck
ACROSS beginDate AS ''
WHERE Fleet EQ 2;
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D ON
ON GRAPH SET VZERO ON
ON GRAPH SET GRID OFF
ON GRAPH SET VAXIS 500
ON GRAPH SET HAXIS 750
ON GRAPH PCHOLD FORMAT EXL2K OPEN NOBREAK
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setUseSeriesShapes(true);
setConnectLineMarkers(true);
setConnectScatterMarkers(true);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setSeriesType(0,1);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),2);
setLegendDisplay(true);
setTitleString("Rev Truck (Fleet 1)");
setFontSizeAbsolute(getTitle(),true);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setTemplateFile("/images/tdg/template/IBITrueColors.txt");
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
TITLETEXT='Fleet 1',
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END
GRAPH FILE SQLOUT
SUM Turnover
ACROSS beginDate AS ''
WHERE Fleet EQ 2;
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D ON
ON GRAPH SET VZERO ON
ON GRAPH SET GRID OFF
ON GRAPH SET VAXIS 500
ON GRAPH SET HAXIS 750
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setUseSeriesShapes(true);
setConnectLineMarkers(true);
setConnectScatterMarkers(true);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setSeriesType(0,1);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),2);
setLegendDisplay(true);
setTitleString("Turnover (Fleet 1)");
setFontSizeAbsolute(getTitle(),true);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setTemplateFile("/images/tdg/template/IBITrueColors.txt");
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
TITLETEXT='Fleet 1',
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END
GRAPH FILE SQLOUT
SUM Hr11Drive AS 'Hr 11 Drive'
ACROSS beginDate AS ''
WHERE Fleet EQ 2;
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D ON
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET VAXIS 500
ON GRAPH SET HAXIS 750
ON GRAPH PCHOLD FORMAT EXL2K CLOSE
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setConnectLineMarkers(false);
setConnectScatterMarkers(false);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),1);
setLegendDisplay(true);
setTitleString("Hr 11 Drive");
setFontSizeAbsolute(getTitle(),true);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setTemplateFile("/images/tdg/template/IBITrueColors.txt");
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
TITLETEXT='Hr 11 Drive',
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

Thanks!

This message has been edited. Last edited by: Kerry,


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
<JG>
posted
quote:
EXL2K OPEN NOBREAK
 
Report This Post
Gold member
posted Hide Post
Winnie,

You are going to want to try the Layout Composer. Using EXL2K OPEN NOBREAK is not going to work because format png/gif/svg etc. are not excel formats. What you will need to do is instead of doing an on graph hold, do on graph save format gif/png/svg (I personally like using gif). You are going to want to write each graph and report in its own fex and then reference them in the Layout Composer. You can write it all in the Layout Composer, I just find it cleaner to reference them. I think by default the composer is set to PDF, so you will have to make sure to change it to Excel.

This way though, you can only put one graph per sheet into excel and they will be images. If you want it to be an actual graph or have more then one graph per page, then you will need to create a template and use a data sheet to populate your graphs.

Those are the only 2 ways that I know to do what you are looking to do. Good luck.

Eric


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
Expert
posted Hide Post
Here is an antique, but still working method to do this. Personally, I wouldn't bother with the Layout Composer.

-* compound_excel6.fex - compound excel report and graph

-SET &ECHO=ALL;

SET PAGE=NOLEAD
-RUN

TABLE FILE CAR
SUM DC
BY COUNTRY
HEADING
"Cost of Goods Sold"
" "
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Cost of Goods Sold', $
ENDSTYLE
END
-RUN

-* Keep the files created in the WebFOCUS temporary working folder
SET TEMPERASE=OFF
-RUN

-* Determine the path of the WebFOCUS temporary working folder
-SET &TEMPDIR = TEMPPATH(80,'A80');

-* Set the path of the graph image file
-SET &GIFFILE = &TEMPDIR || 'G1.GIF';

GRAPH FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS G1 FORMAT GIF
END
-RUN

DEFINE FILE CAR
IMG1/A100 WITH CAR='<img src="&GIFFILE">';
END
-RUN

TABLE FILE CAR
HEADING
"Sales"
" "
"<IMG1"

ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Sales', $
ENDSTYLE
END
-RUN


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Thanks for the response on my question...i did try to use the code Francis suggested, but I'm now getting an error...i'm using a stored procedure instead of a hold file to produce my report...i'm not very familiar with temp directories as we've always produce our reports in Excel so i'm not sure what my temp path is...below is my modified code and the error i'm getting...

-* File fltscorecard1.fex
ENGINE SQLSYB SET DEFAULT_CONNECTION MARTEN
SQL SQLSYB
EX mastersys.dbo.WinnieFleetScoreCardWF '&beginDate', '&endDate',&runType
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
-SET &ECHO=ALL;

SET PAGE=NOLEAD
-RUN

TABLE FILE SQLOUT
PRINT
Fleet
FleetManagerName AS 'Fleet Manager'
FleetDescription AS 'Fleet Description'
emptyTrucks AS 'Empty Trucks'
staffedTrucks AS 'Staffed Trucks'
totalTrucks AS 'Total Trucks'
RevTruck/I11M AS 'Revenue'
Turnover
Hr11Drive AS 'Hr 11 Drive'
OnTimePct AS 'On Time %'
WeekdayVacPct AS 'Weekday Off %'
WeekendVacPct AS 'Weekend Off %'
beginDate/HMDYY AS 'Begin Date'
HEADING
"Fleet Manager Scorecard"
"&DATEtMDYY <+0> "
" "
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT,
TITLETEXT='Fleet Scorecard',
$
ENDSTYLE
END
-RUN

SET TEMPERASE=OFF
-RUN
-* Determine the path of the WebFOCUS temporary working folder
-SET &TEMPDIR = TEMPPATH(80,'A80');

-SET &GIFFILE = &TEMPDIR || 'REVTRUCK.GIF';

GRAPH FILE SQLOUT
SUM RevTruck
ACROSS beginDate AS ''
WHERE Fleet EQ 2;
ON TABLE HOLD AS REVTRUCK FORMAT GIF
END
-RUN

DEFINE FILE SQLOUT
IMG1/A100 WITH SQLOUT='<"&GIFFILE">';
END
-RUN

TABLE FILE SQLOUT
HEADING
"fleet scorecard"
" "
"
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Fleet', $
ENDSTYLE
END
-RUN


-*this is the error i get:
SET PAGE=NOLEAD
-RUN
0 NUMBER OF RECORDS IN TABLE= 365 LINES= 365
TABLE FILE SQLOUT
PRINT
Fleet
FleetManagerName AS 'Fleet Manager'
FleetDescription AS 'Fleet Description'
emptyTrucks AS 'Empty Trucks'
staffedTrucks AS 'Staffed Trucks'
totalTrucks AS 'Total Trucks'
RevTruck/I11M AS 'Revenue'
Turnover
Hr11Drive AS 'Hr 11 Drive'
OnTimePct AS 'On Time %'
WeekdayVacPct AS 'Weekday Off %'
WeekendVacPct AS 'Weekend Off %'
beginDate/HMDYY AS 'Begin Date'
HEADING
"Fleet Manager Scorecard"
"Jul 6, 2010 <+0> "
" "
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT,
TITLETEXT='Fleet Scorecard',
$
ENDSTYLE
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 365 LINES= 365
SET TEMPERASE=OFF
-RUN
-* Determine the path of the WebFOCUS temporary working folder
-SET &TEMPDIR = TEMPPATH(80,'A80');
-SET &GIFFILE = C:\ibi\srv76\wfs\edatemp\ts000008\ || 'REVTRUCK.GIF';
GRAPH FILE SQLOUT
SUM RevTruck
ACROSS beginDate AS ''
WHERE Fleet EQ 2;
ON GRAPH HOLD AS REVTRUCK FORMAT GIF
END
-RUN
0 NUMBER OF RECORDS IN GRAPH= 5 PLOT POINTS= 5
GIF FILE SAVED ...
DEFINE FILE SQLOUT
IMG1/A100 WITH SQLOUT='<"C:\ibi\srv76\wfs\edatemp\ts000008\REVTRUCK.GIF ">';
END
-RUN
0 ERROR AT OR NEAR LINE 59 IN PROCEDURE fltscorecard1.fexF
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: SQLOUT
TABLE FILE SQLOUT
HEADING
"fleet scorecard"
" "
" ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Fleet', $
ENDSTYLE
END
-RUN
0 ERROR AT OR NEAR LINE 67 IN PROCEDURE fltscorecard1
(FOC406) THE FIELDNAME IS NOT RECOGNIZED: IMG1
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
(FOC3298) ERROR FOUND IN A COMPOUND REPORT
Compound Report is TERMINATING.....


i would appreciate any input to make this work...

Regards,
Winnie


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
DEFINE FILE SQLOUT
IMG1/A100 WITH SQLOUT='<"C:\ibi\srv76\wfs\edatemp\ts000008\REVTRUCK.GIF ">';
END
The SQLOUT field used in the WITH statement must be a field in table SQLOUT, which is most likely not the case - that's why you get the error "(FOC003) THE FIELDNAME IS NOT RECOGNIZED: SQLOUT" Just leave "WITH SQLOUT" out of the DEFINE.

"i'm not sure what my temp path is" - You don't have to know the temp path, this code determines the temp path:
-SET &TEMPDIR = TEMPPATH(80,'A80');


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Hi Francis,

I haven't given up on this yet..I took the "WITH SQLOUT" out of the define, but it when I ran it again...i got the message in the end saying " (FOC1175) NO REFERENCE TO FIELD DATA"

I understand now that the temp path is determined by the code, but could the issue be also that the path that it's creating does not exist? For example, the path that it created pointed to "'';
In reality, i don't have this path on my c: drive...

thanks,
Winnie


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
Winnie, sorry about that, I should have not suggested removing the WITH clause. Please use WITH with a field that exists in your SQLOUT hold file, like RevTruck, beginDate or Fleet.

That temp path is on the server, not your PC - you shouldn't have to worry about the temp path.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
No problem, Francis...Thanks a lot for your help...I added the WITH RevTruck on my DEFINE and it worked as far as creating a separate tab for the graph...In the graph tab, however, it shows that there is a link (like an "X" but not the graph) saying "The linked image cannot be displayed. The file may have been moved, renamed, or deleted. Verify that the link points to the correct file and location."

I know you said don't worry about the temp path...This is the first time we are creating graphs on our report and I would say that once I get this to work, I would definitely appreciate what WF coould do with graphs in Excel...it's so close...

Thanks so much!
Winnie


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
Winnie, my mistake again!

This will work on a single-tier environment, but not on a multi-tier environment, because this code assumes the reporting server is the same machine as the web server.

The program saves the graph image in the temporary directory. The temporary directory and image file is then referred to in the image tag:
IMG1/A100 WITH CAR='[IMG]&GIFFILE[/IMG]';

If your reporting (WebFOCUS) server is on a different machine than the web server then the web server cannot refer to the image file.

If these servers can "talk" to each other, on the web server, you could map a drive to the reporting server or you could create an alias.

Please let us know if you are on a multi-tier environment.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
No problem, Francis. We do have a multi-tier environment and yes our reporting server and web server can "talk" to each other. In order to map a drive, can we specify any folder to the reporting server from the web server?

Thanks,
Winnie


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Gold member
posted Hide Post
Hi Francis,

I'd like to make a correction. We do have a single-tier environment. I just talked to our network admin and he is able to see the graph in the server, but why it's not displaying in my Excel file, we are not sure...

What happens is that it creates this temp folder everytime and it maps to my c: drive (e.g. C:\ibi\srv76\wfs\edatemp\ts000068\REVTRUCK.GIF), which does not exist.

Can you tell me how TEMPPATH is set just like the code you sent me? Can we set that differently to map at a particular location in our server? what happens right now is that it creates different folders and they don't get wiped out.

-* Determine the path of the WebFOCUS temporary working folder
-SET &TEMPDIR = TEMPPATH(80,'A80');

-* Set the path of the graph image file
-SET &GIFFILE = &TEMPDIR || 'REVTRUCK.GIF';

Thanks again,
Winnie


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
C:\ibi\srv76\wfs\edatemp\ts000068\ is one of several folders used by WebFOCUS to hold temporary files. There is one folder for each WebFOCUS "agent". During the execution of a program, HOLD files are physically stored here, at the end of the program the report is displayed and all HOLD files are deleted automatically.

SET TEMPERASE=OFF "Keeps any temporary files created during a WebFOCUS connection", including the graph gif file.

The temporary drive is not on your PC, but on the WebFOCUS server, unless you're using Dev Studio and running the program locally.

Here is revised code, this time the graph image file is saved in an app folder, baseapp. This should work for you. The 2 problems with this method are: if another user runs a report creating an image file with the same name, one will clobber the other; the graph image file will stay in the app folder until you manually delete it.

Give this a try and let us know if you're using Dev Studio to run the report.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Yes, I am using Dev Studio to run the report. I am actually using SET TEMPERASE=OFF in my code. I also use Report Caster to run the report, but I get an error there saying "Task error: UNRECOGNIZED COMMAND SET THE PATH OF THE GRAPH IMAGE FILE"

Here's my latest code so far...was the "SET TEMPERASE=OFF" the revised code you were referring to on your last post?

-* File fltscorecard1.fex
ENGINE SQLSYB SET DEFAULT_CONNECTION MARTEN
SQL SQLSYB
EX mastersys.dbo.WinnieFleetScoreCardWF '&beginDate', '&endDate',&runType
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
-SET &ECHO=ALL;

SET PAGE=NOLEAD
-RUN

TABLE FILE SQLOUT
PRINT
Fleet
FleetManagerName AS 'Fleet Manager'
FleetDescription AS 'Fleet Description'
emptyTrucks AS 'Empty Trucks'
staffedTrucks AS 'Staffed Trucks'
totalTrucks AS 'Total Trucks'
RevTruck/I11M AS 'Revenue'
Turnover
Hr11Drive AS 'Hr 11 Drive'
OnTimePct AS 'On Time %'
WeekdayVacPct AS 'Weekday Off %'
WeekendVacPct AS 'Weekend Off %'
beginDate/HMDYY AS 'Begin Date'
HEADING
"Fleet Manager Scorecard"
"&DATEtMDYY <+0> "
" "
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT,
TITLETEXT='Fleet Scorecard',
$
ENDSTYLE
END
-RUN

SET TEMPERASE=OFF
-RUN

-* Determine the path of the WebFOCUS temporary working folder
-SET &TEMPDIR = TEMPPATH(80,'A80');


Set the path of the graph image file
-SET &GIFFILE2 = &TEMPDIR || 'TURNOVER.GIF';

GRAPH FILE SQLOUT
SUM Turnover
ACROSS beginDate AS ''
WHERE Fleet EQ 2;
ON TABLE HOLD AS TURNOVER FORMAT GIF
END
-RUN

DEFINE FILE SQLOUT
IMG2/A1000 WITH Turnover='';
END
-RUN

TABLE FILE SQLOUT
HEADING
" "
"ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Fleet 1', $
ENDSTYLE
END
-RUN


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
Winnie, I forgot to include the sample program in my last posting!

I *think* this will work in DevStudio:

-* compound_excel.fex - compound excel report and graph

-SET &ECHO=ALL;

SET PAGE=NOLEAD
-RUN

TABLE FILE CAR
SUM DC
BY COUNTRY
HEADING
"Cost of Goods Sold"
" "
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Cost of Goods Sold', $
ENDSTYLE
END
-RUN

-* Hold the graph to an app folder
GRAPH FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS baseapp/G1 FORMAT GIF
END
-RUN

DEFINE FILE CAR
IMG1/A100 WITH CAR='<img src="c:\ibi\apps\baseapp\G1.GIF">';
END
-RUN

TABLE FILE CAR
HEADING
"Sales"
" "
"<IMG1"

ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Sales', $
ENDSTYLE
END
-RUN


Report Caster is a beast that cannot be tamed so it is quite difficult to do certain things there.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Yes, it works now!!! The only thing different I did in my code is to specify the server name in my DEFINE (e.g.
Now I just have to figure out to create multiple graphs on one sheet, but I think this should be easier...

Thank you so much for your assistance on this, Francis! I very much appreciate it!


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
Winnie,

I'll leave that "multiple graphs on one sheet with your for a while!

There is a minor error in your code, check your comments - they should always begin with -* (dash asterisk). "Set the path..." is missing the -*.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Francis,

You're correct on that comments part...it was an oversight on my part...

Thanks,


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Expert
posted Hide Post
Winnie,

I only mentioned it because the error in ReportCaster is due to this.

Cheers.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Gotcha! Thanks again!


Winnie

Webfocus 7.7.3
 
Posts: 83 | Registered: July 16, 2008Report This Post
Member
posted Hide Post
Greetings All,

I am new to webfocus. Working on putting graph in excel through webfocus. Gone through this discussion and applied the sample code provided. I am getting the excel and within that a red cross mark stating "The linked image cannot be displayed. the file may have been moved,renamed or deleted.".

Winnie - You mentioned you need to specify the server name in DEFINE to the code provided by Francis in addition. Can you please share the complete syntax that you use for this. i think that can be an issue at my end.

P.S. - I am using a multitier environment. WebFocus Developer Studio version 7707.


WebFocus 7.7, Developer Studio 7707, Windows 8,Report Caster
Output Format - Excel, PDF, HTML
 
Posts: 18 | Registered: December 28, 2016Report This Post
Platinum Member
posted Hide Post
Well Jagriti, it depends on where exactly the \baseapp is located... and on what OS... Try using &APPROOT and then a folder you know is there as in the APP PATH (baseapp usually is).


Cheers,
H.

WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 115 | Location: Brighton UK | Registered: February 19, 2005Report This Post
Member
posted Hide Post
Thanks for your response Hank!

The full path where the image file has been created is D:\ibi\apps\baseapp\G1.GIF on server (I can see the image file created). I have hardcoded it in my code as follows (reference part of code provided):

GRAPH FILE STOCK
-* Created by Advanced Graph Assistant
SUM GEN_STOCK AS 'GEN Stock (ton)'
OTHERS AS 'Other (ton)'

ACROSS DISPLAY_TIME AS ''


-*ON GRAPH PCHOLD FORMAT PNG
-*ON GRAPH SET HTMLENCODE ON
-*ON GRAPH SET GRAPHDEFAULT OFF
-*ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 800
ON GRAPH SET VAXIS 500
-*ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBRSTK1
-*ON GRAPH SET GRMERGE ADVANCED
-*ON GRAPH SET GRMULTIGRAPH 0
-*ON GRAPH SET GRLEGEND 0
-*ON GRAPH SET GRXAXIS 2
ON GRAPH SET GRAPHSTYLE *
-*setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
-*setReportParsingErrors(false);
-*setSelectionEnableMove(false);
-*setDepthRadius(5);
-*setDataTextDisplay (true);
-*setFillMissingData(0);
-*setNumMissingDataSegments(1);
-*setDisplay(getDataText(),true);
-*setDataTextPosition(4);
setStackedDataValueSum(false);
-*setDataTextPosition(4);
-*setConnectLineMarkers(false);
-*setConnectScatterMarkers(false);
-*setO1LabelDisplay(true);
-*setO1AxisSide(0);
-*setO1MajorGridDisplay(false);
-*setO1MajorGridStyle(0);
-*setO1MinorGridDisplay(false);
-*setAxisAssignment(0,0);
-*setY1LabelDisplay(true);
-*setY1AxisSide(0);
-*setY1MajorGridDisplay(false);
-*setY1MajorGridStyle(0);
-*setY1MinorGridDisplay(false);
-*setTextFormatPreset(getY1Label(),-1);
-*setTextFormatPattern(getY1Label(),"#.##");
-*setRiserBorderMode(1);
-*setSeriesDefaultTransparentBorderColor(true);
-*setUseSeriesBorderDefaults(true);
-*setLegendMarkerPosition(4);
-*setFontSizeAbsolute(getLegendText(),true);
-*setFontSize(getLegendText(),9);
-*setFontStyle(getLegendText(),2);
-*setLegendDisplay(true);
-*
-*setTextJustHoriz(getO1Title(),1);
-*setFontSizeAbsolute(getO1Title(),true);
-*setFontSize(getO1Title(),10);
-*setTextRotation(getO1Title(),0);
-*setTextWrap(getO1Title(),false);
-*setTextJustHoriz(getO1Label(),0);
-*setFontSizeAbsolute(getO1Label(),true);
-*setFontSize(getO1Label(),10);
-*setTextRotation(getO1Label(),3);
-*setTextWrap(getO1Label(),false);
-*setPlaceResize(getO1Title(),0);
-*setPlaceRotate(getO1Title(),0);
-*setPlaceAlign(getO1Title(),0);
-*setPlaceWordWrap(getO1Title(),0);
-*setPlaceResize(getO1Label(),0);
-*setPlaceRotate(getO1Label(),0);
-*setPlaceAlign(getO1Label(),0);
-*setPlaceWordWrap(getO1Label(),0);
setPlace(true);
setTextString(getTitle(),"Stock (ton) - Overview");
-*
-*setFontSizeAbsolute(getTitle(),true);

ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA,
ACROSSCOLUMN=N1,
SIZE=6,
COLOR=RGB(51 102 255),
$
TYPE=DATA,
ACROSSCOLUMN=N2,
SIZE=6,
COLOR='RED',
$
TYPE=DATA,
ACROSSCOLUMN=N3,
SIZE=6,
COLOR='YELLOW',
$
TYPE=SUBHEAD,
SIZE=6,
STYLE=BOLD,
JUSTIFY=CENTER,


$
ENDSTYLE
ON TABLE HOLD AS baseapp/G1 FORMAT GIF
END
-RUN

DEFINE FILE STOCK
IMG1/A100 WITH GEN_STOCK=''< img src="D:\ibi\apps\baseapp\G1.GIF">';
END
-RUN

TABLE FILE STOCK
HEADING
"DISPLAY"
" "
"
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Stock', $
ENDSTYLE
END
--------------------------------------------------

The webfocus server resides on Windows OS (Windows Server 2008 R2 Standard, Service Pack1).

Thanks and Regards,
Jagriti

This message has been edited. Last edited by: Jagriti Kumari,


WebFocus 7.7, Developer Studio 7707, Windows 8,Report Caster
Output Format - Excel, PDF, HTML
 
Posts: 18 | Registered: December 28, 2016Report This Post
Member
posted Hide Post
Hello All,

Can you please help me on this. Its not resolved yet for me. I am using a multi tier environment and writing the script in client machine. Trying to get the image created in baseapp folder in the excel, but no success yet. The image file is created in baseapp folder. the path is D:\ibi\apps\baseapp\G1.GIF.

But this is the server location. I can see the file G1 being created from my client terminal as well. Please provide me the full syntax to reference the image.
P.S. The code is given in my previous post.
Many thanks,
Jagriti


WebFocus 7.7, Developer Studio 7707, Windows 8,Report Caster
Output Format - Excel, PDF, HTML
 
Posts: 18 | Registered: December 28, 2016Report This Post
Platinum Member
posted Hide Post
Hi Jagriti,
quote:
DEFINE FILE STOCK
IMG1/A100 WITH GEN_STOCK=''< img src="D:\ibi\apps\baseapp\G1.GIF">';
END


Try this to read the file

 DEFINE FILE STOCK
IMG1/A100 WITH GEN_STOCK='<img src="/approot/baseapp/g1.gif">';
END 

This message has been edited. Last edited by: Chaudhary,


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Member
posted Hide Post
quote:
Originally posted by Chaudhary:
Hi Jagriti,
quote:
DEFINE FILE STOCK
IMG1/A100 WITH GEN_STOCK=''< img src="D:\ibi\apps\baseapp\G1.GIF">';
END


Try this to read the file

 DEFINE FILE STOCK
IMG1/A100 WITH GEN_STOCK='[IMG]/approot/baseapp/g1.gif[/IMG]';
END 


--------------------------------------------
Thank you for your response Chaudhary!

I tried this out. I am getting a red cross mark in the excel stating "The linked image cannot be displayed. the file may have been moved, renamed, or deleted. Verify that the link points to the correct file and location."

Through an R&D on the excel red cross image, i noticed it is referencing to the location, "C:\approot\baseapp\g1.gif". I can still see the image being created in the baseapp folder correctly, from my machine (client terminal).

Thanks in advance for your help and time.
Regards,
Jagriti.


WebFocus 7.7, Developer Studio 7707, Windows 8,Report Caster
Output Format - Excel, PDF, HTML
 
Posts: 18 | Registered: December 28, 2016Report This Post
Platinum Member
posted Hide Post
quote:
ON TABLE HOLD AS baseapp/G1 FORMAT GIF


Jagriti if the requirement is to display tabular report in one tab and chart in one tab of excel try Excel compound report:-

 

-* Default Mode: ResourceLayout
SET HTMLARCHIVE=ON
*-HOLD_SOURCE
COMPOUND LAYOUT PCHOLD FORMAT XLSX
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='prop_with_names^Margins_Left=0.5^Margins_Top=0.5^Margins_Right=0.5^Margins_Bottom=0.5^thumbnailscale=4', MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, SHOW_GLOBALFILTER=OFF, $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(0.708 0.625), DIMENSION=(6.563 3.542), BYTOC=0,  ARREPORTSIZE=DIMENSION, METADATA='left: 0.708in; top: 0.625in; width: 6.563in; height: 3.542in; position: absolute; z-index: 1;', $
COMPONENT='chart1', TEXT='chart1', TOC-LEVEL=2, POSITION=(0.708 4.896), DIMENSION=(6.667 3.438), COMPONENT-TYPE=GRAPH,  ARREPORTSIZE=DIMENSION, METADATA='left: 0.708in; top: 4.896in; width: 6.667in; height: 3.438in; position: absolute; z-index: 2;', $
END
SET COMPONENT='report1'
-*component_type report
TABLE FILE CAR
SUM
     CAR.BODY.DEALER_COST
     CAR.SPECS.FUEL_CAP
BY  CAR.CARREC.MODEL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
SET COMPONENT='chart1'
-*component_type chart
-*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';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
GRAPH FILE car
-* Created by Info Assist for Graph
SUM CAR.SPECS.BHP
CAR.SPECS.RPM
BY CAR.COMP.CAR
ON GRAPH PCHOLD FORMAT HTML
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 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET LOOKGRAPH VBAR
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

-*IA_GRAPH_FINISH
COMPOUND END

 


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Platinum Member
posted Hide Post
Try , It works for me


Use absolute URL(Server: port/image path ) ,
 DEFINE FILE CAR
IMG1/A100= '<img src="http://localhost:8080/approot/baseapp/g1.gif">'
END
TABLE FILE CAR
SUM
SALES
BY COUNTRY
HEADING
"<IMG1"
ON TABLE PCHOLD FORMAT EXL2K
END 


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Member
posted Hide Post
quote:
Originally posted by Chaudhary:
Try , It works for me


Use absolute URL(Server: port/image path ) ,
 DEFINE FILE CAR
IMG1/A100= '[IMG]http://localhost:8080/approot/baseapp/g1.gif[/IMG]'
END
TABLE FILE CAR
SUM
SALES
BY COUNTRY
HEADING
"<IMG1"
ON TABLE PCHOLD FORMAT EXL2K
END 

-----------------------------------------------

Chaudhary,
Unfortunately this does not work for me. i had tried this syntax earlier as well. Frowner
For us we set port as 80, instead of 8080 for the environment, so I tried both localhost:8080 and localhost:80, but no success.
I am struggling to get this done.Any suggestion further? Can it be any configuration or compatibility related issue?

Let me know if you require any piece of info.

Regards,
Jagriti


WebFocus 7.7, Developer Studio 7707, Windows 8,Report Caster
Output Format - Excel, PDF, HTML
 
Posts: 18 | Registered: December 28, 2016Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to show graphs in Excel in another tab

Copyright © 1996-2020 Information Builders