Look Out, My Spreadsheet Is Overflowing!

By Brian Carter

I am certain I heard the faint sound of applause in the distance back when WebFOCUS 5.3.2 was actually released. The applause wasn't for the plethora of enhancements such as the performance improvements, security features, or even the super cool new graphics capabilities. No, the applause was for a single Excel feature – the ability to create an Excel report that has multiple worksheets in a single workbook.

Yes, you heard correctly. The "compound Excel" feature is in.

There are two flavors of this feature. The first is the ability to generate a different Excel worksheet for each request of data. Each worksheet is its own report containing data from any data source that even can be in the form of a PivotTable. These reports are all grouped together in a single Excel workbook that can be distributed anywhere in your application.

The other flavor is more of a "bursting" feature that takes an individual data request and breaks the output into separate worksheets based on the first sort field. In this case each primary sort value is placed in its own worksheet. For example, "sales by country" would deliver a workbook with a separate worksheet for each country in the dataset.

In addition to all of the "out-of-the-box" benefits to this new functionality, there is a technique that can be used to solve a specific reporting problem. As we all know, Excel limits the number of rows that can appear in a single worksheet to 65,536. I have been asked on several occasions, "How can I incorporate more than 65,000 rows of data into an Excel spreadsheet?"

Well, with the new compound Excel feature we can now accommodate these large Excel reports. By using a combination of a HOLD file and the Excel bursting feature we can set up a counter that automatically breaks the data into multiple worksheets when the desired row limit is reached.

Here's a sample report that demonstrates this technique:

1.	TABLE FILE QAFOC03
2.	PRINT F3SSN
3.	COMPUTE COUNTER/I9=COUNTER+1;
4.	COMPUTE WORKSHEET/I4=COUNTER/65500;
5.	ON TABLE HOLD AS EXL65K FORMAT ALPHA
6.	END
7.	TABLE FILE EXL65K
8.	PRINT F3SSN BY WORKSHEET NOPRINT
9.	ON TABLE PCHOLD FORMAT EXL2K BYTOC
10.	END

This request queries a sample QA database containing exactly 100,000 records. Line 2 prints out a field in the database representing the number of each record, which will help to demonstrate this technique. Line 3 starts our counter. A COMPUTE is used to count each record that is printed. Line 4 takes the COUNTER field and divides it by the maximum number of rows desired for each worksheet. This number is arbitrary and you may not want to fill every row in the worksheet, just in case you need room for any manipulation that may be done after the fact. I have left 36 blank rows at the end of the worksheet in my example. The WORKSHEET field represents the number of worksheets required to display all of the data and will be used in the second request. Line 5 holds the data in a temporary file in an alphanumeric format. Line 7 begins the second request against the temporary HOLD file. Then, line 8 prints the F3SSN field and sorts it by the WORKSHEET field. The NOPRINT keyword ensures the WORKSHEET field will not be printed in the final output.

Finally, line 9 sends the output to the browser in our Excel output format. The special BYTOC keyword tells the formatting engine to burst the data into multiple worksheets. You may notice this is the same keyword used to generate a table of contents in an HTML report. In HTML the output is broken into separate sections that are only displayed when selected from the table of contents in the report. This is very similar to the Excel feature since the data is broken up and only displayed when a worksheet tab is selected. Screen 1 displays both worksheets in the Excel output from my example.

Screen 1

Notice the data in worksheet number one stops after 65,500 rows and starts up again on the second worksheet. This technique will continue to create additional worksheets as needed, but beware of the file size.

I hope this offers some assistance to those in need of this type of functionality. Be on the lookout for more exciting Excel functionality in coming releases.