Qantel Product Resources

When Zero Isn't A Number007

If you output PowerShift data to Excel, you'll be happy to learn that you now have lots more control over how zeroes are output, as this report demonstrates:

   10 INPUT INV.ENT.QTY           
  100 PRINTER L61             
  200 SETBEGIN (EC);(SOP=1)         
  350 RANGE FROM '0' TO '0z'
  700 LINES 0            
  800 REPORT   
  900 USING I1AEQ.TO.I1AE 
 1000 PRINT  (SFM="@40@"); ITEM.NUMBER; (TAB); 
 1100 (SFM='General');DESCRIPTION; (TAB);QTY.ON.HAND,BZ:Y;(CR);(LF)

Set Output Program (SOP=1) tells RPG the output is going to Excel; Set Format Mask (SFM) controls how data is output:

  • (SFM="@40@") sets the Excel cell to a "Text" field so that numeric values are left justified (and leading zeroes are printed)
  • (SFM='General') resets the Excel cell to its default (e.g., a numeric value is treated as a number)

This command suppresses the printing of zero values:

  • BZ:Y replaces zero values with blanks

Sample Excel Output:

Excel Output