I used the SET NODATA = 0 and that worked perfectly in HTML format but not in Excel.
Any advice.
Here is the program.
SET ALL = ON SET NODATA = 0
TABLE FILE CAR SUM COMPUTE PER_CENT/F6.1 = ((RETAIL_COST -DEALER_COST) / DEALER_COST) * 100; AS ' '
BY BODYTYPE ACROSS COUNTRY
ENDThis message has been edited. Last edited by: WFLurker,
WF 8105M - Portal, Dashboard - Rcaster, Data Migrator - Windows 2012 Client Server - Dev/App Studio 8105 - Data: SQL, Oracle, Neteeza,MVS
September 18, 2008, 01:33 PM
j.gross
It appears that WF does not transmit cells to Excel (format EXCEL or EXL2K) if the value is Missing, so they remain empty, and appear blank. NODATA has no effect.
But you can force the missing cells to be populated with zero. One way is to Hold an initial result in format Focus, and use Modify or Maintain to add the missing rows; then report against that Focus hold file. Thus:
TABLE FILE CAR
SUM RETAIL_COST DEALER_COST
COMPUTE PER_CENT/F6.1 = ((RETAIL_COST -DEALER_COST) / DEALER_COST) * 100;
BY COUNTRY BY BODYTYPE
ON TABLE HOLD FORMAT FOCUS
END
EX FILLXTAB FILE=HOLD,KEY1=COUNTRY,KEY2=BODYTYPE,FIELD=RETAIL_COST
TABLE FILE HOLD
SUM PER_CENT AS ' '
BY BODYTYPE
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
..........................................................
-* fillxtab.fex
-DEFAULT &FILE=file?, &KEY1=key1?, &KEY2=key2?, &FIELD=field?
FILEDEF SAVEKEY1 DISK SAVEKEY1.FEX
FILEDEF SAVEKEY2 DISK SAVEKEY2.FEX
-RUN
TABLE FILE &FILE
BY &KEY1
ON TABLE HOLD AS HOLDKEY1
END
-RUN
-SET &MAX1=0+&LINES;
TABLE FILE &FILE
BY &KEY2
ON TABLE HOLD AS HOLDKEY2
END
-RUN
-SET &MAX2=0+&LINES;
TABLE FILE HOLDKEY1
PRINT COMPUTE I/I3=I+1; FILL1/A2=','''; &KEY1 COMPUTE FILL2/A1='''';
ON TABLE SAVE AS SAVEKEY1
END
-RUN
TABLE FILE HOLDKEY2
PRINT COMPUTE I/I3=I+1; FILL1/A2=','''; &KEY2 COMPUTE FILL2/A1='''';
ON TABLE SAVE AS SAVEKEY2
END
-RUN
MODIFY FILE &FILE
COMPUTE
I1/I3=; I2/I3=;
FIXFORM I1
COMPUTE I1 =0;
GOTO LOOP1
CASE LOOP1
COMPUTE I1=I1+1; I2=0;
IF I1 GT &MAX1 GOTO EXIT ;
COMPUTE &KEY1 = DECODE I1 (
-INCLUDE SAVEKEY1
ELSE '?');
GOTO LOOP2
ENDCASE
CASE LOOP2
COMPUTE I2=I2+1;
IF I2 GT &MAX2 GOTO LOOP1 ;
COMPUTE &KEY2 = DECODE I2 (
-INCLUDE SAVEKEY2
ELSE '?');
PERFORM PLUG
GOTO LOOP2
ENDCASE
CASE PLUG
MATCH &KEY1 &KEY2
ON MATCH GOTO ENDCASE
ON NOMATCH COMPUTE &FIELD=0;
ON NOMATCH INCLUDE
ON NOMATCH GOTO ENDCASE
ENDCASE
DATA
000
END
- Jack Gross WF through 8.1.05
September 18, 2008, 01:37 PM
Prarie
Try SET EMPTYCELLS = OFF before your NODATA
In Focus since 1993. WebFOCUS 7.7.03 Win 2003
September 18, 2008, 03:18 PM
WFLurker
quote:
SET EMPTYCELLS = OFF
Thanks that works!!
WF 8105M - Portal, Dashboard - Rcaster, Data Migrator - Windows 2012 Client Server - Dev/App Studio 8105 - Data: SQL, Oracle, Neteeza,MVS
September 18, 2008, 03:49 PM
Prarie
Great! Can you edit your post and put [solved] in the title.