Column Titles for FML and BY ROWS OVER Requests

How to:

By default, the FOR field in an FML request and the last BY field in a request with BY ROWS OVER has no column title. You can use the AS phrase to specify a column title for those fields.
Top of page
x
Syntax: How to Provide a Column Title for the FOR Field in an FML Request
FOR fieldname [AS 'coltitle'] value [OR value OR...] [AS 'text'] 
[LABEL label] OVER
.
.
.
[value [OR value ...]] [AS 'text'] [LABEL label]
END

where:

fieldname

Is the FOR field for the FML report.

coltitle

Is the column title for the FOR field on the report output.

value

Is the value (also known as a tag value) describing the data that is retrieved for this row of the report.

AS 'text'

Enables you to assign a name to a tag value, which replaces the tag value in the output. Enclose the text in single quotation marks.

label

Assigns a label to the row for reference in a RECAP expression. The label can be up to 66 characters and cannot have blanks or special characters. Each explicit label you assign must be unique.

Even if you assign an explicit label, the positional label (R1, R2, and so on) is retained internally.
Example: Adding a Column Title to a FOR Field

The following request against the GGSALES data source produces an FML report in which PRODUCT is the FOR field:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
FOR PRODUCT       
'Biscotti' OVER   
'Scone' OVER      
'Mug'     
END

With no AS name for the FOR field, there is no column title above the list of products:

          Dollar Sales  Unit Sales
          ------------  ----------
Biscotti       5387773      430369
Scone          4216114      333414
Mug            4522521      360570

The following request adds a column title for the PRODUCT field:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
FOR PRODUCT AS 'Product'     
'Biscotti' OVER   
'Scone' OVER      
'Mug'     
END

On the report output, there is now a column title above the list of products:

Product   Dollar Sales  Unit Sales
-------   ------------  ----------
Biscotti       5387773      430369
Scone          4216114      333414
Mug            4522521      360570
Top of page
x
Syntax: How to Provide a Column Title for a BY ROWS OVER Field
BY sortfield AS 'coltitle' ROWS value1 [AS 'text1'] OVER value2 [AS 'text2']
[... OVER valuen [ AS 'textn']]
END

where:

sortfield

Is the last BY field in the report.

coltitle

Is the column title for the BY field on the report output.

value1

Is the sort field value that is first in the sorting sequence.

AS 'text1'

Enables you to assign alternate text for the first row, which replaces the field value in the output. Enclose the text in single quotation marks.

value2

Is the sort field value that is second in the sorting sequence.

AS 'text2'

Enables you to assign alternate text for the second row, which replaces the field value in the output. Enclose the text in single quotation marks.

valuen

Is the sort field value that is last in the sorting sequence.

AS 'textn'

Enables you to assign alternate text for the last row, which replaces the field value in the output. Enclose the text in single quotation marks.

Example: Adding a Column Title to a BY ROWS OVER Sort Field on Report Output

The following request against the GGSALES data source has two BY fields, PRODUCT and CATEGORY. The CATEGORY sort field specifies the rows Coffee and Food:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
BY PRODUCT        
BY CATEGORY ROWS  
'Coffee' OVER     
'Food'            
END

With no AS name for the CATEGORY field, there is no column title above the list of categories:

Product                   Dollar Sales  Unit Sales
-------                   ------------  ----------
Biscotti          Coffee             .           .
                  Food         5387773      430369
Capuccino         Coffee       2401556      190880
                  Food               .           .
Croissant         Coffee             .           .
                  Food         7758857      630753
Espresso          Coffee       3906243      308986
                  Food               .           .
Latte             Coffee      11000388      882849
                  Food               .           .
Scone             Coffee             .           .
                  Food         4216114      333414

The following request adds a column title for the CATEGORY field:

TABLE FILE GGSALES
SUM DOLLARS UNITS 
BY PRODUCT        
BY CATEGORY AS 'Category' ROWS  
'Coffee' OVER     
'Food'            
END

On the report output, there is now a column title above the list of categories:

Product           Category  Dollar Sales  Unit Sales
-------           --------  ------------  ----------
Biscotti          Coffee               .           .
                  Food           5387773      430369
Capuccino         Coffee         2401556      190880
                  Food                 .           .
Croissant         Coffee               .           .
                  Food           7758857      630753
Espresso          Coffee         3906243      308986
                  Food                 .           .
Latte             Coffee        11000388      882849
                  Food                 .           .
Scone             Coffee               .           .
                  Food           4216114      333414
WebFOCUS