WebFOCUS Newsletter September 2003 - Creating an Efficient Excel Spreadsheet With WebFOCUS

When it comes to outputting data into an Excel spreadsheet, WebFOCUS reigns supreme. Whether you want styling, drill-downs, formulas, or even PivotTables, WebFOCUS has it covered.

This powerful Excel integration has evolved over the past few years into a tool that helps you and your customers analyze data in the most effective way possible. The current strategy here in the Product Management group is to provide the most efficient Excel spreadsheets possible, allowing the user to immediately begin customizing and analyzing the data. This means that any spreadsheet generated by WebFOCUS has to be prepared and be "functionally ready" for the many features available for use in Excel. This article describes a few simple techniques to make your spreadsheets ready for action.

The first commandment that we follow in the product division when developing and enhancing our Excel integration offering is that Excel spreadsheets are primarily used for analyzing data. There’s no better way to deliver information to be used for analysis than to deliver it in an interactive spreadsheet.

The main function of a spreadsheet is to allow someone to manipulate an extraction of data, analyze it and make decisions based on the analysis. That decision-making process involves the use of all of Excel’s analytical features such as sorting, filtering, applying formulas, and even changing the actual values to see how the numbers change. This important analytical aspect takes precedent over styling.

Yes, Excel has an abundance of color, shading, border options, and text orientation features allowing a skilled user to turn a boring dataset into a thing of beauty. Any ordinary spreadsheet can be transformed into a work of art that will dazzle the most demanding manager or executive. I can say this because I have spent countless hours tweaking every little detail of a spreadsheet to deliver a final output worthy of framing and hanging on the CEO’s office door (or at least in my own cubicle). There’s no doubt, Excel is a masterful product when it comes to formatting and presenting a report.

Yet, the formatting usually comes after the analysis. I have built spreadsheets that span the width of a conference room, and all of the formatting in the world couldn’t tie it all together. So the main goal to keep in mind when designing a spreadsheet is to make sure that the data itself is ready for analysis. Remember, the formatting options will still be available after the analysis is done. It’s certainly more efficient to apply the heavy duty styling and formatting after all of the "sorting" is done.

With this in mind, there are a few basic rules one must follow to ensure complete compatibility with Excel. First, there can be no breaks in the data. Excel has the ability to recognize and automatically highlight all of the data making sorting, filtering, and applying sub totals very easy. Excel highlights the data by searching for the first blank line and blank column in the current data "range." So if you want to take advantage of this timesaving feature, make sure the data has no breaks returned by WebFOCUS.

Second, keep in mind that any columns or rows attached to the data range will also be highlighted and potentially sorted, filtered, etc. So for things like HEADING and FOOTING, you want to make sure there are breaks in the data.

To handle the first rule, two things have to happen in the WebFOCUS request. By default WebFOCUS suppresses repeated sort values. While this may look nice for an HTML report, it does not serve much purpose for analysis in Excel. There is a SET command that reverses this default behavior and ensures all values are displayed in the spreadsheet (line 1 in the second example on page 16). The second thing to remember is that WebFOCUS will not automatically add a blank line between a HEADING/FOOTING and the body of the report. You must specify it manually in the request (line 10 in the second example).

First let’s look at a bad example:

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

Notice the spreadsheet in Screen 1 is not completely populated. There’s no break between the HEADING/FOOTING and the data, totals are “hard-coded” in the spreadsheet.

Screen 1

This may be fine if you are not going to analyze the data, but if that were the case, wouldn’t HTML or PDF format have sufficed? What if I want to sort the data? Well, I would have to manually delete all of the total rows and insert rows to separate my footings. What a pain! Also notice that the highlighted data range includes my SUBFOOT because Excel thinks it’s a part of the data range.

Now let’s look at a good example:

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
16. ON TABLE SET STYLE *
17. TYPE=REPORT, TITLETEXT="Profit Report", 
    $
18. TYPE=TABHEADING, STYLE=BOLD, SIZE=16, $
19. ENDSTYLE
20. END

Now we have a fully populated spreadsheet ready for analysis with Excel (Screen 2). Note there are no breaks in the data, there is a blank line separating the HEADING as well as the FOOTING from the body of the report, and most importantly, there are no “hard-coded” subtotals.

Screen 2

In this example you can visually see how the data will be sorted properly with just a couple of clicks. If totals are desired, the user can easily add them using Excel’s built-in subtotal feature. This allows the user to add and remove subtotals very easily without interfering with sorting operations. Finally, note the customized worksheet name as specified in line 17. That’s not really necessary for analysis, but I figured I would throw it in anyway.

That covers the basic concepts of creating an Excel spreadsheet with WebFOCUS. Remember, the reason users want a report in Excel is so that they can do something with it. They are not going to just look at the report; they want to use all of the cool analytical features Excel has to offer. Your job as a developer is to give them a report that is ready for that task, and these tips will help you.

Stay tuned for the next article, in which I’ll discuss the various Excel formats that WebFOCUS has to offer.