Using Excel Display Formats: XLSX, EXL2K, EXL97

Example: Controlling Column Width and Wrapping in XLSX Report Output

The following example illustrates how to turn on and turn off data wrapping in a column and how to set the column width for a particular column. The UNITS in this example are set to inches (the default).

DEFINE FILE GGSALES
PROFIT/D14.3 = BUDDOLLARS-DOLLARS;
DESCRIPTION/A80 = 'Subtract Total Sales Quota from Reported Sales to calculate profit.';
END

TABLE FILE GGSALES
SUM 
DESCRIPTION AS 'DEFAULT' 
DESCRIPTION AS 'WRAP = 2'
DESCRIPTION AS 'WRAP = OFF'
DESCRIPTION AS 'SQUEEZE = 1.5' 
PROFIT
BY REGION NOPRINT
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=DESCRIPTION(2), WRAP=2,$
TYPE=REPORT, COLUMN=DESCRIPTION(3), WRAP=OFF,$
TYPE=REPORT, COLUMN=DESCRIPTION(4), SQUEEZE=1.5,$
END

where:

1. The column titled "DEFAULT" illustrates the default column width and wrapping behavior.
2. The column titled "WRAP=2" sets the column width to 2 inches with data wrapping on.
3. The column titled "WRAP=OFF" turns off data wrapping for that column.
4. The column titled "SQUEEZE=1.5" sets the column width to 1.5 inches with data wrapping off.

Since the output spans two pages, the output is shown below in two separate images.

The following output displays the different behavior for the "DEFAULT" and "WRAP=2" columns.

The following output displays the output for the "WRAP=OFF" and "SQUEEZE=1.5" columns.

Example: Preserving Leading and Internal Blanks in XLSX Report Output

The following request creates a variable called SHOWVAR that contains leading, internal, and trailing blanks.

SET SHOWBLANKS = OFF

-SET &SHOWVAR= '  AB  C  ';
DEFINE FILE CAR
SHOWFIELD/A9 = '  AB  C  ';
END

TABLE FILE CAR
ON TABLE SUBHEAD
"SHOWBLANKS OFF"
"/&SHOWVAR/"
""
HEADING
"In Heading:"
"SHOWVAR<+0>&SHOWVAR"
"SHOWFIELD<+0><SHOWFIELD"
""
"In DATA":
PRINT SHOWFIELD
BY COUNTRY 
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
HEADALIGN=BODY,SQUEEZE=ON,$
TYPE=TABHEADING,COLSPAN=2,$
END

The following outputs show the differences in XLSX generated using SET SHOWBLANKS = OFF and SET SHOWBLANKS = ON.

SET SHOWBLANKS = OFF with HEADALIGN=BODY (no leading blanks or trailing blanks)

SET SHOWBLANKS = OFF without HEADALIGN=BODY (preserved blanks and concatenated heading items)

SET SHOWBLANKS = ON with HEADALIGN=BODY (leading blanks and trailing blanks)

SET SHOWBLANKS = ON without HEADALIGN=BODY (preserved blanks and concatenated heading items)

Example: Inserting Images in Excel Headers and Footers and Defining Page Settings

The following request against the GGSALES data source places the image, ibi_logo.gif, on the left header area of the first page and the right header area of every other page of the resulting worksheet. It places the image webfocus1.gif in the center area of the footer on every page:

TABLE FILE GGSALES
SUM DOLLARS UNITS BUDDOLLARS BUDUNITS 
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD FORMAT XLSX 
ON TABLE SET STYLE *
FONT=ARIAL,SIZE=12,
XLSXPAGESETS=ON,TOPMARGIN=1,BOTTOMMARGIN=1,ORIENTATION=LANDSCAPE,
PAGESIZE=LETTER,$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=IBI_LOGO.GIF, DISPLAYON=FIRST,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=IBI_LOGO.GIF, DISPLAYON=NOT-FIRST,$
TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=webfocus1.gif, $
END

The first page of output has the image ibilogo.gif in the left area of the header and the image webfocus1.gif in the center area of the footer.

The second page of output has the image ibilogo.gif in the right area of the header and the image webfocus1.gif in the center area of the footer.

Example: Placing a Watermark in an XLSX Header

The following request against the GGSALES data source uses the image internaluseonly.gif as a watermark to display in the background of every page of the worksheet. Although the image is placed in the center area of the header, it is large enough to span the entire worksheet page. It has a transparent background, so it does not cover the logo images placed at the left in the header and the center in the footer:

TABLE FILE GGSALES
SUM DOLLARS UNITS BUDDOLLARS BUDUNITS 
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD FORMAT XLSX 
ON TABLE SET STYLE *
XLSXPAGESETS=ON,
TOPMARGIN=1,BOTTOMMARGIN=1,LEFTMARGIN=1, RIGHTMARGIN=1, ORIENTATION=LANDSCAPE,PAGESIZE=LETTER,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=IBI_LOGO.GIF, DISPLAYON=FIRST, $
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=INTERNALUSEONLY.GIF, $
TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=WEBFOCUS1.GIF, $
END

The first page of the generated worksheet shows the watermark image beneath the data. This image is displayed on every page of the worksheet.

Example: Creating a Simple BYTOC Report

The following request against the GGSALES data source creates separate tabs based on the REGION sort field.

TABLE FILE GGSALES
SUM UNITS/D12C DOLLARS/D12CM
BY REGION NOPRINT
BY CATEGORY
BY PRODUCT
HEADING
"<REGION Region Sales"
ON TABLE HOLD FORMAT XLSX BYTOC
ON TABLE SET BYDISPLAY ON
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=9,$
TYPE=HEADING, SIZE=12,$
TYPE=TITLE, BACKCOLOR=GREY, COLOR=WHITE,$
ENDSTYLE
END

The output is:

Example: Creating Overflow Worksheets

The following request creates XLSX report output with overflow worksheets. The ROWOVERFLOW=ON attribute in the StyleSheet activates the overflow feature. Without this attribute, one worksheet would have been generated instead of three.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
-* ****Worksheet Heading****
HEADING
"SALES REPORT WORKSHEET <TABPAGENO"
" "
-* ****Worksheet Footing****
FOOTING
" "
"END OF WORKSHEET <TABPAGENO"
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION
BY CATEGORY
BY PRODUCT
BY DATE

-* ****Subfoot****
ON REGION SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Subhead****
ON REGION SUBHEAD
" "
"Category <CATEGORY for Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON, ROWLIMIT=2000,$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading and column titles display on each worksheet, and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet, displaying the report heading, page heading, column titles, and first subhead.

Note that the TITLETEXT attribute in the StyleSheet specified the name EXLOVER, so the three worksheets were generated with the names EXLOVER1, EXLOVER2, and EXLOVER3. If there had been no TITLETEXT attribute, the sheets would have been named SHEET1, SHEET2, and SHEET3.

The worksheet footing displays at the bottom of each worksheet and the report footing displays at the bottom of the last worksheet. The following image shows the bottom of the last worksheet, displaying the last subfoot, the page footing, and the report footing.

Example: Creating Overflow Worksheets With FOCUS Page Breaks

The following request creates XLSX report output with overflow worksheets. The ROWOVERFLOW=PBON attribute in the StyleSheet activates the overflow feature, and the ROWLIMIT=250 sets the maximum number of rows in each worksheet to approximately 250. Without this attribute, one worksheet would have been generated. The PRODUCT sort phrase specifies a page break.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION 
BY HIGHEST CATEGORY 
BY PRODUCT PAGE-BREAK
BY DATE
WHERE DATE GE '19971001'
-* ****Page Heading****
HEADING
" Product: <PRODUCT in Category: <CATEGORY for Region: <REGION"
-* ****Page Footing****
FOOTING
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
INCLUDE=endeflt,TITLETEXT=EXLOVER, ROWOVERFLOW=PBON, ROWLIMIT=250,
$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading, footing, and column titles display on each worksheet and at each FOCUS page break (each time the product changes), and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet.

Example: Creating a Simple Compound Report Using XLSX

SET PAGE-NUM=OFF
TABLE FILE GGSALES
HEADING
"Report 1: Coffee - Budget"
" "
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Coffee Budget,$
TYPE=HEADING, SIZE=14,$
ENDSTYLE
ON TABLE HOLD AS EX1 FORMAT XLSX OPEN
END

TABLE FILE GGSALES
HEADING
"Report 2: Coffee - Actual "
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Coffee Actual,$
TYPE=HEADING, SIZE=14,$
ENDSTYLE
END

TABLE FILE GGSALES
HEADING
"Report 3: Food - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Food Budget,$
TYPE=HEADING, SIZE=14,$
ENDSTYLE
ON TABLE HOLD FORMAT XLSX CLOSE
END

The output is:

Example: Creating a Compound Report Using NOBREAK

In this example, the first two reports are on the first worksheet, and the last two reports are on the second worksheet, since NOBREAK appears on both the first and third reports.

TABLE FILE GGSALES
HEADING
"Report 1: Coffee - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Coffee
ON TABLE HOLD FORMAT XLSX OPEN NOBREAK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Coffee, FONT=ARIAL, SIZE=10, STYLE=NORMAL,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=HEADING, SIZE=12, STYLE=BOLD, COLOR=BLUE,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
END

TABLE FILE GGSALES
HEADING
" "
"Report 2: Coffee - Actual "
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Coffee
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10, STYLE=NORMAL,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
TYPE=HEADING, SIZE=12, STYLE=BOLD, COLOR=BLUE,$
END

TABLE FILE GGSALES
HEADING
"Report 3: Food - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Food
ON TABLE HOLD FORMAT XLSX NOBREAK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Food, FONT=ARIAL, SIZE=10, STYLE=NORMAL,$
TYPE=HEADING, STYLE=BOLD, SIZE=12, COLOR=BLUE,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
END

TABLE FILE GGSALES
HEADING
" "
"Report 4: Food - Actual"
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Food
ON TABLE HOLD FORMAT XLSX CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10,  $
TYPE=TITLE, STYLE=BOLD,$
TYPE=HEADING, SIZE=12, STYLE=BOLD, COLOR=BLUE,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
END

Report output is displayed in two separate tabs.

Example: Identifying Null Values in EXL2K Report Output

The following syntax utilizes the default behavior of ON TABLE SET EMPTYCELLS ON, which is set in the background:

TABLE FILE CAR
   SUM SALES BY COUNTRY ACROSS SEATS
   ON TABLE HOLD FORMAT EXL2K
END

The following output displays empty spaces in the cells of the report for each field where null values are retrieved from the database:

The following syntax utilizes the SET NODATA command:

TABLE FILE CAR
   SUM SALES BY COUNTRY ACROSS SEATS
   ON TABLE SET NODATA 'n/a'
   ON TABLE SET EMPTYCELLS OFF
   ON TABLE HOLD FORMAT EXL2K
END

Note: If you do not add SET EMPTYCELLS OFF, the SET NODATA command will be ignored.

The following output displays 'n/a' in the cells of the report for each field where null values are retrieved from the database:

The following syntax turns off the default SET EMPTYCELLS behavior and does not use SET NODATA, which makes it impossible to distinguish null values from zero quantities:

TABLE FILE CAR
   SUM SALES BY COUNTRY ACROSS SEATS
   ON TABLE SET EMPTYCELLS OFF
   ON TABLE HOLD FORMAT EXL2K
END

The following output displays zeros in the cells of the report for each field where either null values are retrieved from the database or the quantity is zero:

Example: Controlling Column Width and Wrapping in EXL2K Report Output

The following example illustrates how to turn on and turn off data wrapping in a column and how to set the column width for a particular column. The UNITS in this example are set to inches (the default).

  DEFINE FILE CAR
  MYDATE/MDY='10/22/60';
  RCD/D14.3=RETAIL_COST;
  VERYLONG/A80='Subtract dealer cost from retail cost to calculate
  profit.';
  END

   TABLE FILE CAR
   PRINT MYDATE RCD 
   VERYLONG AS 'Default' VERYLONG AS 'WRAP=OFF'
   VERYLONG AS 'WRAP=4.1' VERYLONG AS 'WRAP=2'
   VERYLONG AS 'SQUEEZE=2' SALES 
   BY COUNTRY
   ON TABLE HOLD FORMAT EXL2K 
   ON TABLE SET STYLE *
   TYPE=DATA, COLUMN=MYDATE, JUSTIFY=CENTER, $ 
1. TYPE=REPORT, COLUMN=VERYLONG(2), WRAP=OFF, $
2. TYPE=REPORT, COLUMN=VERYLONG(3), WRAP=4.1, $
3. TYPE=REPORT, COLUMN=VERYLONG(4), WRAP=2, $
4. TYPE=REPORT, COLUMN=VERYLONG(5), SQUEEZE=2, $ 
   END

where:

1. Identifies the column titled "WRAP=OFF" and turns off data wrapping for that column.
2. Identifies the column titled "WRAP=4.1" and sets the column width to 4.1 inches with data wrapping on.
3. Identifies the column titled "WRAP=2" and sets the column width to 2 inches with data wrapping on.
4. Identifies the column titled "SQUEEZE=2" and sets the column width to 2 inches with data wrapping off.

Note: The column titled "Default" illustrates the default column width and wrapping behavior.

Since the output is wider than this page, it is shown in two sections. The following output displays the "Default", "WRAP=OFF", and "WRAP=4.1" columns:

The following output displays the "WRAP=2", and "SQUEEZE=2" columns:

Example: Locking an Entire EXL2K Workbook

The following request locks the entire workbook because the StyleSheet declarations include the following declaration:

TYPE=REPORT, PROTECTED=ON, $

The request is:

TABLE FILE CAR
HEADING
"Profit By Car "
" "
SUM RETAIL_COST AND DEALER_COST AND
COMPUTE PROFIT/D12.2 = RETAIL_COST - DEALER_COST;
BY CAR
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD AS EXLFORM1 FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=REPORT, PROTECTED=ON,            $
TYPE=HEADING, STYLE=BOLD, SIZE=14, $
TYPE=TITLE, STYLE=BOLD, SIZE=11,$
ENDSTYLE
END

You cannot edit any value on the worksheet. Any attempt to do so displays a message that the sheet is protected:

Example: Locking a Single Column on an EXL2K Workbook

Example: Generating Native Excel Formulas for Column Totals

The following example illustrates the translation of a column total in a report request into an Excel formula when using format EXL2K FORMULA. Note that the formatting of the column total (TYPE=GRANDTOTAL) is retained in the Excel 2000 spreadsheet.

When you select the total in the report, the equation =SUM(B4:B7) appears in the formula bar, representing the column total as a sum of cell ranges.

TABLE FILE CENTORD
HEADING
"Projected Return By Region"
" "
SUM LINE_COGS AS 'RETURN'
BY REGION AS 'REGION'
ON TABLE COLUMN-TOTAL
ON TABLE HOLD AS EXL2K5 FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=HEADING, STYLE=BOLD, SIZE=14,$
TYPE=TITLE, STYLE=BOLD+UNDERLINE, SIZE=10,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
ENDSTYLE
END

The output is:

Example: Generating Native Excel Formulas for Row Totals

This request calculates totals for line price and quantity across regions. The row totals are represented as sums of cell ranges.

TABLE FILE CENTORD
HEADING
"Projected Line Cost Across Region"
" "
SUM LINEPRICE AND QUANTITY
ACROSS REGION AS 'Region'
BY STORENAME
WHERE REGION EQ 'EAST' OR 'NORTH'
ON REGION ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS EXL2K6 FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=HEADING, STYLE=BOLD, SIZE=14,$
TYPE=TITLE, STYLE=BOLD, SIZE=11,$
TYPE=SUBTOTAL, STYLE=BOLD, $
TYPE=GRANDTOTAL, STYLE=BOLD, SIZE=11,$
TYPE=ACROSSTITLE, STYLE=BOLD, SIZE=11, JUSTIFY=LEFT,$
TYPE=ACROSSVALUE, STYLE=BOLD, SIZE=10, JUSTIFY=CENTER,$
ENDSTYLE
END

The output highlights the formula that calculates the row total in cell G11=C11+E11:

Example: Generating Native Excel Formulas for Calculated Values

This request totals the columns for retail cost and dealer cost and calculates the value of a field called PROFIT by subtracting the dealer cost from the retail cost.

The formula for the calculated values is generated by translating the internal form of the FOCUS expression (COMPUTE PROFIT/D12.2 = RC - DC;) into an Excel formula. In this example, the formulas appear in cells B14, C14, and D14.

TABLE FILE CAR
ON TABLE SET PAGE-NUM OFF
SUM RC AND DC AND
COMPUTE PROFIT/D12.2 = RC - DC;
BY CAR
HEADING
"Profit By Car"
" "
ON TABLE COLUMN-TOTAL
ON TABLE HOLD FORMAT EXLL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=HEADING, STYLE=BOLD, SIZE=14, $
TYPE=TITLE, STYLE=BOLD, SIZE=11,$
TYPE=GRANDTOTAL, STYLE=BOLD, SIZE=11,$
ENDSTYLE
END

The following output highlights the formula that calculates for the column total of PROFIT: D14=SUM(D4:D13).

Example: Generating a Native Excel Formula for a Function

The following illustrates how functions are translated to Excel 2000/2003 reports. The function DMOD divides ACCTNUMBER by 1000 and returns the remainder to LAST3_ACCT. The Excel formula corresponds to this, =TRUNC((MOD($C3,(1000)))).

TABLE FILE EMPLOYEE
PRINT ACCTNUMBER AS 'Account Number' AND COMPUTE
LAST3_ACCT/I3L = DMOD(ACCTNUMBER, 1000, LAST3_ACCT);
BY LAST_NAME AS 'Last Name' 
BY FIRST_NAME AS 'First Name'
WHERE (ACCTNUMBER NE 000000000) AND (DEPARTMENT EQ 'MIS');
ON TABLE HOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=TITLE, SIZE=12, STYLE=BOLD, $
ENDSTYLE
END

The output is:

Example: Generating a Formula With Recomputed Values

The following request computes the difference (DIFF) by subtracting budgeted dollars from dollar sales. The budgeted dollars field used in the expression is not included in the SUM command. The value of DIFF is recomputed on the region level:

TABLE FILE GGSALES
HEADING
"Profit By Region"
" "
SUM DOLLARS 
COMPUTE DIFF/I9=DOLLARS - BUDDOLLARS;
BY REGION
BY CATEGORY
ON REGION RECOMPUTE
ON TABLE HOLD FORMAT EXL2K FORMULA
END

The output shows that the formula is subtracting a data value that is not displayed on the worksheet. It is actually the BUDDOLLARS value from the previous row:

If you change the SUM command to the following, the formula can be recomputed correctly:

SUM DOLLARS BUDDOLLARS

The formula generated with the new SUM command contains cell references for both fields used in the calculation:

Example: Using the EXL2K PIVOT Option

This simple example shows how to populate and generate PivotTables:

TABLE FILE CENTINV
HEADING
"CENTINV File PivotTable"
"Sum of Price by Product Across Category"
PRINT PRICE
BY PROD_NUM
ACROSS PRODCAT
ON TABLE COLUMN-TOTAL
  ON TABLE HOLD AS EXL2K9 FORMAT EXL2K PIVOT
  PAGEFIELDS PRODNAME
  CACHEFIELDS COST QTY_IN_STOCK
ON TABLE SET STYLE *
TYPE=HEADING, LINE=1, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$
TYPE=HEADING, LINE=2, FONT='ARIAL', COLOR=PURPLE, SIZE=12, STYLE=BOLD,$
TYPE=DATA, FONT='ARIAL', COLOR=PURPLE,$
TYPE=GRANDTOTAL, FONT='ARIAL', COLOR=PURPLE, SIZE=12, STYLE=BOLD,$
ENDSTYLE
END

The output is:

Example: Using CACHEFIELDS With EXL2K PIVOT

This example shows how to specify CACHEFIELDS to populate the PivotTable toolbar.

TABLE FILE CENTINV
HEADING
"PivotTable with CACHEFIELDS"
"Sum of Price by Product Across Category"
PRINT PRICE
BY PRODCAT BY PRODTYPE
ON PRODCAT SUB-TOTAL
  ON TABLE HOLD AS EXL2K10 FORMAT EXL2K PIVOT
  CACHEFIELDS COST PRODNAME
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=PRODCAT, COLOR=RED,$
TYPE=HEADING, COLOR=BLUE, STYLE=BOLD, SIZE=14,$
ENDSTYLE
END

The output is:

Example: Using Excel Named Ranges

This example creates one report in one worksheet of an Excel workbook. The code specific to Excel Named Ranges appears in bold in the following syntax.

TABLE FILE GGSALES
PRINT
     PRODUCT
     DATE
     UNITS
BY REGION
BY DOLLARS
ON TABLE SET PAGE-NUM OFF 
ON TABLE SET BYDISPLAY ON 
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE * 
     UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $
TYPE=REPORT, FONT='ARIAL', SIZE=9, COLOR='BLACK', BACKCOLOR='NONE',     
STYLE=NORMAL, $
TYPE=DATA, IN-RANGES='RegionalSales', $
TYPE=TITLE, STYLE=BOLD, IN-RANGES='RegionalSales', $
ENDSTYLE
END

The Excel output is:

The name assigned to this Excel Named Range is RegionalSales. If additional rows of data are added, or columns of data are inserted, the named range will stretch to contain both new and existing data.

Example: Creating a Simple TOC Report

The following request against the GGSALES data source creates separate tabs based on the REGION sort field:

TABLE FILE GGSALES
SUM UNITS/D12C DOLLARS/D12CM
BY REGION NOPRINT
BY CATEGORY
BY PRODUCT
HEADING
"<REGION Region Sales"
ON TABLE HOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
ON TABLE SET COMPOUND BYTOC
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL,SIZE=9,$
TYPE=HEADING, SIZE=12,$
TYPE=TITLE, BACKCOLOR=GREY,COLOR=WHITE, $
ENDSTYLE
END

The output is:

Example: Creating Overflow Worksheets With EXL2K Report Output

The following request creates EXL2K report output with overflow worksheets. The SET LINES command sets the maximum number of rows in each worksheet to approximately 2000, and the ROWOVERFLOW=ON attribute in the StyleSheet activates the overflow feature. Without this attribute, one worksheet would have been generated instead of three:

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
-* ****Worksheet Heading****
HEADING
"SALES REPORT WORKSHEET <TABPAGENO"
" "
-* ****Worksheet Footing****
FOOTING
" "
"END OF WORKSHEET <TABPAGENO"

PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION
BY CATEGORY
BY PRODUCT
BY DATE
-* ****Subfoot****
ON REGION SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Subhead****
ON CATEGORY SUBHEAD
" "
" Category <CATEGORY for Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET LINES 2000
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON,$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading and column titles display on each worksheet, and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet, displaying the report heading, page heading, column titles, and first subhead:

Note that the TITLETEXT attribute in the StyleSheet specified the name EXLOVER, so the three worksheets were generated with the names EXLOVER1, EXLOVER2, and EXLOVER3. If there had been no TITLETEXT attribute, the sheets would have been named SHEET1, SHEET2, and SHEET3.

The worksheet footing displays at the bottom of each worksheet and the report footing displays at the bottom of the last worksheet. The following image shows the bottom of the last worksheet, displaying the last subfoot, the page footing and the report footing:

Example: Creating Overflow Worksheets With FOCUS Page Breaks

The following request creates EXL2K report output with overflow worksheets. The ROWOVERFLOW=PBON attribute in the StyleSheet activates the overflow feature, and the ROWLIMIT=250 sets the maximum number of rows in each worksheet to approximately 250. Without this attribute, one worksheet would have been generated. The PRODUCT sort phrase specifies a page break.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION 
BY HIGHEST CATEGORY 
BY PRODUCT PAGE-BREAK
BY DATE
WHERE DATE GE '19971001'
-* ****Page Heading****
HEADING
" Product: <PRODUCT in Category: <CATEGORY for Region: <REGION"
-* ****Page Footing****
FOOTING
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
INCLUDE=endeflt,TITLETEXT=EXLOVER, ROWOVERFLOW=PBON, ROWLIMIT=250,
$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading, footing, and column titles display on each worksheet and at each FOCUS page break (each time the product changes), and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet.

Example: Creating a Simple Compound Report Using EXL2K

SET PAGE-NUM=OFF
TABLE FILE CAR
HEADING
"Sales Report"
" "
SUM SALES
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Sales Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS EX1 FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Inventory Report"
" "
SUM RC
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Inv. Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS EX1 FORMAT EXL2K
END

TABLE FILE CAR
HEADING
"Cost of Goods Sold Report"
" "
SUM DC
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Cost Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS EX1 FORMAT EXL2K CLOSE
END

The output for each tab in the Excel worksheet is:

Example: Creating a Compound Report With Pivot Tables and Formulas

SET PAGE-NUM=OFF
TABLE FILE CAR
HEADING
"Sales Report"
" "
PRINT RCOST
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Sales Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS PIV1 FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Inventory Report"
" "
PRINT SALES
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Inv. Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS PPPP FORMAT EXL2K PIVOT
PAGEFIELDS TYPE SEATS
CACHEFIELDS MODEL MPG RPM 
END

TABLE FILE CAR
SUM RCOST
BY COUNTRY BY CAR BY MODEL BY TYPE BY SEATS SUMMARIZE
ON MODEL SUB-TOTAL
ON TABLE HOLD AS XFOCB FORMAT EXL2K FORMULA
END

TABLE FILE CAR
HEADING
"Cost of Goods Sold Report"
" "
PRINT DCOST
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Cost Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS ONE FORMAT EXL2K CLOSE PIVOT
PAGEFIELDS RCOST
CACHEFIELDS MODEL TYPE SALES ACCEL SEATS
END

The output for each tab in the Excel worksheet is:

Example: Creating a Compound Report Using NOBREAK

In this example, the first two reports are on the first worksheet, and the last two reports are on the second worksheet, since NOBREAK appears on both the first and third reports.

TABLE FILE GGSALES
HEADING
"Report 1: Coffee - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Coffee
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
ON TABLE SET STYLE *
type=report, font=Arial, size = 10, style=normal, $
type=title, style=bold, $
type=heading,   size=12, style=bold, color=blue, $
type=grandtotal,  style=bold,  $
END

TABLE FILE GGSALES
HEADING
" "
"Report 2: Coffee - Actual "
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Coffee
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
type=report, font=Arial, size=10, style=normal, $
type=grandtotal,  style=bold, $
type=heading,  size=12, style=bold, color=blue, $
END

TABLE FILE GGSALES
HEADING
"Report 3: Food - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Food
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET STYLE *
type=REPORT, font=Arial, size=10, style=normal, $
type=HEADING,  style=bold, size=12, color=blue, $
type=title, style=bold, $
type=grandtotal,  style=bold,  $
END

TABLE FILE GGSALES
HEADING
" "
"Report 4: Food - Actual"
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Food
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
type=report, font=Arial, size=10,  $
type=title, style=bold, $
type=heading,size=12, style=bold,  color=blue,$
type=grandtotal, style=bold,  $
END

The output is: