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,

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?

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 has highlighted a valid point.

What is the format of the field with the issue ?

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.

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.

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.

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.