If you're also an Excel expert, the R1C1 format for specifying cells might seem obvious, but that runs into an Excel blank wall. R1C1 addressing must be switched on within the worksheet and there is no XML for that (yet).

The answer is to use the Excel OFFSET(cell, rowoffset,columnoffset,height,width) function. This returns the value of a cell a certain number of rows or columns away from a fixed point. Therefore, to access the value in the column F of the current row, the function is:

Out of that one basic principle, you can write many types of formula -- calculating tax, subtracting one column from another -- and without the need for any computed Notes fields. Don't forget, however, to supply a static value for browser viewers, like this:

With these basic principles, complex dynamically built spreadsheets can be created with row formula, column totals and more. You'll turn your Excel users into active modelers of your data rather than passive recipients. And no OLE, no DDE , and for these small spreadsheets, no LotusScript.

The XML Icing on the Cake
You were promised the ability to create pivot tables in Excel, and you're almost there.

For the pivot table, create a simple untotalled view - Excel will provide all the column totals itself. Next, choose which field you wish to appear horizontally and which vertically in the pivot. Add a few options to the standard tags, and you're there!

The changes to the tags are:

• Add parameters CROSSTAB and CROSSTABGRAND to the <TABLE> tag

• Add ROWFIELD parameter to the field you wish to have appear vertically in the pivot

• Add COLFIELD parameter to the field you wish to have appear horizontally in the pivot

• Add DATAFIELD and AGGREGATE parameters to the field you want totaled

The example in Figure D demonstrates how little you need to do to turn the simplest Domino table view into a pivot:

FIGURE D


Here's a $$ViewTemplate form to create an XML pivot table for Excel. Roll over picture for a larger image.

Using agents
For maximum flexibility, Notes views must be passed over in favor of agents. If you were frightened off Web agents by examples of Perl cgi-bin scripts, have no fear -- LotusScript Web agents are comparatively simple. There are three basic principles involved in writing them:-

• You'll need a special document, known as the DocumentContext, which holds information about the user who called the agent (i.e., the CGI variables)

• The parameters given to the agent, are held in the item QueryString of DocumentContext

• The PRINT statement is used to send HTML directly back to the browser

To call an agent, use the format:

Parameters are supplied using the "&" character after the ?OpenAgent keyword. For example, the sample agent below can be called to show the view "Excel"