Identifying an Entire Column

The following illustrates how to identify an entire column, which consists of the column data and the column title, in a report. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE CENTINV
HEADING
"Excess Stock Report"
SUM QTY_IN_STOCK  
BY PRODNAME
WHERE QTY_IN_STOCK GT 10000
FOOTING CENTER
"**End of Report**"
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=REPORT, COLUMN=N1, STYLE=ITALIC,$ 
ENDSTYLE
END

The output is:

Identifying an Entire Horizontal (ACROSS) Column

The following illustrates how to identify a horizontal (ACROSS) column. When you identify and format an ACROSSCOLUMN, all data values and the column title sort. The relevant StyleSheet declarations are highlighted in the request.

TABLE FILE CENTORD
SUM LINEPRICE LINE_COGS AS 'Line Cost of,Goods Sold'
BY PLANT AS 'Plant'
ACROSS YEAR
WHERE YEAR EQ 2000 OR 2001
HEADING
"Cost Analysis"
FOOTING CENTER
"**End of Report**"
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=REPORT, ACROSSCOLUMN=N1, STYLE=ITALIC,$
TYPE=REPORT, ACROSSCOLUMN=N2, STYLE=BOLD,$ 
ENDSTYLE
END

The output is:

Identifying an Entire FML Row

The following illustrates how to identify an entire FML row, consisting of the row label and the row data. The relevant StyleSheet declarations are highlighted in the request.

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT
1010 AS 'CASH ON HAND' LABEL COH OVER
1020 AS 'DEMAND DEPOSITS' LABEL DD OVER
1030 AS 'TIME DEPOSITS' LABEL TD OVER
BAR OVER
RECAP TOTCASH = R1 + R2 + R3; AS 'TOTAL CASH' 
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=REPORT, LABEL=COH, STYLE=ITALIC, $
TYPE=REPORT, LABEL=DD, STYLE=ITALIC, $
TYPE=REPORT, LABEL=TD, STYLE=ITALIC, $ 
ENDSTYLE
END

The output is:

Identifying an Entire Total Row

The following illustrates how to identify an entire COLUMN-TOTAL row in a StyleSheet. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE SALES
SUM RETURNS DAMAGED AND ROW-TOTAL AND COLUMN-TOTAL
BY PROD_CODE
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $  
TYPE=GRANDTOTAL, STYLE=BOLD, SIZE=12, $ 
ENDSTYLE
END

The output is:

Identifying a Row Total

The following illustrates how to identify a row total. The relevant StyleSheet declaration is highlighted in the request. Note that if you want to format an instance of row-total, you can add a WHEN statement to your StyleSheet. For details, see Conditionally Formatting in a StyleSheet.

TABLE FILE SALES
SUM RETURNS DAMAGED AND ROW-TOTAL
BY PROD_CODE AS 'PRODUCT,CODE'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT PS
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=REPORT, COLUMN=ROWTOTAL, STYLE=BOLD, $ 
ENDSTYLE
END

The output is:

Identifying All Data in a Report

The following illustrates how to identify all of the data in a report. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE CENTORD
HEADING CENTER
"UNITS SOLD IN 2002 BY PLANT"
SUM QUANTITY AND ROW-TOTAL AS '2002 TOTAL'
ACROSS QUARTER
BY PLANTLNG AS 'PLANT'
WHERE YEAR EQ 2002
ON TABLE COLUMN-TOTAL AS 'TOTAL UNITS'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=DATA, STYLE=BOLD, $ 
ENDSTYLE
END

In the output, the titles and grand total are not bolded because they are not data values:

Identifying a Column of Data

The following illustrates how to identify a column of data. The relevant StyleSheet declaration is highlighted in the request.

Note that when identifying a column using Nn, NOPRINT columns are counted. Even though the Product Name field is the first column in this report, it is identified with N2 because of the NOPRINT column.

TABLE FILE CENTORD
PRINT QUANTITY LINEPRICE LINE_COGS
BY ORDER_NUM NOPRINT 
BY PRODNAME
WHERE ORDER_NUM EQ '48045'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=DATA, COLUMN=N2, STYLE=ITALIC,$ 
ENDSTYLE
END

The output is:

Identifying a Row of Horizontal Sort (ACROSS) Data

The following illustrates how to identify a row of horizontal data values. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE CENTORD
HEADING
"Units Sold"
SUM QUANTITY  
BY PRODNAME
ACROSS PLANT AS 'Manufacturing Plant'
WHERE PRODTYPE EQ 'Digital'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=HEADING, SIZE=12, $ 
TYPE=ACROSSVALUE, ACROSS=PLANT, STYLE=BOLD, $ 
ENDSTYLE
END

The output is:

Identifying Subtotals and the Grand Total

The following illustrates how to identify subtotals and the grand total in a report request. In this example, only subtotal values in the QUANTITY and LINE_COGS fields are formatted, so the COLUMN attribute is included in the StyleSheet declarations.

The grand total in this request is generated by COLUMN-TOTAL.

Since there are two SUBTOTAL commands associated with two of the three BY fields (PLANT and ORDER_NO), the BY attribute is also included in each declaration to ensure the formatting is applied to the correct value.

TABLE FILE CENTORD
SUM QUANTITY LINE_COGS AS 'Line Cost of, Goods Sold'
BY PLANT AS 'Plant'
BY ORDER_NUM AS 'Order,Num'
BY PRODNAME
ON PLANT SUBTOTAL AS 'Total:'
ON ORDER_NUM SUBTOTAL AS 'Total:'
WHERE ORDER_NUM EQ '35774' OR '48041'
WHERE PLANT EQ 'BOS'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$
TYPE=SUBTOTAL, BY=PLANT, COLUMN=LINE_COGS, STYLE=BOLD+ITALIC,  COLOR=BLUE,$
TYPE=SUBTOTAL, BY=ORDER_NUM, COLUMN=QUANTITY, STYLE=BOLD, SIZE=11,$
TYPE=GRANDTOTAL, COLUMN=QUANTITY, STYLE=ITALIC, SIZE=11,$
ENDSTYLE
END

The output is:

Identifying a Subtotal Calculation (RECAP/COMPUTE)

The following illustrates how to identify a subtotal calculation created with a RECAP or COMPUTE phrase. In this example, the subtotal calculation is generated with ON PLANT RECAP QTY/F6=QUANTITY. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE CENTORD
SUM QUANTITY LINEPRICE LINE_COGS AS 'Line Cost of, Goods Sold'
BY PLANT AS 'Plant' BY ORDER_NUM
ON PLANT RECAP QTY/F6=QUANTITY;
WHERE PLANT EQ 'BOS'
WHERE ORDER_NUM GT '60000' AND ORDER_NUM LT '70000'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=RECAP, STYLE=BOLD+ITALIC, $
ENDSTYLE
END

The output is:

Styling Multiple RECAP Statements in a Matrix

You can style multiple RECAP commands in a matrix when the RECAP statements are placed after the last ACROSS value:

TABLE FILE GGSALES
SUM UNITS
BY PRODUCT
ACROSS REGION
RECAP
TTL1/I8=C1+C2+C3+C4;
TTL2/D12.2=TTL1*1.25;
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=TTL1 (*), COLOR=BLUE, BACKCOLOR=SILVER, STYLE=BOLD, $
TYPE=DATA, COLUMN=TTL2 (*), COLOR=RED, BACKCOLOR=AQUA, STYLE=BOLD, $
ENDSTYLE
END

The output is:

Styling SUBTOTAL and GRANDTOTAL Tags

The following request against the GGSALES data source generates subtotal and grand total rows. The tags for the subtotal rows are in italics and are red. The tag for the grand total row has a drill-down link to a URL:

TABLE FILE GGSALES  
SUM UNITS/D8C DOLLARS/D12CM BUDUNIT/D8C BUDDOLLARS/D12CM
BY REGION
BY CATEGORY
ON REGION SUBTOTAL
HEADING CENTER
"Gotham Grinds Sales Report"
ON TABLE SET HTMLCSS ON
ON TABLE HOLD FORMAT PDF
ON TABLE SET DROPBLNKLINE ALL
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *  
TYPE=SUBTOTAL, OBJECT=TAG,STYLE=ITALIC,COLOR=RED,$
TYPE=GRANDTOTAL, BACKCOLOR='LIGHT GREY',$
TYPE=GRANDTOTAL, OBJECT=TAG,URL='http://www.informationbuilders.com',$
ENDSTYLE   
END

The output is:

Identifying Column Titles and Horizontal Sort (ACROSS) Values

The following illustrates how to identify vertical sort titles, horizontal sort titles, and horizontal sort values. The vertical sort titles (TYPE=TITLE) are Manufacturing Plant, Quantity Sold and Product Cost; the horizontal sort title (TYPE=ACROSSTITLE) is Year; and the horizontal sort values (TYPE=ACROSSVALUE) are 1999, 2000, and 2001. The StyleSheet declarations that identify these components are highlighted in the request.

TABLE FILE CENTORD
SUM QUANTITY AS 'Quantity,Sold' LINE_COGS/I9  AS 'Product,Cost'
BY PLANT
ACROSS YEAR
WHERE YEAR EQ '2000' OR '2001' OR '2002'
HEADING
"Plant Production Cost Analysis"
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$ 
TYPE=TITLE, STYLE=BOLD, $
TYPE=ACROSSTITLE, STYLE=BOLD, $
TYPE=ACROSSVALUE, STYLE=BOLD+ITALIC, COLOR=BLUE, $
TYPE=ACROSSVALUE, COLUMN=N4, STYLE=BOLD, COLOR=RED, $ 
ENDSTYLE
END

The output is:

Identifying Free Text in an FML Report

The following illustrates how to identify free text in an FML report. In this example, the free text are the rows, CASH ACCOUNTS and OTHER CURRENT ASSETS. The relevant StyleSheet declarations are highlighted:

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT
" --- CASH ACCOUNTS ---" LABEL CA            OVER
1010 AS 'CASH ON HAND'                       OVER
1020 AS 'DEMAND DEPOSITS'                    OVER
1030 AS 'TIME DEPOSITS'                      OVER
" "                                          OVER
" --- OTHER CURRENT ASSETS ---" LABEL OCA    OVER
1100 AS 'ACCOUNTS RECEIVABLE'                OVER
1200 AS 'INVENTORY'
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=FREETEXT, LABEL=CA, STYLE=BOLD, SIZE=12, $
TYPE=FREETEXT, LABEL=OCA, STYLE=BOLD, SIZE=12, $ 
ENDSTYLE
END

The output is:

Identifying an Individual Line in a Heading

The following example illustrates how to format individual lines in a heading. Heading line 1 (Sales Quantity Analysis) is formatted in bold, point size 11. Heading line 2 (**Confidential**) is formatted in bold and red. The relevant StyleSheet declarations are highlighted in the request.

TABLE FILE CENTORD
HEADING
"Sales Quantity Analysis"
"**Confidential**"
" "
SUM QUANTITY
ACROSS YEAR
BY PLANT
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=HEADING, LINE=1, SIZE=11, STYLE=BOLD,$
TYPE=HEADING, LINE=2, COLOR=RED, STYLE=BOLD,$ 
TYPE=HEADING, JUSTIFY=CENTER,$
ENDSTYLE
END

The output is:

Identifying a Text String in a Heading Using Spot Markers

The following illustrates how to apply different formats to text strings in a heading using spot markers. The spot markers used in this example are <+0>, since they do not add any spaces. The relevant StyleSheet declarations are highlighted in the request.

TABLE FILE CENTORD
HEADING
"Third Quarter,<+0>2002:<+0> Sales Quantity Analysis"
SUM QUANTITY  BY PLANT
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=HEADING, OBJECT=TEXT, ITEM=1, STYLE=BOLD+UNDERLINE, SIZE=12, $
TYPE=HEADING, OBJECT=TEXT, ITEM=2, COLOR=BLUE, SIZE=12,
     STYLE=BOLD+UNDERLINE, $
TYPE=HEADING, OBJECT=TEXT, ITEM=3, STYLE=ITALIC,$ 
ENDSTYLE
END

The output is:

Identifying Embedded Fields in a Heading

The following illustrates how to format an embedded field in a heading. Notice that the item number is not specified in the StyleSheet declaration since there is only one embedded field in the heading. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE CENTORD
HEADING
"Sales For <YEAR By Plant"
SUM QUANTITY  BY PLANT
WHERE YEAR EQ 2000  
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=HEADING, OBJECT=TEXT, COLOR=BLUE,$ 
TYPE=HEADING, OBJECT=FIELD, COLOR=RED, STYLE=BOLD,$ 
ENDSTYLE
END

The output is:

Inserting the Current Page Number and the Total Page Count

The following illustrates how to add the current page number and the total page count to a report. The relevant syntax is highlighted in the request.

TABLE FILE EMPLOYEE
PRINT EMP_ID AS 'Employee ID'
BY SALARY IN-GROUPS-OF 5000 AS 'Salary'
BY PCT_INC AS 'Percent,Increase'
BY DAT_INC AS 'Date of,Increase'
ON SALARY PAGE-BREAK
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=TITLE, STYLE=BOLD, SIZE=11, $
ENDSTYLE
FOOTING 
"Page <TABPAGENO of <TABLASTPAGE" 
END

The first two pages of output are:

Paginating Within a Sort Group

The following request against the GGSALES data source sorts by product, region, category, and city. It resets the pagination each time the product changes. The heading prints the current page number and the total within each product group:

TABLE FILE GGSALES                          
HEADING CENTER                               
"<PRODUCT : Page <TABPAGENO of <BYLASTPAGE "                                            
SUM UNITS                                   
BY PRODUCT NOPRINT REPAGE        
BY REGION PAGE-BREAK                        
BY CATEGORY                                 
BY CITY                                     
ON TABLE HOLD FORMAT PDF           
END                                         

The following partial output shows that the page number resets to 1 when the product changes and that the BYLASTPAGE variable displays the total number of pages for each product:

Identifying Underlines and Page Numbers

The following illustrates how to identify underlines and page numbers in a report request. The relevant StyleSheet declarations appear in boldface in the request.

Note that this report is formatted in PDF, since formatting is not supported for underlines in an HTML report.

TABLE FILE CENTORD
HEADING
"Sales By Plant"
SUM QUANTITY  
BY PLANT BY YEAR
WHERE PLANT EQ 'BOS' OR 'DAL' OR 'LA'
ON PLANT UNDER-LINE SKIP-LINE
ON TABLE HOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=HEADING, OBJECT=TEXT, COLOR=BLUE, FONT=ARIAL,$ 
TYPE=PAGENUM, STYLE=ITALIC, SIZE=8,$
TYPE=UNDERLINE, COLOR=RED,$ 
ENDSTYLE
END

The output is:

Identifying Skipped Lines

The following illustrates how to identify skipped lines in a report. The relevant StyleSheet declaration is highlighted in the request.

TABLE FILE CENTINV
HEADING
"Low Stock Report"
" "
SUM QTY_IN_STOCK 
WHERE QTY_IN_STOCK LT 5000
BY PRODNAME
ON PRODNAME SKIP-LINE
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT PDF
ON TABLE SET STYLE * 
TYPE=SKIPLINE, BACKCOLOR=SILVER, $ 
ENDSTYLE
END

The output is:

Extending an Underline to the Entire Report Column

The following request from the GGSALES data source sums dollar sales by city and by date:

DEFINE FILE GGSALES               
YEAR/YY = DATE;                   
MONTH/M = DATE;
END                               
TABLE FILE GGSALES                
SUM DOLLARS                   
BY DATE                           
BY CITY
WHERE YEAR EQ 1997                
WHERE MONTH FROM 01 TO 05
WHERE CITY EQ 'Seattle' OR 'San Francisco' OR 'Los Angeles'
ON TABLE SET PAGE NOPAGE
ON TABLE HOLD FORMAT DHTML
END

The output shows that only the column titles are underlined:

To underline entire columns, generate the output in a format that can be styled and use the EXTUNDERLINE option in the STYLE attribute for the TITLE component. For example, the following request creates DHTML output in which the column titles are in boldface and left justified, and the underline is extended to the entire report columns:

DEFINE FILE GGSALES               
YEAR/YY = DATE;                   
MONTH/M = DATE;
END                               
TABLE FILE GGSALES                
SUM DOLLARS                   
BY DATE                           
BY CITY
WHERE YEAR EQ 1997                
WHERE MONTH FROM 01 TO 05
WHERE CITY EQ 'Seattle' OR 'San Francisco' OR 'Los Angeles'
ON TABLE SET PAGE NOPAGE
ON TABLE HOLD FORMAT DHTML
ON TABLE SET STYLE *               
TYPE=TITLE, STYLE= BOLD +EXTUNDERLINE, JUSTIFY=LEFT $   
ENDSTYLE
END

The output is:

The following version of the request makes the EXTUNDERLINE and JUSTIFY=LEFT options the default for the TITLE component, then makes the Date column title bold and removes the extended underline from that column:

DEFINE FILE GGSALES               
YEAR/YY = DATE;                   
MONTH/M = DATE;
END                               
TABLE FILE GGSALES                
SUM DOLLARS AS 'Sales'                   
BY DATE                           
BY CITY
WHERE YEAR EQ 1997                
WHERE MONTH FROM 01 TO 05
WHERE CITY EQ 'Seattle' OR 'San Francisco' OR 'Los Angeles'
ON TABLE SET PAGE NOPAGE
ON TABLE HOLD FORMAT DHTML
ON TABLE SET STYLE *               
TYPE=TITLE,STYLE= EXTUNDERLINE, JUSTIFY=LEFT ,$   
TYPE=TITLE,COLUMN= DATE, STYLE= -EXTUNDERLINE +BOLD ,$   
ENDSTYLE                          
END

The output is:

Formatting a Sort Group Underline

This request uses UNDERLINE to change the default color of an underline from black to red.

TABLE FILE GGSALES
SUM UNITS DOLLARS
BY CATEGORY BY PRODUCT
HEADING
"Sales Report"
" "
ON CATEGORY UNDER-LINE
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT PDF
ON TABLE SET STYLE * 
TYPE=UNDERLINE, COLOR=RED, $ 
ENDSTYLE
END

The result is an eye-catching separation between sort group values. The online PDF report appears as:

Adding Column Underlines and Removing Column Title Underlines

This request adds underlines to the values of the column CATEGORY and removes the default underlines from the column titles in an HTML report with an internal cascading style sheet.

SET HTMLCSS = ON 
TABLE FILE MOVIES
PRINT TITLE DIRECTOR
BY CATEGORY
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=TITLE, STYLE=-UNDERLINE, $
TYPE=REPORT, COLUMN=CATEGORY, STYLE=UNDERLINE, $ 
ENDSTYLE
END

The partial report is: