I have an issue displaying 0s in Excel format

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

END

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

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

Try
SET EMPTYCELLS = OFF before your NODATA

quote:

SET EMPTYCELLS = OFF

Thanks that works!!

Great! Can you edit your post and put [solved] in the title.