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     Graph in Excel & Compound Excel reports

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Graph in Excel & Compound Excel reports
 Login/Join
 
Gold member
posted
All,
I have following doubts.
Can anyone help me resolving it?

1. Can we add graphs in Excel reports? (Example code would be of great help)

2. I would like to generate compund reports in Excel and place each report in separate sheet.Tried doing below but it has shown only the first report.

SET COMPUND = OPEN
TABLE FILE CAR
PRINT
RETAIL_COST
BY COUNTRY
HEADING
"CAR REPORT"
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE =REPORT, TITLETEXT = 'CAR REPORT', $
ENDSTYLE
END

SET COMPUND = CLOSE
TABLE FILE CAR
PRINT
SEATS
RETAIL_COST
BY COUNTRY
HEADING
"CAR & SEAT REPORT"
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE =REPORT, TITLETEXT = 'CS REPORT', $
ENDSTYLE
END

Thank you very much in advance
 
Posts: 71 | Registered: November 20, 2003Report This Post
Guru
posted Hide Post
1) I'm not sure.
2) You misspelled COMPOUND (COMPUND) if you fix that the report works.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Expert
posted Hide Post
This example from the documentation does what you require:

-* compound excel 2000 report

SET PAGE-NUM=OFF
TABLE FILE CAR
HEADING
"Sales Report"
" "
SUM SALES
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Sales Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE PCHOLD AS EX1 FORMAT EXL2K OPEN
END
TABLE FILE CAR
HEADING
"Inventory Report"
" "
SUM RC
BY COUNTRY
ON TABLE SET STYLE *
type=heading, size=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END
TABLE FILE CAR
HEADING
"Cost of Goods Sold Report"
" "
SUM DC
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Cost Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Krishkasi, what version are you in, please?
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Gold member
posted Hide Post
Thaks for all your response.
Susannah,
Am using 5.2.4.

Francis,
Tried using the same example from documentation but only got Inventory Report sheet displayed.
Am not sure.It seems should have Excel 2002 installed in my machine to get compound report.
Please confirm.

Any idea about displaying graphs the similar way doing it in PDF (holding it in GIF format and then displaying it)
 
Posts: 71 | Registered: November 20, 2003Report This Post
Gold member
posted Hide Post
Hi CurtisA,
Thank you very much for the response.
Sorry it is a typo error.
Have given COMPOUND only but it also give out only the first report in one sheet.
Second report is not shown at all.
 
Posts: 71 | Registered: November 20, 2003Report This Post
<JG>
posted
Unfortunately compound Excel documents did not become available until WebFocus 5.3.
And you are correct about the version of Excel it must be Excel 2002 (Excel XP)
because the files generated by WebFocus are Microsoft webarchive format and not a binary XLS format

To incorporate a graph into EXL2K try the fillowing example



FILEDEF G1 DISK C:\IBI\WEBFOCUS52\IBI_HTML\G1.GIF
SET GRAPHSERVURL=http://localhost/ibi_apps/IBIGraphServlet
GRAPH FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS G1 FORMAT GIF
END
DEFINE FILE CAR
IMG1/A60 WITH CAR='';
ENDIMG1/A6 WITH CAR='';
END
TABLE FILE CAR
HEADING CENTER
"" "
PRINT CAR BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END


This message has been edited. Last edited by: <Mabel>,
 
Report This Post
Gold member
posted Hide Post
Thanks a lot JG,
Now i can see an image holder (image is not shown instead it shows the image area with red colour X mark on it) placed in Excel sheet.
Am working from Dev studio connecting to a remote server.
Before working with
GRAPHSERVURL & FILEDEF, do we need to set up anything in the server side
(or) Do I need to know the correct physical path and the graphservurl from the administrator?
Please let me know what exactly i should ask to my administrator.
 
Posts: 71 | Registered: November 20, 2003Report This Post
<JG>
posted
Kasi,

sorry my post was a little mixed up



FILEDEF G1 DISK C:\IBI\apps\baseapp\G1.GIF
SET GRAPHSERVURL=http://localhost/ibi_apps/IBIGraphServlet
GRAPH FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS G1 FORMAT GIF
END
DEFINE FILE CAR
IMG1/A60 WITH CAR='';
ENDIMG1/A6 WITH CAR='';
END
TABLE FILE CAR
HEADING CENTER
"PRINT CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
localhost should be changed to the address of your WebFocus server.
The directory for the filedef should point to a directory that is accessible
to the Web server (check the drive letter, your system may not be installed on C: the easiest
way to do this is to write a focexec that simply says !DIR you will then know
the correct mapping)

This message has been edited. Last edited by: <Mabel>,
 
Report This Post
Expert
posted Hide Post
Using what has been discussed so far, here is a working example of a compound Excel Report with a graph.

The TEMPERASE command prevents the WF agent temp area from being cleaned up at the end of execution, to enable placement of the graph image.

The TEMPPATH function provides the directory of the WF agent temp area.

This is used so you don't have to hard-code the location of the image. You also don't need to use the web server alias - the physical location is used instead.


-* compound excel graph and report

-SET &ECHO=ALL;

SET PAGE-NUM=OFF
SET TEMPERASE=OFF
-RUN

-SET &TEMPDIR = TEMPPATH(80,'A80');
-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 Report"
" "
FOOTING
"<IMG1"
SUM SALES BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Sales', $
ENDSTYLE
END
-RUN

TABLE FILE CAR
HEADING
"Inventory Report"
" "
SUM RC
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Inventory', $
ENDSTYLE
END
-RUN

TABLE FILE CAR
HEADING
"Cost of Goods Sold Report"
" "
SUM DC
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Cost of Goods Sold', $
ENDSTYLE
END
-RUN
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Sorry your wrong.

For an Excel file delivered via a web Browser it requires a URL.

WebFocus does not deliver Binaries for EXL2K it delivers XHT documents which resolve the contents in the browser not on the server. So unless the webfocus edatemp directory is available it will not work.

It works on a local system but not in the internet.

Secondly SET TEMPERASE=OFF is not meant to be a production setting it is designed to aid in debugging and development.
It has the potential to leave files and metadata that could subsequently cause other users problems.
 
Report This Post
Expert
posted Hide Post
Sorry. The program I posted works, because the web server is on the same machine as the WebFOCUS server. I'm sorry I neglected to mention that.

From the IBI documentation:
quote:

Parameter: TEMPERASE
Description: Determines if the temporary files created during a WebFOCUS connection are retained after the connection is closed. Applies to HOLD files and other files that may be created during the session.
Syntax: SET TEMPERASE = {ON|OFF}
where:
ON
Erases any temporary files after the WebFOCUS connection is closed. ON is the default.
OFF
Keeps any temporary files created during a WebFOCUS connection.
No mention of the parameter causing porblems with susequent users. I've used it many times without problems.

"Sorry your wrong." should be "Sorry, you're wrong.".

!DIR will not work on a UNIX server.

Cheers.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Francis, you're correct, my English is colloquial and lazy.

However the code you posted only works when you run it through a browser on the same physical machine as the WebFocus / Web server. It does not work if the Web Server and WebFocus servers are in a LAN/WAN environment unless the server network drives are mapped to the client with the same drive letter.

Yes SET TEMPERASE is documented. However generating HOLD files that contain potentially sensitive data and then leaving them lying around is never a good idea.

in UNIX it's !ls
 
Report This Post
Gold member
posted Hide Post
JG,
Thank you very much for the clarfication. But still am facing the same problem. Image holder alone shown not the image.
But the DIR worked and found the physical path now.
It looked as follows
D:\ibi\serv52\wfs\edatemp\foldername
Have used the same in the FILEDEF too.
Given the
GRAPHSERVURL = HTTP://<SERVERIP>/ibi_apps/IBIGraphServlet
Given src attribute in img tag as follows.
http://<serverip>/approot/foldername/imagename.gif
Do not know whether both <SERVERIP> & <serverip> are same.
Will send the code to my administrator and ask him whether the webserver is mapped to access the physical path.

Another quick question?
If the report is generated multiple time then the folder is going to have only the recent image of the graph. Please advise whether am right or wrong. If my assumption is right then i should create image name dynamically to avoid over writing of images.
 
Posts: 71 | Registered: November 20, 2003Report This Post
Expert
posted Hide Post
As JG pointed out, the directory for the FILEDEF must be somewhere the web server can access.

You can only use D:\ibi\serv52\wfs\edatemp\foldername if you you use the method I demonstrated and only if the WebFOCUS server is on the same machine as the web server. As well, JG pointed out the dangers of using the method I demonstrated.

You should FILEDEF to a directory that the webserver can access.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Try using

FILEDEF imagename DISK D:\IBI\apps\foldername\imagename.gif
And
http://<serverip>/approot/foldername/imagename.gif

If that does not open the graph you should look at the gif file. Try opening it from the server using paintbrush or another graphic editor. If you cannot open it then it may be that you have a java problem.

? Can you display graphs via HTML

With regard the multiple copy issue, you can always do a delete before your filedef.

!IF EXIT D:\IBI\apps\foldername\imagename.gif (DEL D:\IBI\apps\foldername\imagename.gif)
 
Report 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     Graph in Excel &amp; Compound Excel reports

Copyright © 1996-2020 Information Builders