Back Issues
The Director’s Chair
Advanced Techniques
by David Glick
New Feature
Special Feature
FOCUS 101
Update
Customer Support
OS Feature
User Group Meeting Schedule

Excel 2000 Support in FOCUS 7.3.0

Excel power users will be pleased to find support for Excel 2000 in FOCUS 7.3.0. The new EXL2K HOLD format enhances previous Excel hold capabilities with the addition of full StyleSheet support and conversion of the output data format from binary to alpha.

Feature options enable Excel users to create and employ pivot tables for OLAP analysis and to generate correct formulas within their Excel spreadsheets for calculating new cells when requests employ aggregate verbs.

Consider the following Excel request:

TABLE FILE CAR                                                           
HEADING                                                                  
"CAR FILE PIVOTTABLE"                                                    
"SUM OF SALES BY CAR ACROSS MODEL"                                       
PRINT SALES                                                              
BY CAR                                                                   
ACROSS MODEL                                                             
ON TABLE COLUMN-TOTAL                                                    

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, STYLE=BOLD, $               
ENDSTYLE                                                                 

ON TABLE HOLD AS PIVOTTST FORMAT EXL2K PIVOT                             
PAGEFIELDS COUNTRY                                                       
CACHEFIELDS RETAIL_COST TYPE                                             
END

Note the FOCUS StyleSheet code in this request and the PAGEFIELDS and CACHEFIELDS phrases, which are used to generate the pivot table information for OLAP analysis.

Here is the output:

Notice the coloring and bold fonts, which are accomplished by the StyleSheet code within the request. Pivot tables allow users to selectively toggle the display of specific field values. For example, this report shows CAR, SALES, COUNTRY, and MODEL, but since we also specified CACHEFIELDS of RETAIL_COST and TYPE in our request, these values are also available for display should we choose to do so. To display a CACHEFIELD, you simply drag it from the Pivot Table Field List and drop it where you wish to display it on the spreadsheet.

The following shows the result of dragging the RETAIL_COST field from the pivot table to the column immediately after CAR.

These are just a few of the capabilities available with the Excel 2000 format. The opportunities for creativity are endless.

Back Next