WebFOCUS Newsletter December 2003 - Creating an Efficient Excel Spreadsheet With WebFOCUS (Part 2)

In my previous column I discussed the different uses and philosophies for Excel spreadsheets and shared a few tips to help you deliver a better spreadsheet. In this article I will go into the various Excel outputs available with WebFOCUS and how best to use each one.

WebFOCUS doesn’t have just one Excel output format, but rather a variety of formats from which to choose. This at first may seem confusing, but with a little explanation you will see how each can serve your needs. No matter what version of Excel will be used, there is a comparable Excel output. In addition to Excel compatibility, there are other matters to consider when choosing an Excel output – things like efficiency, styling and distribution. All of these considerations play a major part in choosing the right output format.

There are three main Excel outputs: EXCEL, EXL97, and EXL2K. In addition, there are subsequent “extensions” to our standard Excel output: EXL2K PIVOT and EXL2K FORMULA. I have put together a chart that describes the various Excel formats along with the advantages and disadvantages to each one (see following chart). This chart will help you choose which format is right for your application.

Output Format File Description Version of Excel Primary Use Advantages Disdvantages

EXCEL Microsoft Excel binary file format Excel 2.0 and higher Output raw data into Excel Compatible with all versions of Excel
Very lightweight
Great for distribution
.xls file extension
No HEADINGs or FOOTINGs
No styling
No drill-downs
No formulas
Stabilized – no new features

EXL97 HTML file with "hard coded" styling attributes (no CSS) Excel 97 and higher Styled spreadsheets for Excel 97 and higher Retains styling
Drill-downs
No support for CSS
No formulas
No control of column width
Numeric and date format support is limited
Distributed as an HTML file
Stabilized – no new features

EXL2K Microsoft Excel 2000 HTML file format (HTML with CSS and XML) Excel 2000 and higher Styled, fully functional Excel spreadsheets (standard for WebFOCUS) Full styling
Drill-downs
Column width/wrapping
Customized sheet names
Can be distributed with ReportCaster
Heavy styling leads to large file size
No formulas
Distributed with an .xht file extension

EXL2K PIVOT Microsoft Excel 2000 HTML file format (HTML with embedded XML) Excel 2000 and higher Native Excel PivotTables Fully Functional Excel PivotTable
Can contain cached data
Cannot distribute with ReportCaster
Large file size and higher load time
Limited styling

EXL2K FORMULA Microsoft Excel 2000 HTML file format (HTML with embedded XML) Excel 2000 and higher Styled, fully functional spreadsheets with formulas Computed fields, subtotals, etc., contain native Excel formulas
Can be distributed with ReportCaster
Slightly larger processing/load time
Distributed with an .xht file extension

So, let me highlight a few points and suggest a few tips related to the chart.

Don’t write off that old EXCEL format; it actually holds a lot of value. If you are looking to simply “dump” data into Excel and are not concerned with styling, drill-downs, etc., I would recommend this format. It is much more efficient than the heavier HTML-based formats and you don’t have to worry about which version of Excel your users have. The file generated from EXCEL has an .xls extension, which is always associated with Excel. So if distribution is your main goal, you may want to consider sacrificing the bells and whistles for guaranteed delivery of your spreadsheet. Users are going to manipulate and customize the spreadsheet, anyway.

It has become apparent that many businesses are still using Excel 97 and are not going to upgrade their versions of Office anytime soon, so we decided to accommodate that with EXL97. This HTML-based file format offers a nice interim solution for users stuck on Excel 97, but want styling and drilldowns in their spreadsheets. But let me point out a quick tip when using Excel 97. There is a known issue where Excel 97 hides the second column of a report that contains a heading. This is because WebFOCUS generates the HEADING in a nested table, confusing Excel. So we can use another feature to take care of this issue. Let’s take another look at that report from the last article:

1.	SET BYDISPLAY=ON
2.	TABLE FILE CENTORD
3.	SUM
4.		PRICE/D12.2CM
5.		COST/D12.2CM
6.		COMPUTE PROFIT/D12.2CM = PRICE - COST;
7.	BY REGION BY STORENAME
8.	ON TABLE SUBHEAD
9.	"Century Corp Profit Margin Report"
10.	" "
11.	ON TABLE SUBFOOT
12.	" "
13.	"Report Generated on: &DATE"
14.	ON TABLE SET PAGE-NUM OFF
15.	ON TABLE SET ACCESSIBLE 508
16.	ON TABLE PCHOLD FORMAT EXL97
17.	ON TABLE SET STYLE *
18.	TYPE=REPORT, TITLETEXT="Profit Report", $
19.	TYPE=TABHEADING, STYLE=BOLD, SIZE=16, $
20.	ENDSTYLE
21.	END

Line 15 contains a command that is used to make our HTML reports meet government accessibility standards, and it works nicely for EXL97 too. I would recommend adding this command to any report you plan on outputting to EXL97.

EXL2K FORMULA is a new extension to our standard EXL2K format and adds native Excel formulas inside the spreadsheet. This gives the users a truly interactive worksheet that can be used for "what if" types of analyses. One thing to keep in mind is that EXL2K requires a bit more processing time to generate the report. The end result is more interactive, but the load time will suffer. So, again keep in mind the intent of use for your report. Do you absolutely need formulas in your spreadsheet? If not, then it would make more sense to simply use EXL2K.

The last tip I’d like to give you is related to the formula feature. If we run the same report and change the output format to EXL2K FORMULA (line 15 in the example in Screen 1), the PROFIT column may not have the correct width. Notice the column is as wide as the column title and pound signs are displayed in the PROFIT column.

Screen 1

This occurs because WebFOCUS has no way of knowing how wide the resulting formula value will be because the value is calculated after the spreadsheet is loaded into Excel. In WebFOCUS, column width is calculated by surveying the data in the column and setting it to the widest value in the column.

Since the PROFIT column contains formulas and no actual values, the column title is considered the widest value in the column. The user can manually expand the column width to display the values or you can take care of this in the WebFOCUS request. You can guard against a column being too narrow by specifying an exact width for that column. The following code can be added that specifies a width of 1-1/4 inches for the PROFIT column, ensuring the values will be displayed (see line 18).

1.	SET BYDISPLAY=ON
2.	TABLE FILE CENTORD
3.	SUM
4.		PRICE/D12.2CM
5.		COST/D12.2CM
6.		COMPUTE PROFIT/D12.2CM = PRICE - COST;
7.	BY REGION BY STORENAME
8.	ON TABLE SUBHEAD
9.	"Century Corp Profit Margin Report"
10.	" "
11.	ON TABLE SUBFOOT
12.	" "
13.	"Report Generated on: &DATE"
14.	ON TABLE SET PAGE-NUM OFF
15.	ON TABLE PCHOLD FORMAT EXL2K FORMULA
16.	ON TABLE SET STYLE *
17.	TYPE=REPORT, TITLETEXT="Profit Report", $
18.	TYPE=REPORT, COLUMN=PROFIT, WRAP=1.25, $
19.	TYPE=TABHEADING, STYLE=BOLD, SIZE=16, $
20.	ENDSTYLE
21.	END

I hope this helps resolve some of the main issues you may face when creating Excel spreadsheets with WebFOCUS. Keep in mind a few things when building your reports: Who will be receiving these spreadsheets? What will the spreadsheets be used for? What is my distribution method? Answering such questions and understanding the differences and advantages of the various Excel outputs will help you tremendously during the development process. In addition, choosing the most efficient Excel output will help you build a more efficient application, maximize your IT resources, and keep those users happy. Well, as they say, “two out of three ain’t bad.”

See you in the next issue.