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     [CLOSED] Crazy Excel formating

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Crazy Excel formating
 Login/Join
 
Member
posted
Something interesting has suddenly taken place. Without changing a single thing (that I know of) a report, when run with EXL2K as the output format, has started randomly displaying -$9,998,998 as a value for $0 (in the html report, the output is MISSING, or '.' if that matters). When run in any other format (HTML,COMT,AHTML) the value is $0 but not Excel or any other Excel format (EXCEL,EXL07). I thought it might be an issue with the data, but now reports run for date ranges that previously worked are displaying the incorrect value. It does not matter the version of Excel or the Operating System. We have restarted the reporting server and all related WebFOCUS hardware to no avail.

Has anyone seen this before, or know what in the world to do?

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


WebFOCUS 7.7.2: Windows 7: all output (Excel, HTML, PDF)
 
Posts: 18 | Registered: June 22, 2010Report This Post
Platinum Member
posted Hide Post
Does it only show $9,998,998 for the value for $0 only for this report? Have you tried running a different report with value $0 and see if it does the same thing.

Can you please show the code?


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Platinum Member
posted Hide Post
It sounds like some kind of overflow problem. I have only seen it show up as large negative on an IBM Mainframe. Number with seem far apart to a human, can be very close together in a computers internal storage. With some number formats there is fuzz. (This is why Windows File time stamps are only accurate to two seconds) This fuzz can make things "jump" around.

You might experiment with using DEFINE to change the number format to something really large say P32


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Report This Post
Expert
posted Hide Post
Jim has highlighted a valid point.

What is the format of the field with the issue ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
<JG>
posted
generate the report and then save it in .mht format.

Open the file in a text editor to see what has been physically delivered by WebFOCUS.

If the value is -$9,998,998 or -9998998 with display options then it's probably a WF issue.
if it's not then it's probably a Microsoft issue.
 
Report This Post
Member
posted Hide Post
Thanks for the feedback guys. You brought up some things that got me thinking a little.
Here is what I figured out: I was using TABLE FILE SQLOUT using the SQLMSS Engine, and was running a query that was doing some group by counts and was occasionally returning NULL. The format of my field in the fex was D12.2CM. In all other output formats, the NULL returned value was being represented in WebFOCUS as MISSING (.), but Excel seemed to be having a hard time with it. I was incorrect earlier when I stated that the value returned was 0, sorry. As expected, when I changed my query to display "0" instead of returning "NULL", everything worked correctly in Excel.
Similarly, I also changed the format of the field (without substituting "0" for "NULL" through the SQL query) to P12.2CM, and Excel had no problem displaying an empty cell in the output.

So, while I don't know why D12.2CM (D12.2 had the same problem) has a hard time with "MISSING" in Excel, and P12.2CM doesn't, I have figured out two fixes:

1. Pass 0 instead of NULL in SQL
2. Use Packed format instead of Decimal Format

Does anyone know why Packed might work while Decimal doesn't?

Thanks everyone.


WebFOCUS 7.7.2: Windows 7: all output (Excel, HTML, PDF)
 
Posts: 18 | Registered: June 22, 2010Report This Post
Virtuoso
posted Hide Post
quote:
-$9,998,998


That's sounds like the value stored internally to represent Missing. At least, so I recollect from when the MISSING attribute was first announced. For whatever reason, the internal value is creeping out, ...

repro:

TABLE FILE CAR
PRINT SEATS AND COMPUTE
DSEATS/D12.2CM MISSING ON = IF SEATS EQ 4 THEN MISSING ELSE SEATS;
PSEATS/P12.2CM MISSING ON = IF SEATS EQ 4 THEN MISSING ELSE SEATS;
BY COUNTRY SUB-TOTAL
BY CAR
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END


I find it remarkable that the cells containing 'normal' values are filled with a formula that tests for SEATS = 4, whereas the cells that map to Missing are set to a constant (empty or that absurd number). If you think about it, that itself is a no-no: the handling of cells in a column should be consistent over all the data (i.e., non-total) rows of the table - either all the cells in the column get a consistent formula, or [if WF cannot translate the Compute to an Excel formula] all get a constant.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
I raised this in February for a customer. It was missing values being displayed with internal values.

My recollection was that a border caused the issue, however unlikely that may sound.

I think the case was Case 70282017 Missing values and border in exl2k.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report 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     [CLOSED] Crazy Excel formating

Copyright © 1996-2020 Information Builders