Specifying Data Format in a Report

Changing the Format of Values in a Report Column

How to:

Change Format of Values in a Column

Reference:

Usage Notes for Changing Column Format

A field format is defined in the Master File. You can, however, change the format of a report column. Field formats are described in full detail in the Describing Data With TIBCO WebFOCUS® Language manual.

Syntax: How to Change Format of Values in a Column

fieldname [alignment] [/format]

where:

fieldname

Is a display field—that is, a field displayed by the PRINT, LIST, SUM, or COUNT command, a row-total, or a column-total.

alignment

Specifies the position of the column title.

/R specifies a right justified column title.

/L specifies a left justified column title.

/C specifies a centered column title.

format

Is any valid field format, preceded by a slash (/). Field formats are described in the Describing Data With TIBCO WebFOCUS® Language manual. Field formats cannot be used with a column total.

Example: Changing the Format of Values in a Column

The UNIT_PRICE field has a format of D7.2 as defined in the GGPRODS Master File. To add a floating dollar sign to the display, the field format can be redefined as follows:

TABLE FILE GGPRODS
PRINT UNIT_PRICE/D7.2M
END

The output is:

    Unit 
   Price  $58.00
  $81.00
  $76.00
  $13.00
  $17.00
  $28.00
  $26.00
  $96.00
 $125.00
 $140.00

Example: Using Multiple Format Specifications in a Column

The following request illustrates column title justification with a format specification, a BY field specification, and an AS phrase specification:

TABLE FILE CAR
PRINT MODEL/A10 STANDARD/A15/R AS 'RJUST,STANDARD' 
BY CAR/C
WHERE CAR EQ 'JAGUAR' OR 'TOYOTA'
END

The output is:

                                        RJUST
       CAR        MODEL              STANDARD
----------------  -----       ---------------
JAGUAR            V12XKE AUT  POWER STEERING 
                  XJ12L AUTO  RECLINING BUCKE
                              WHITEWALL RADIA
                              WRAP AROUND BUM
                              4 WHEEL DISC BR
TOYOTA            COROLLA 4   BODY SIDE MOLDI
                              MACPHERSON STRU

Reference: Usage Notes for Changing Column Format

Each time you reformat a column, the field is counted twice against the limit for display fields in a single report. For details, see Controlling Report Formatting.
If you create an extract file from the report—that is, a HOLD, PCHOLD, SAVE, or SAVB file—the extract file will contain fields for both the original format and the redefined format, unless HOLDLIST=PRINTONLY. Extract files are described in Saving and Reusing Your Report Output.
When the size of a word in a text field instance is greater than the format of the text field in the Master File, the word wraps to a second line, and the next word begins on the same line.
You may specify justification for display fields, BY fields, and ACROSS fields. For ACROSS fields, data values, not column titles, are justified as specified.
For display commands only, the justification parameter may be combined with a format specification. The format specification may precede or follow the justification parameter.
If a title is specified with an AS phrase or in the Master File, that title will be justified as specified in FORMAT.
When multiple ACROSS fields are requested, justification is performed on the lowest ACROSS level only. All other justification parameters for ACROSS fields are ignored.

Controlling Missing Values for a Reformatted Field

How to:

Control Missing Values in Reformatted Fields

Reference:

Usage Notes for SET COMPMISS

When a field is reformatted in a request (for example, SUM field/format), an internal COMPUTE field is created to contain the reformatted field value and display on the report output. If the original field has a missing value, that missing value can be propagated to the internal field by setting the COMPMISS parameter ON. If the missing value is not propagated to the internal field, it displays a zero (if it is numeric) or a blank (if it is alphanumeric). If the missing value is propagated to the internal field, it displays the missing data symbol on the report output.

Syntax: How to Control Missing Values in Reformatted Fields

SET COMPMISS = {ON|OFF}

where:

ON: Propagates a missing value to a reformatted field. ON is the default value.
OFF: Displays a blank or zero for a reformatted field.

Example: Controlling Missing Values in Reformatted Fields

The following procedure prints the RETURNS field from the SALES data source for store 14Z. With COMPMISS OFF, the missing values display as zeros in the column for the reformatted field value.

Note: Before trying this example, you must make sure that the SALEMISS procedure, which adds missing values to the SALES data source, has been run.

SET COMPMISS = OFF
TABLE FILE SALES
PRINT RETURNS RETURNS/D12.2 AS 'REFORMATTED,RETURNS'
BY STORE_CODE
WHERE STORE_CODE EQ '14Z' 
END

The output is:

                        REFORMATTED
STORE_CODE  RETURNS     RETURNS    
----------  -------     -----------
14Z               2            2.00
                  2            2.00
                  0             .00
                  .             .00
                  4            4.00
                  0             .00
                  3            3.00
                  4            4.00
                  .             .00
                  4            4.00

With COMPMISS ON, the column for the reformatted version of RETURNS displays the missing data symbol when a value is missing:

SET COMPMISS = ON         
TABLE FILE SALES        
PRINT RETURNS RETURNS/D12.2 AS 'REFORMATTED,RETURNS'
BY STORE_CODE              
WHERE STORE_CODE EQ '14Z'  
END

The output is:

                        REFORMATTED 
STORE_CODE  RETURNS     RETURNS     
----------  -------     ----------- 
14Z               2            2.00 
                  2            2.00 
                  0             .00 
                  .               . 
                  4            4.00 
                  0             .00 
                  3            3.00 
                  4            4.00 
                  .               . 
                  4            4.00

Reference: Usage Notes for SET COMPMISS

If you create a HOLD file with COMPMISS ON, the HOLD Master File for the reformatted field indicates MISSING = ON (as does the original field). With COMPMISS = OFF, the reformatted field does NOT have MISSING = ON in the generated Master File.
The COMPMISS parameter cannot be set in an ON TABLE command.

Using Commas vs. Decimals (Continental Decimal Notation)

The CDN parameter determines the characters used for punctuation in numbers. For information about the CDN parameter values, see the TIBCO WebFOCUS® Developing Reporting Applications manual.

Setting Characters to Represent Null and Missing Values

How to:

Set Characters to Represent a Null or Missing Value
Set the NODATA Character as a SET Command

You can alter the appearance of your report output by specifying your own string of characters that will appear when no data is available for a field.

Syntax: How to Set Characters to Represent a Null or Missing Value

To specify a string for NODATA fields, use the following syntax

ON TABLE SET NODATA character string

where:

NODATA: Indicates that a NODATA character will be set.
character string: Is the string of characters that you want to appear when no data is available for a field. The default value is a period (.).

Syntax: How to Set the NODATA Character as a SET Command

To specify a character for NODATA fields, use the following syntax

SET NODATA=character

where:

character: Is the character or characters that you want to appear when no data is available for a field. The maximum number of characters is 11. The default value is a period (.).

Example: Setting the NODATA Character in a Request

This request changes the NODATA character for missing data from a period (default) to the word NONE.

TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY LAST_NAME BY FIRST_NAME
ACROSS DEPARTMENT 
ON TABLE SET NODATA NONE
ON TABLE SET STYLE * 
TYPE=REPORT, GRID=OFF, SQUEEZE=OFF,$
ENDSTYLE
END

This request produces the following report.

		DEPARTMENT
LAST_NAME	FIRST_NAME	MIS	PRODUCTION
BANNING	JOHN	NONE	$29,700.00
BLACKWOOD	ROSEMARIE	$21,780.00	NONE
CROSS	BARBARA	$27,062.00	NONE
GREENSPAN	MARY	$9,000.00	NONE
IRVING	JOAN	NONE	$26,862.00
JONES	DIANE	$18,480.00	NONE
MCCOY	JOHN	$18,480.00	NONE
MCKNIGHT	ROGER	NONE	$16,100.00
ROMANS	ANTHONY	NONE	$21,120.00
SMITH	MARY	$13,200.00	NONE
	RICHARD	NONE	$9,500.00
STEVENS	ALFRED	NONE	$11,000.00

Using Conditional Grid Formatting in a Field

You can use conditional grid formatting in order to emphasize a particular cell or field in a report.

Example: Creating a Report Using Conditional Grid Formatting

TABLE FILE CAR
SUM SALES BY CAR
ON TABLE SET STYLE *
ON TABLE PCHOLD FORMAT PDF
TYPE=DATA, COLUMN=SALES, GRID=HEAVY, WHEN=CAR EQ 'DATSUN', $
ENDSTYLE
END

The output is: