Keeping the boss happy
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....
Feedback:
Keeping the boss happy . .
Not working (Kurt, Mon 10 Dec 2001)
. . . .
Re: Not working (Jake Howlett, Mon 10 Dec 2001)
. . . .
Web Components (Malmberg, Tue 11 Dec 2001)
. . . . . .
Re: Web Components (Jake Howlett, Tue 11 Dec 2001)
. . . . . . . .
Re: Web Components (Steve Castledine, Tue 11 Dec 2001)
. . . . . . . . . .
Try export straight to HTML file (Ben Newman, Wed 12 Dec 2001)
. . . . . . . . . . . .
how to convery HTML file to PDF & DOC (HARISH, Fri 17 Sep 2004)
. . . . . .
Re: Web Components (J Burrows, Wed 12 Dec 2001)
. . . . . .
Re: Web Components (saad rabbani, Fri 18 Jul 2003)
. . . .
Re: Not working (Richard Sampson, Wed 12 Dec 2001)
. .
open a view in excel (Francesco Tarantino, Wed 12 Dec 2001)
. .
HTML Reports (Jack Ratcliff, Mon 17 Dec 2001)
. . . .
Re: HTML Reports (Fred Stoki, Tue 18 Dec 2001)
. . . .
Re: HTML Reports (Steve, Tue 18 Dec 2001)
. . . . . .
Re: HTML Reports (Jake Howlett, Tue 18 Dec 2001)
. . . . . .
Re: HTML Reports (Giles Hill, Fri 10 May 2002)
. .
Launching Excel in-place (Mike Golding, Wed 19 Dec 2001)
. . . .
Client side launch? (Folke, Tue 29 Jan 2008)
. .
Doing this Using Web Components (Mike Golding, Thu 20 Dec 2001)
. . . .
Re: Doing this Using Web Components (ajay, Tue 26 Nov 2002)
. . . . . .
Re: Doing this Using Web Components (Jake Howlett, Tue 26 Nov 2002)
. . . . . . . .
Thanks, Jake (ajay, Tue 26 Nov 2002)
. . . . . . . .
Re: Doing this Using Web Components (ajay, Tue 26 Nov 2002)
. .
An alternative (Jetze van der Wal, Fri 28 Dec 2001)
. .
export to excel from notes using browser Domino R5 (jay, Sun 30 Dec 2001)
. .
Combined this with an agent I use to export CSV (Chad Amberg, Sun 30 Dec 2001)
. . . .
Re: Combined this with an agent I use to export CSV (Aodhaidin O'Shea, Wed 12 Jun 2002)
. . . .
Re: Combined this with an agent I use to export CSV (leon, Sun 10 Nov 2002)
. . . . . .
Re: Combined this with an agent I use to export CS (Freitag, Tue 25 Apr 2006)
. . . .
Re: Content-type:application/vnd.ms-excel (srinivas, Thu 22 Jan 2004)
. . . . . .
Re: Content-type:application/vnd.ms-excel (Sandesh, Tue 21 Jun 2005)
. . . . . .
Re: Content-type:application/vnd.ms-excel (Nitesh, Wed 28 Jun 2006)
. . . . . . . .
Re: Content-type:application/vnd.ms-excel (Ruediger Weitz, Mon 16 Apr 2007)
. . . . . .
Re: Content-type:application/vnd.ms-excel (magesh, Wed 15 Aug 2007)
. . . .
Re: Combined this with an agent I use to export CSV (Roy Castillo, Fri 19 May 2006)
. .
Report (janardan, Wed 2 Jan 2002)
. . . .
Saving the excel doc (Hugh Lazarus, Wed 6 Feb 2002)
. . . . . .
Re: Open the excel doc (Sri, Mon 24 Oct 2005)
. .
Boggles the Mind (Ian O'Rourke, Thu 17 Jan 2002)
. .
Domino Security (Ian O'Rourke, Thu 17 Jan 2002)
. . . .
Re: Domino Security (Jonathon Llewellyn thomas, Fri 17 May 2002)
. .
Column formatting (Sam, Fri 18 Jan 2002)
. . . .
Re: Column formatting (PAL, Fri 18 Oct 2002)
. . . .
Re: Column formatting (Jake Howlett, Tue 22 Jan 2002)
. . . . . .
Re: Column formatting (Lea LoBue, Thu 9 May 2002)
. . . . . .
making sure cell displays as text (sander peters, Wed 24 Aug 2005)
. .
Works on one server but not another (Michelle Snow, Tue 22 Jan 2002)
. . . .
Re: Works on one server but not another (Michelle Snow, Mon 11 Feb 2002)
. .
Saving the excel doc (Hugh Lazarus, Wed 6 Feb 2002)
. . . .
Re: Saving the excel doc (Paul Graham, Thu 7 Feb 2002)
. . . . . .
Re: Saving the excel doc (Hugh Lazarus, Fri 8 Feb 2002)
. . . . . .
Re: Saving the excel doc (Mike D, Fri 15 Feb 2002)
. .
how is the agent called ? (Ray, Thu 14 Feb 2002)
. . . .
Re: how is the agent called ? (Pat C: ), Fri 10 Oct 2003)
. .
Problem getting the column total to work (Tim, Thu 14 Feb 2002)
. . . .
Re: How to Hide Column Grand Total (jolz, Fri 8 Mar 2002)
. . . . . .
Re: How to Hide Column Grand Total (Brad Malia, Thu 5 Feb 2004)
. . . . . . . .
How to Hide Row Grand Total in OWC 10/11 (Fabiano S. Carneiro (brazil), Thu 25 Mar 2004)
. . . . . . . . . .
Re: How to Hide Row Grand Total in OWC 10/11 (Jagdev, Wed 14 Jun 2006)
. . . .
Login screen appears when exporting to excel (RButt, Sat 27 Nov 2004)
. . . .
Jack Please Respond .. (Manish, Wed 10 Aug 2005)
. . . .
Here's my take on the code to do this (Adam Fenstermaker, Tue 20 Dec 2005)
. .
Problems with the while loop... (Vegard, Mon 25 Feb 2002)
. . . .
Re: Problems with the while loop... (Vegard, Mon 25 Feb 2002)
. . . . . .
Re: Problems with the while loop... (joel, Thu 4 Aug 2005)
. .
Set column propertie values in Excel (vegard, Tue 26 Feb 2002)
. . . .
Re: Set column propertie values in Excel (Pat Read, Tue 26 Feb 2002)
. . . . . .
Controlling Excel from LotusScript (Campbell Moody, Thu 17 Jul 2003)
. .
Works also in Netscape Navigator (Ray Weber, Thu 28 Feb 2002)
. .
Works for RTF also (Keith Nolen, Tue 12 Mar 2002)
. .
A link to the RTF specification (Keith Nolen, Tue 12 Mar 2002)
. . . .
Re: A link to the RTF specification (Jake Howlett, Tue 12 Mar 2002)
. .
Agent Done? - Again (Samantha, Thu 28 Mar 2002)
. . . .
Re: Agent Done? - Again (Eric, Fri 29 Mar 2002)
. . . . . .
Re: Agent Done? - Again (Jason, Fri 29 Mar 2002)
. . . . . .
Re: Agent Done? - Again (Samantha, Mon 1 Apr 2002)
. . . . . . . .
Re: Agent Done? - Again (vidhya sankar, Wed 3 Apr 2002)
. . . . . . . .
Re: Agent Done? - Again (Lee, Thu 4 Apr 2002)
. . . . . . . . . .
Re: Agent Done? - Again (Jake Howlett, Fri 5 Apr 2002)
. . . . . . . . . . . .
Re: Agent Done? - Again (Lee, Fri 5 Apr 2002)
. . . . . . . . . .
Re: Agent Done? - Again (Samantha, Wed 10 Apr 2002)
. . . . . . . . . . . .
Re: Agent Done? - Again (J, Thu 9 Sep 2004)
. .
open an excel template and report (George Malkoun, Thu 18 Apr 2002)
. . . .
Re: open an excel template and report (jey, Tue 11 Jun 2002)
. . . . . .
Re: open an excel template and report (Brian Gaherty, Wed 17 Nov 2004)
. .
THANK YOU! (Ginni Machamer, Tue 23 Apr 2002)
. .
Use with MS-Word? (Jonathon Llewellyn Thomas, Fri 17 May 2002)
. . . .
Re: Use with MS-Word? (Jake Howlett, Mon 20 May 2002)
. . . . . .
Re: Use with MS-Word? (Jonathon, Mon 20 May 2002)
. . . . . . . .
Re: Use with MS-Word? (Giuseppe Maio, Tue 21 May 2002)
. . . . . . . .
Re: Use with MS-Word? (sundar, Thu 13 Jun 2002)
. . . . . . . .
Re: Use with MS-Word? (Brian Sniegocki, Fri 11 Apr 2003)
. . . . . . . . . .
Re: Use with MS-Word? (Jake Howlett, Fri 11 Apr 2003)
. . . . . . . .
Re: Use with MS-Word? (Paul, Tue 28 Sep 2004)
. .
Exporting selected documents only... Example (Jonathon Llewellyn Thomas, Mon 20 May 2002)
. . . .
Re: Exporting selected documents only... Example (Jerry, Fri 28 Jun 2002)
. . . .
Problem Faced (neel, Tue 22 Jan 2008)
. .
EXCEL as Pop-up won't close/refocus - error (Paula Wright, Mon 28 Oct 2002)
. .
Embedded Excel in VB app (Pat Hanc, Mon 2 Dec 2002)
. .
I' Going Bald! (Ian O'Rourke, Tue 17 Dec 2002)
. . . .
Re: I' Going Bald! (Ian O'Rourke, Tue 17 Dec 2002)
. .
Cell formula not working (Hope Niblick, Tue 18 Mar 2003)
. . . .
Re: Cell formula not working (Savy, Wed 2 Apr 2003)
. .
Notes release 6 (Patrick Kwinten, Mon 31 Mar 2003)
. . . .
Re: Notes release 6 (Jake Howlett, Mon 31 Mar 2003)
. . . .
Re: Notes release 6 (Kumar, Sat 22 Nov 2003)
. .
Closing excel in the agent (Corbitt, Mon 9 Jun 2003)
. . . .
Re: Closing excel in the agent (Jake Howlett, Mon 9 Jun 2003)
. . . .
Re: Closing excel in the agent (Balaji Vaddepalli, Wed 15 Aug 2007)
. .
It works!! kind of..... (David Albright, Thu 31 Jul 2003)
. . . .
Re: It works!! kind of.....CORRECTED (David Albright, Fri 1 Aug 2003)
. .
Lotus Notes Reporter 2 (Andy Davies, Tue 8 Jul 2003)
. .
Cell data too large (Sumathi, Tue 26 Aug 2003)
. . . .
Re: Cell data too large (valexiev, Tue 3 Feb 2004)
. . . . . .
Re: Cell data too large (Lanette Boren, Wed 9 Mar 2005)
. .
problem with set (lomags, Fri 18 Jul 2003)
. . . .
Creating multiple worksheets (Caroline Shields, Fri 15 Aug 2003)
. . . . . .
Re: Creating multiple worksheets - use XML (Caroline McGrath, Mon 20 Dec 2004)
. .
Create in new window (Kurt, Thu 27 Jan 2005)
. . . .
Re: Create in new window (Jake Howlett, Thu 27 Jan 2005)
. . . . . .
Re: Create in new window (Kurt, Fri 28 Jan 2005)
. . . .
Re: Create in new window (Per M, Tue 18 Sep 2007)
. .
whether doese it work in Domino R6.5?? (Thomas liu, Tue 22 Mar 2005)
. .
Great (Per M, Wed 8 Jun 2005)
. .
Based on a file template? (L, Mon 20 Jun 2005)
. .
Not working for Spanish Characters (Lingaraje, Thu 20 Apr 2006)
. .
API for Excel to use from Notes... (Pankaj, Wed 6 Dec 2006)
. .
Problem with display (Justin, Fri 15 Feb 2008)
. . . .
Re: Problem with display (Jake Howlett, Fri 15 Feb 2008)
. .
Another way to create Excel reports from Notes (Thierry Seunevel, Thu 17 Apr 2008)
. . . .
Re: Another way to create Excel reports from Notes (AA, Wed 15 Oct 2008)
. .
problem with giving the filename (Ravi, Tue 29 Apr 2008)
. . . .
How to lock the header (Ravi, Wed 30 Apr 2008)
. .
how to sort columns (ND, Thu 4 Sep 2008)
Add your response here:
