Reporting. That one word is enough to put the fear in me. If I had a pound for every time I'd told somebody that "Notes isn't too hot when it comes to reporting" then I'd probably be out with my mates tonight instead of sitting at home writing this....

Did anybody ever use Lotus Notes Reporter 2. That one piece of software caused me more nightmares than the time I saw V, late at night, alone and young when a lady gave birth to a lizard-tongued baby.

Luckily it's not very often I get asked to create reports. If ever I do then I usually opt for the foolproof method of printing all the data back to the browser in the form of a table. The problem being that your code needs to account for every whim of the manager and all the formats they "need" to see the data in. Can get very messy, very quickly.

The last time I got asked to do some reporting it made me think of something Chris King said. That then led me to do some investigating. What I found was very interesting indeed. Writing the reporting side of an application is almost like having fun now.


What I did:

First thing was to test the theory. To do this I created a simple agent with code similar to the following and ran it from the browser.

Print {Content-Type:application/vnd.ms-excel}
Print {Hello Excel}

Sure enough, this opened the Excel ActiveX object within the browser and displayed the data. At this point I realised this was going to change the way I develop Web Applications forever.



Next thing I tried was to send the data in the form of an HTML formatted table.

Print |Content-Type:application/vnd.ms-excel|
Print |<table border="1">
<tr><td width="80">Quarter</td><td>Sales</td></tr>
<tr><td>1</td><td>£10,000</td></tr>
<tr><td>2</td><td>£12,000</td></tr>
<tr><td>3</td><td>£18,000</td></tr>
<tr><td>4</td><td>£30,000</td></tr>
</table>|

Which produced:



Excited yet? I was. Just imagine what you can do now. All you need do is send the data to the browser and then the user can do all the work, formatting the data as they like. They can save it as an .XLS file to their hard-drive. They can also use things like AutoFilter to better view the data. Anything they want really.

You can also do this with Word and Powerpoint. The oppurtunities being almost endless.


Producing the spreadsheet:

Assuming the data you are sending to the browser is held in Notes documents that are held in views you can use code similar to the following to produce dynamic tables.

Dim db As NotesDatabase
Dim qs As NotesView
Dim nv As NotesViewNavigator
Dim ne As NotesViewEntry
Set qs = db.GetView("QuarterlySales")
Set nv = qs.CreateViewNav
Set ne = nv.GetFirst

Print |Content-Type:application/vnd.ms-excel|

Print |<table border="1">
<tr><td width="80">Quarter</td><td>Sales</td></tr>|

While Not(ne Is Nothing)

Print|<tr>
<td>| + ne.ColumnValues(1) + |</td>
<td >| + ne.ColumnValues(2) + |</td>
</tr>|

Set ne = nv.GetNext( ne )
Wend


Now the data in the spreadsheet is truly dynamic. How impressed the boss will be.


Taking it further:

The second of the above two examples is still quite limiting. What we are going to need to do to make the boss really happy is make it look pretty and add extra features.

To find out how one would go about this I first took a basic example and added some formatting and a sum formula.



With this done, I saved the spreadsheet using the "Save as Web Page" option in Excel. Here is the result. Look at the source to this page and you start to get some ideas about how to format cells as number/currency and add formulas to cells. To add the "total" row in the above code we need the following line:

Print |<tr><td>Total</td>
<td x:fmla="=SUM(B2:B5)"><b></b></td>
</tr>|

In applications where you don't know that there are 4 rows you will obviously need to add the logic to work out the number of rows and alter the above formula. You also need the lines at the top of the file that define the x namespace.


Something to note:

One problem I came across was IE getting confused about what it was presenting. To get round this probem I found it was best to end all Agent names with ".xls" so a typical URL would end in /db.nsf/Table.xls?OpenAgent. This fools IE in to believing it really is an Excel file.

Have fun....