Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem creating excel report EXL2K
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Problem creating excel report EXL2K
 Login/Join
 
Member
posted
Hello all,

I am facing an issue while creating excel format report.
I have web service data source, which I am able to connect and retrieve data successfully. I have created a html report out of the fetched data, but my client need it in excel format.
But when I am getting report in same html format even after using “ ON TABLE PCHOLD FORMAT EXL2K” .
Could you please help me here and let me now if you require more details.


Thanks & Regards,
Chhavikant
WF 7.7.03
IE8

This message has been edited. Last edited by: <Kathryn Henning>,
 
Posts: 7 | Registered: July 06, 2012Reply With QuoteReport This Post
Member
posted Hide Post
hello,

can you paste your fex ?

it was on TABLE PCHOLD FORMAT HTML
you change to
ON TABLE PCHOLD FORMAT EXL2K and you still have HTML output ?
correct ?

Rgds


8007 FR, Windows 2008 R2 64b, HTML, AHTML, PDF, EXL07...
 
Posts: 27 | Location: Puteaux | Registered: August 26, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Please see the code below (with the necessary changes to post in public):

-SET &ECHO = 'ALL';
-SET &CNT1 = 0;
SET ACROSSPRT = COMPRESS

-SET PAGE=NOPAGE;
-SET HOLDFORMAT = ALPHA
TABLE FILE webserv_mas_name
PRINT column NOPRINT

WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND TIMEOUT EQ 'xx'
AND STPARAM EQ 'xx'
AND BQUEUE EQ 'xx';
END

-SET &COUNTER = &LINES;
TYPE &COUNTER


DEFINE FILE webserv_mas_name
DATA1_BLOCK/I8 = IF _DATA NE (LAST _DATA) THEN LAST DATA1_BLOCK + 1 ELSE LAST DATA1_BLOCK;
INDEX_COLUMN/I8 = IF column EQ MISSING THEN 0 ELSE LAST INDEX_COLUMN + 1;
END


TABLE FILE webserv_mas_name
PRINT
ITEM.KEY
ITEM.VALUE
column
_DATA2
DATA1_BLOCK
INDEX_COLUMN
COMPUTE CNT1/I5 = CNT1 + 1;
COMPUTE ROWCH/I3 = (CNT1 - 1) / &COUNTER; NOPRINT
COMPUTE index_data/I8 = IF ROWCH NE (LAST ROWCH) THEN 1 ELSE LAST index_data + 1;
COMPUTE project_id/A25 = 'PROJECT NUMBER ' | EDIT(ROWCH);
WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND STPARAM EQ 'xx'
AND TIMEOUT EQ 'xx'
AND BQUEUE EQ 'xx';
ON TABLE HOLD AS datafil
END

TABLE FILE datafil
PRINT
project_id
_data2
index_data
BY ROWCH
ON TABLE HOLD AS table1
END

TABLE FILE datafil
PRINT
column
index_column
BY ROWCH
ON TABLE HOLD AS table2 FORMAT FOCUS INDEX index_column
END

JOIN table1.index_data IN table1 TAG A TO MULTIPLE table2.index_column IN table2 TAG B AS J0

TABLE FILE table1
PRINT
_data2 AS 'DATA'
BY project_id AS 'PID'
ACROSS column AS 'COL LST'
ON TABLE SET PAGE OFF
ON TABLE SET PAGE NOPAGE
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET LINES 99999
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT,
BORDER-TOP=ON,
BORDER-BOTTOM=ON,
BORDER-LEFT=ON,
BORDER-RIGHT=ON,
FONT='ARIAL',
SIZE=9,
$
ENDSTYLE
END

My data source is a webservice.

Out of curiosity I tried to run the last table file request with the help of Report painter, and the result was positive. I was able to see the output in excel sheet. But when I try to run whole program it gives me output in html format.

TABLE FILE table1
PRINT
_data2 AS 'DATA'
BY project_id AS 'PID'
ACROSS column AS 'COL LST'
ON TABLE SET PAGE OFF
ON TABLE SET PAGE NOPAGE
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET LINES 99999
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT,
BORDER-TOP=ON,
BORDER-BOTTOM=ON,
BORDER-LEFT=ON,
BORDER-RIGHT=ON,
FONT='ARIAL',
SIZE=9,
$
ENDSTYLE

Do you know what I am doing wrong here..??

Thank you in advance..!!
 
Posts: 7 | Registered: July 06, 2012Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
TABLE FILE webserv_mas_name
PRINT column NOPRINT

WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND TIMEOUT EQ 'xx'
AND STPARAM EQ 'xx'
AND BQUEUE EQ 'xx';
END

Issues seems to be due to above piece of code.
Try removing it from the code(if not required) or make your report as compound excel report, then issue should be resolved.


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Reply With QuoteReport This Post
Member
posted Hide Post
quote:
TABLE FILE webserv_mas_name
PRINT column NOPRINT

WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND TIMEOUT EQ 'xx'
AND STPARAM EQ 'xx'
AND BQUEUE EQ 'xx';



Hello jvb,

Actually I am using this to count the number of fields in webservice, as the count vary every day.

Could you please give some idea on how to create compound excel report..


WebFocus 7.7.03
Windows
PDF/Excel/HTML
 
Posts: 7 | Registered: July 06, 2012Reply With QuoteReport This Post
Gold member
posted Hide Post
if you want it just to count the number of lines then hold in a hold file rather than printing in the report.Following code may help:
TABLE FILE webserv_mas_name
PRINT column NOPRINT

WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND TIMEOUT EQ 'xx'
AND STPARAM EQ 'xx'
AND BQUEUE EQ 'xx';
ON TABLE HOLD
END
-RUN


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Reply With QuoteReport This Post
Member
posted Hide Post
[SOLVED] :

This is seriously amazing….I follow your suggestion and it worked perfectly.

Thank you very much.. Smiler Smiler


WebFocus 7.7.03
Windows
PDF/Excel/HTML
 
Posts: 7 | Registered: July 06, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
The reason it didn't work was because your first table file
TABLE FILE webserv_mas_name
PRINT column NOPRINT

WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND TIMEOUT EQ 'xx'
AND STPARAM EQ 'xx'
AND BQUEUE EQ 'xx';
END 
actually gets changed to
TABLE FILE webserv_mas_name
PRINT column NOPRINT

WHERE TOKEN EQ 'xxxx'
AND TYPENAME EQ 'xxxx'
AND ACTION EQ 'xxxx'
AND TIMEOUT EQ 'xx'
AND STPARAM EQ 'xx'
AND BQUEUE EQ 'xx';
ON TABLE PCHOLD FORMAT HTML
END 

Thus causing the report to print the HTML and not run the rest of the report. You should be able to debug this pretty easily. If you don't specify ON TABLE in your requests it will revert to your default which is HTML in your case.


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem creating excel report EXL2K

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.