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]Excel Compound Report Issue

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Excel Compound Report Issue
 Login/Join
 
Member
posted
Hello,

I'm having an issue with a compound report in EXL2K format. When the file is opened, I'm getting an 'Excel found unreadable content' error, and asks me if I want to restore the contents of the workbook. When I select yes, only the first 2 reports on the compound report show up. Up until the segment below, the code for this compound report is the same for the html and pdf exports, which both work. Here's the segment in question:

DEFINE FILE OPS
YRNAME/A20 = IF YR EQ '&PREVYR.EVAL' THEN 'Prior Year' ELSE IF YR EQ '&CURYR.EVAL' THEN 'Current Year' ELSE '% Change';
END


TABLE FILE OPS
SUM
DIR_WRT_PREM AS '1) Direct Written Premium';
OVER
AS_WRT_PREM AS '2) Assumed Written Premium';
OVER
CED_PREM AS '3) Ceded Written Premium';
OVER
POL_FEES AS '4) Policy Fees';
OVER
FINANCE_CHARGES AS '5) Finance Charges';
OVER
TOT_MTH_SALARIES AS '6) Total Salaries';
OVER
TOT_HEADCOUNT AS '7) Total Headcount';
ACROSS HIGHEST YRNAME AS ''
ON TABLE HEADING
"Operations"
""
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END



DEFINE FILE LR_PRES
YEAROUT/A40 = IF YR EQ '&PREVYR.EVAL' THEN 'Prior Year' ELSE 'Current Year';
END

TABLE FILE LR_PRES
SUM LOSS_RATIO AS ''
BY SORTSTATE AS ''
BY POL_COV_LVL_2_CD AS ''
ACROSS HIGHEST YEAROUT AS ''
COMPUTE TEST/D12.2%= ((C2 - C1)/C1) * 100; AS '% Change'
ON TABLE HEADING
""
"Claims"
""
""
"Gross Pure Incurred Loss Ratio"
""
ON TABLE SET HTMLENCODE ON
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
END



DEFINE FILE CLMTRN_PRES
YEAROUT/A40 = IF YR EQ '&PREVYR.EVAL' THEN 'Prior Year' ELSE 'Current Year';
END

TABLE FILE CLMTRN_PRES
SUM CLM_TURNAROUND AS ''
BY SORTSTATE AS ''
BY POL_COV_LVL_2_CD AS ''
ACROSS HIGHEST YEAROUT AS ''
COMPUTE TEST/D12.2%= ((C2 - C1)/C1) * 100; AS '% Change'
ON TABLE HEADING
"Average Claim Turnaround"
""
ON TABLE SET HTMLENCODE ON
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
END

After this, there are a few more reports in the same format as the second and third here, until we get to...



DEFINE FILE INCFREQ_PRES
YEAROUT/A40 = IF &PERIODFILTER EQ '&PREVYR.EVAL' THEN 'Prior Year' ELSE 'Current Year';
END

TABLE FILE INCFREQ_PRES
SUM INCUR_FREQ AS ''
BY SORTSTATE AS ''
BY POL_COV_LVL_2_CD AS ''
ACROSS HIGHEST YEAROUT AS ''
COMPUTE TEST/D12.2%= ((C2 - C1)/C1) * 100; AS '% Change'
ON TABLE HEADING
"Incurred Frequency"
""
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

Excel errors out and only shows the first 2 tables. At first I thought it was an issue with my code, but I made a simpler report using data from the car file, and got the same 'unreadable content' error...

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

Is there something wrong with my logic here? Help would be greatly appreciated. Thanks!

This message has been edited. Last edited by: <Emily McAllister>,


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Platinum Member
posted Hide Post
Hmm I ran that bottom code and it works on my system. Try taking out the NOBREAK and -RUN and see what happens.

 
TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K OPEN
END


TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END


TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
 


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Report This Post
Member
posted Hide Post
Tim, I ran the code exactly as typed above and got the same error message, and only the first 2 tables were printed. Maybe this is a server configuration issue of some kind?


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Platinum Member
posted Hide Post
That's odd that 2 of the 3 reports show up, it's almost like it doesn't understand the closing report.

What happens if you try a different excel format? EXL07, XLSX etc?


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Report This Post
Member
posted Hide Post
Already tried both. Same thing. What's really odd is that in the actual report I'm working on, there are 8 reports in this compound report, and the EXL2K CLOSE is on the 8th one. It still only shows the first 2.

And to clarify, this is in 7.7.03.

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


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Master
posted Hide Post
quote:
Originally posted by bkoehn:
Already tried both. Same thing. What's really odd is that in the actual report I'm working on, there are 8 reports in this compound report, and the EXL2K CLOSE is on the 8th one. It still only shows the first 2.

And to clarify, this is in 7.7.03.

Try taking out the middle section of the car example:

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

Do you get both sets of data with no error, or only one with error?


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Member
posted Hide Post
Squatch, the report as you have it ran without error. I added back the middle table, and decided to try again. This:

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE SUBFOOT
"End of table 1"
""
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE SUBFOOT
"End of table 2"
""
""
ON TABLE PHCOLD FORMAT EXL2K NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

Gave me only the first and last tables, ignoring the middle. The error message wasn't shown this time. I'm wondering if someone is making modifications to the environment. I'll come back to this later.


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Virtuoso
posted Hide Post
Try removing the NOBREAK.

Stacking reports in a worksheet is generally inadvisable, as they will fight each other over column widths.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
I tried removing the NOBREAK from both the first and second, the first and not the second, and the second and not the first, and there was no change in result at all.


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Expert
posted Hide Post
Which version of MS Office are you opening the file in?


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
Member
posted Hide Post
2010


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Platinum Member
posted Hide Post
Are you setting the Excel worksheet names anywhere? The only time I've run into unreadable content or corrupt was because I had an ampersand in a worksheet name. Just something to check.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
Master
posted Hide Post
bkoehn,

You have to issue the open command on every table file request. Try this:

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE SUBFOOT
"End of table 1"
""
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE SUBFOOT
"End of table 2"
""
""
ON TABLE PHCOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN
  


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
quote:
Originally posted by bkoehn:
Squatch, the report as you have it ran without error. I added back the middle table, and decided to try again. This:

TABLE FILE CAR
PRINT CAR
BY MODEL
ON TABLE SUBFOOT
"End of table 1"
""
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE SUBFOOT
"End of table 2"
""
""
ON TABLE PHCOLD FORMAT EXL2K NOBREAK
END
-RUN

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

Gave me only the first and last tables, ignoring the middle. The error message wasn't shown this time. I'm wondering if someone is making modifications to the environment. I'll come back to this later.

This line has a typo in it:

ON TABLE PHCOLD FORMAT EXL2K NOBREAK

Change "PHCOLD" to "PCHOLD".


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
quote:
You have to issue the open command on every table file request.


That would mean all of our 100-150 multi-tabbed reports would break, and, they don't.
That has never been true...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Definitely only one OPEN and one CLOSE.

This message has been edited. Last edited by: Francis Mariani,


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
Member
posted Hide Post
Embarrassingly, it was the typo Squatch caught that caused the car file example not to work. That being said, the actual report I was working on was typo free, and was still throwing that unreadable content error. I was able to figure it out, though. In the particular application I'm working on, there are a series of INCLUDES that are called at the beginning of every report that set all the defaults. One of them had a LET EXL2K=EXL07 buried in there. Changing that back to EXL2K solved that problem. Does Webfocus 7703 not support compound reports for EXL07?


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report This Post
Expert
posted Hide Post
Never Ever Use LET, unless you want to play an impractical joke on someone.

Meanwhile, read this and weep: WebFOCUS EXL07/XLSX Format Supported Features Release Roadmap - a long and winding road full of potholes.


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
Member
posted Hide Post
Yes, I'd say I was definitely trolled by whoever made that INCLUDE. Thanks for the resource, Francis!


Webfocus 8104 and 7703, Windows 8, Output formats: HTML, Excel, PDF, Active Report
 
Posts: 15 | Registered: August 31, 2015Report 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]Excel Compound Report Issue

Copyright © 1996-2020 Information Builders