Focal Point
[CLOSED] ROW-TOTAL, MISSING VALUES, NODATA AND EXL07 FORMULA

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9447083396

May 11, 2020, 12:21 PM
John_Edwards
[CLOSED] ROW-TOTAL, MISSING VALUES, NODATA AND EXL07 FORMULA
So I have a report that outputs to EXL07 FORMULA that has an ACROSS in it. I'd like the ROW-TOTAL option to function correctly but it does not. It throws a #VALUE! error whenever there's a missing value in the row, even when I set a NODATA.

Here's the rub -- it works fine when the sheet first opens. I only throws the error when you save the output or click the Allow Editing option in Excel.

Here's the question -- Has anyone bumped into this and found a solution? It more or less works correctly in EXL2K, but it's ugly and doesn't respect the NODATA option in the display. This appears to be a legit bug in the EXL07 FORMULA output option, and I'd like to know if anyone has seen a workaround. I can't find it in the forums.

This message has been edited. Last edited by: FP Mod Chuck,



May 11, 2020, 02:47 PM
Hallway
You need to set NODATA=''

From the docs: NODATA With Formulas
quote:
The null value (NODATA='') is supported for calculations. When cells containing the default NODATA symbol (.) are used in a formula, they will cause a formula error.


  
SET NODATA = ''

TABLE FILE CAR
SUM SALES/I11C AS ''
BY CAR
ACROSS COUNTRY AS ''
ON TABLE ROW-TOTAL
ON TABLE PCHOLD FORMAT EXL07 FORMULA
ON TABLE SET PAGE-NUM NOPAGE
END
-RUN

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
May 11, 2020, 03:19 PM
John_Edwards
That works! My clients want zeros in the empty fields, and that fails. It appears they will need to make a choice.

Thanks!



May 11, 2020, 03:44 PM
Hallway
It really doesn't make much sense why the NODATA is inserted as text, since most null value fields are in COMPUTES.

IMO there should be the option to enter a zero and have it be an actual 0. Maybe something like this:

  
-* INSERT A ZERO AS AN INTEGER
SET NODATA = 0

-* INSERT A ZERO AS TEXT
SET NODATA = '0'

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
May 11, 2020, 03:53 PM
John_Edwards
I tried both of those and both worked in the cells, but neither worked in the row-total formula.