With Excel 2007, Microsoft introduced enhanced worksheet functionality in a new workbook file format. The XLSX format supports most StyleSheet attributes, allowing for full report formatting. The computer on which the report is being displayed must have Microsoft Excel 2007 or higher installed.

The FOCUS procedure generates a new workbook containing a single worksheet with the report output containing your defined report elements (headings and subtotals), as well as StyleSheet syntax:

• XLSX format accurately displays formatted numeric, character and date formats.
• FORMAT XLSX FORMULA enables you to convert summed information (such as column totals, row totals, and subtotals) and calculated values into Excel formulas that will automatically update as you edit the Excel worksheet.
• XLSX workbooks can contain embedded images in fixed positions in each of the FOCUS report areas including headers, footers, and data cells.
• Within each generated worksheet, the columns in the report are automatically sized to fit the largest value in the column. FOCUS calculates the width of each data column based on the font and size requirement of all cells in that column using font metrics developed for other styled formats, including PDF and DHTML. Calculations are based on the data and title elements of the report. Heading and footing elements are not used in the sizing calculation and will be sized based on the data column requirements.
• Tab names within the workbook can be customized to provide better descriptions of the worksheet content using the TITLETEXT StyleSheet attribute
• By default, leading, internal, and trailing blanks are compressed in cells within the worksheet.
• Font definitions for format XLSX in the fontuser.xml file can be defined separately from other output formats, allowing ease in customizing the look and feel of your XLSX workbooks. FOCUS uses Arial as the default font. Use this feature to change the default font to match the Microsoft Office standard font, Calibri, or your corporate standard.

SET EXCELSERVURL = http://servername:8080/ibi_apps

DYNAM ALLOC DD HOLD DA USER1.HOLD.XLSX SHR REU      
SET EXCELSERVURL = http://myserver:8080/ibi_apps
TABLE FILE GGSALES                                   
SUM DOLLARS UNITS                                    
BY CATEGORY                                          
ON TABLE COLUMN-TOTAL                                
ON TABLE HOLD FORMAT XLSX FORMULA                    
END                                                  

The PPTX file format can contain reports, graphs, and images with FOCUS styling features, as well as populate PowerPoint templates containing preset Slide Masters, styling, and other business content, as shown in the following image.

With PowerPoint (PPTX), Microsoft® introduced enhanced functionality in a new presentation file format.

The PPTX format generates fully styled reports in binary display format, which uses the same binary technology that is used for XLSX. When PPTX is specified as the output format, a PowerPoint presentation with a single slide that includes the report is created.

The FOCUS procedure generates a new presentation containing your defined report elements, such as headings and subtotals, as well as StyleSheet syntax, such as conditional styling and drill downs.

Additionally, you can add multiple graphs and images to a PowerPoint presentation. The PowerPoint output format can contain a variety of graphs positioned anywhere on a slide to create a visual layout.

ON TABLE {HOLD|SAVE} [AS name] FORMAT PPTX 
[TEMPLATE {'template.potx'|'template.potm'} SLIDENUMBER n]

TABLE FILE GGSALES
HEADING
" "
" "
" "
" "
" "
SUM DOLLARS UNITS CATEGORY 
BY  REGION
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT PPTX TEMPLATE 'mytemplate.potx' SLIDENUMBER 3
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10,$
TYPE=HEADING, image=gglogo.gif, POSITION=(0.000000 0.000000),$
ENDSTYLE
END

You can selectively embed a subset of Unicode (Arial Unicode and MS Lucida Sans Unicode) or Non-Unicode (Tahoma, Times New Roman, Trebuchet MS, and Courier New) TrueType fonts into a PDF output file. The generated PDF file will contain only the characters required to render the document.

Superscript characters are supported as a text style in text objects using HTML markup tags. The superscript markup tag is now supported in data columns, headings, and footings in HTML, PDF, and PS output formats. Superscript values can be defined within the data, added to virtual fields, or added to text strings displayed in headings and footings.

In order to activate the translation of the HTML markup tags, in the StyleSheet set MARKUP=ON for any report component that will display superscripts. Without this attribute, the markup tags will be treated as text, not tags.

DEFINE FILE GGSALES
SUP1/A12= '<SUP>1</SUP>';
SUP2/A15= '<SUP>2</SUP>';
SUPCOPY/A20= '<SUP>'||HEXBYT(169,'A2')||'</SUP>';
END
TABLE FILE GGSALES
SUM
COMPUTE PROFIT/D12CM=DOLLARS-BUDDOLLARS; NOPRINT
COMPUTE SHOWCAT/A100=IF PROFIT LE -50000 THEN CATEGORY || SUP1
       ELSE IF PROFIT GT 50000 THEN CATEGORY || SUP2
           ELSE CATEGORY; AS Category
BUDDOLLARS/D12CM
DOLLARS/D12CM
BY REGION 
BY CATEGORY NOPRINT
HEADING
"Analysis of Budgeted and Actual Sales"
FOOTING
""
"<SUP1 Dollar sales $50,000 less than budgeted amount."
"<SUP2 Dollar sales $50,000 greater than budgeted amount."
""
"Copyright<SUPCOPY 2012, by Information Builders, Inc " 
ON TABLE SET HTMLCSS ON
ON TABLE SET SQUEEZE ON
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT PDF
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,$
TYPE=DATA,MARKUP=ON,$
TYPE=DATA,COLUMN=N5, COLOR=RED, WHEN=PROFIT LT -50000,$
TYPE=DATA,COLUMN=N6, COLOR=GREEN, WHEN=PROFIT GT 50000,$
TYPE=HEADING, JUSTIFY=LEFT,$
TYPE=FOOTING, MARKUP=ON, JUSTIFY=LEFT,$
TYPE=FOOTING, LINE=2,JUSTIFY=LEFT, COLOR=RED,$
TYPE=FOOTING, LINE=3,JUSTIFY=LEFT, COLOR=GREEN,$
END

The following enhancements have been added to the ROWOVERFLOW feature that overcomes the Excel row limit for a Worksheet:

• ROWOVERFLOW is supported with XLSX report output.
• The new ROWLIMIT StyleSheet attribute sets the maximum number of rows before the report overflows to a new Worksheet. The maximum number of rows per Worksheet that you set will be an approximation of the number of lines per page. The actual number of lines is also dependent on the specific report components used in the request. This attribute replaces the SET LINES command.
• The ROWOVERFLOW=PBON attribute includes internal FOCUS page breaks in EXL2K and XLSX report output.

TYPE=REPORT, ROWOVERFLOW={ON|OFF|PBON}, [ROWLIMIT={n|MAX}...

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 SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON, ROWLIMIT=2000,$
ENDSTYLE
END

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

EXL2K FORMULA output supports prefix operators that are used on summary lines generated by FOCUS commands, such as SUBTOTAL and RECOMPUTE. Where a corresponding formula exists in Excel, these prefix operators are translated into the equivalent Excel summarization formula. The results of prefix operators used directly against retrieved data continue to be passed to Excel as values, not formulas.

The following table identifies the prefix operators supported by EXL2K FORMULA when used on summary lines, and the Excel formula equivalent placed in the generated worksheet.

In the following request against the GGSALES data source, the RECOMPUTE command for the REGION sort field calculates the maximum of the aggregated DOLLARS field and the minimum of the aggregated BUDDOLLARS field:

TABLE FILE GGSALES
SUM UNITS DOLLARS BUDDOLLARS
AND COMPUTE DIFF/I10= DOLLARS-BUDDOLLARS;
BY REGION
BY CATEGORY
WHERE CATEGORY EQ 'Food' OR 'Coffee'
WHERE REGION EQ 'West' OR 'Midwest'
ON REGION RECOMPUTE MAX. DOLLARS MIN. BUDDOLLARS DIFF
ON TABLE HOLD FORMAT EXL2K FORMULA
END

On the output, the cell that represents the recomputed DOLLARS for the Midwest region has been generated as the formula =MIN(E2:E3).

The tag is the text that is displayed in the leftmost portion of each SUBTOTAL and GRANDTOTAL row in a report. The tag is used to identify the type of data represented within this row. The text used to generate this tag can be customized by adding an AS name to the SUBTOTAL syntax.

You can define styling for the subtotal and grand total tag separately from the rest of the row. Text attributes available for the tag, including font, color, size, and style, can be used to differentiate and highlight the tags. Additionally, styling can be applied that turns tags into drill-down links.

Styling is supported for text attributes only. Cell or column features such as borders, background color, or justification are not supported.

This feature is available for PDF, DHTML, PS, HTML, AHTML, XLSX, and EXL2K formats.

TYPE={SUBTOTAL|GRANDTOTAL}, OBJECT=TAG,
   [FONT=font], [SIZE=size], [STYLE=style],
   [COLOR={color|RGB({r g b|#hexcolor})],
   [drilltype=drillparms], $

TABLE FILE GGSALES  
SUM UNITS/D8C DOLLARS/D12CM BUDUNIT/D8C BUDDOLLARS/D12CM
BY REGION
BY CATEGORY
ON REGION SUBTOTAL
HEADING
"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 *  
INCLUDE=ENDEFLT,$ 
TYPE=SUBTOTAL, OBJECT=TAG,STYLE=ITALIC,COLOR=WHITE,$
TYPE=GRANDTOTAL, BACKCOLOR='LIGHT GREY',$
TYPE=GRANDTOTAL, OBJECT=TAG,URL='http://www.informationbuilders.com',$
ENDSTYLE   
END

FOCUS supports using ACROSS values to define conditional styling within the report. ACROSS values can now be used as part of the conditional expressions used to define styling attributes for each cell in the table.

The example below demonstrates how the ACROSS value can be referenced using either the ACROSS field name or the ACROSS column designator (A1, A2).

In this example, the ACROSS values are used in conditional styling to set a unique backcolor for all ACROSS columns in the Category Coffee, and additional font styling for the Espresso ACROSS column.

SET ACROSSTITLE=SIDE
TABLE FILE GGSALES
SUM DOLLARS/I8M AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
WHERE CATEGORY EQ 'Coffee' OR 'Food';
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
SQUEEZE=ON,UNITS=IN,ORIENTATION=PORTRAIT,$
TYPE=REPORT,FONT='ARIAL',SIZE=10,BORDER=LIGHT,$
TYPE=ACROSSTITLE,COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=ACROSSVALUE,COLOR=WHITE, BACKCOLOR=GREY,$ 
TYPE=TITLE,COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=DATA, ACROSSCOLUMN=DOLLARS, BACKCOLOR=THISTLE, WHEN=CATEGORY EQ 'Coffee',$
TYPE=DATA, ACROSSCOLUMN=DOLLARS, STYLE=BOLD+ITALIC, WHEN=A2 EQ 'Espresso', $
ENDSTYLE
END

The output is:

The SET ACROSSTITLE=SIDE command:

• Is supported for HTML, EXL2K, and XLSX report output. In prior releases, only PDF report output was supported.
• Supports the BACKCOLOR StyleSheet attribute for the ACROSS titles.

The following request against the GGSALES data source places the ACROSS titles next to the ACROSS values and holds the output in XLSX format.

SET ACROSSTITLE=SIDE
TABLE FILE GGSALES
SUM
DOLLARS/I8M AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT XLSX
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $
TYPE=REPORT, FONT='ARIAL', SIZE=10, BORDER=LIGHT,$
ENDSTYLE
END

On the output, the ACROSS title CATEGORY displays to the left of its ACROSS values.

The following request against the GGSALES data source places the ACROSS titles next to the ACROSS values and sets matching styling of font color and backcolor for the ACROSSTITLES, ACROSSVALUES, and column titles to white text on grey background color.

SET ACROSSTITLE=SIDE
TABLE FILE GGSALES
SUM DOLLARS/I8M AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
WHERE CATEGORY EQ 'Coffee' OR 'Food';
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
SQUEEZE=ON,UNITS=IN,ORIENTATION=PORTRAIT,$
TYPE=REPORT,FONT='ARIAL',SIZE=10,BORDER=LIGHT,$
TYPE=ACROSSTITLE,COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=ACROSSVALUE,COLOR=WHITE, BACKCOLOR=GREY,$ 
TYPE=TITLE,COLOR=WHITE, BACKCOLOR=GREY,$
ENDSTYLE
END

The output has a grey background color and white text for the ACROSS titles, ACROSS values, and column titles.

A field that contains a list of delimited values (such as email addresses separated by spaces) can now be pivoted to be read as individual rows when an additional segment is added with SEGSUF=DFIX (Delimited Flat File).

Previously, once the segment was added, it was not possible to change the row or record delimiter. Now they can be edited.