IBM®
Skip to main content
    Country/region select      Terms of use
 
 
   
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

I've been corresponding with a customer recently about their attempt to create a view with way, way too many columns. The purpose of this view is to export data into a CSV file so it can be opened in a spreadsheet. The customer claims this is common -- do you folks agree? If so, I have a new crusade.  :-)

The problem with such a view is that it's huge and causes lots of work for the server, so it's dragging down performance. (Especially if, as in this case, the developer adds a re-sort, ascending and descending, on every column. Oops). My take on this is:

  • You don't need a view to do an export -- a script can do it just fine, and the script costs nothing when not in use.
  • CSV is a poor style of output anyway, because it has none of the nice things already in it that spreadsheet users want, like non-scrolling columns, formatting, totals, pivot tables, macros, etcetera. Obviously you can add those things post-export, or have another spreadsheet with that stuff on it and copy/paste the data, but that's extra work. Wouldn't it be nicer for the users if the data were automatically added into a professionally designed template spreadsheet specific to their task?
  • The user has to go to the export view to do the export -- they can't just do it from anywhere -- and manually select the documents to export from that view.  This ignores the fact that users will typically want to export not a random set of documents, but a group of documents defined by parameters -- all documents for department A in a specified range of dates, for instance -- and it's not straightforward to make such a selection in a view of this type.  Full-text search can locate documents based on such criteria -- but the size of the result set is limited.  Better to have an export dialog that's specific to the task, where they can select the department or whatever, and the desired documents can be located by key searches in a view. And maybe also allow export of selected documents, all documents, or everything in a highlighted category -- just in case.
So the goal is to have the simplest possible way for the user to specify what they want to export, and export it into a spreadsheet that has all the controls they want already in it.  Of course, you don't want to use a program to add all these features; you have someone who knows what they're doing create the power-user spreadsheet manually, and then attach a copy, sans actual data, into a configuration document in your application.  When the user requests an export, you detach the template, then use OLE automation or another API to plug in the data and adjust the size of a range that contains the data.

Since this is a performance topic and I'm presenting about design for performance at Lotusphere, this gives me the perfect excuse to go ahead and write a general-purpose exporter of this type. The configuration document will contain not only the file attachment, but the list of field names/datatypes/formatting strings, column headings (optional), range name, and so forth.

I've got it mostly working and will post it here when I'm done. (UPDATE: see entry dated 1/22/09 for download)

Andre Guirard | 23 December 2008 05:50:00 PM ET | Home, Plymouth, MN, USA | Comments (23)


 Comments

1) Cool!
Karsten Lehmann | 12/23/2008 6:45:06 PM

Much better than reinventing the wheel over and over again.

Which spreadsheet software will you support, Excel and/or Symphony (cross-platform, please)?

2) Ditto
Jane Griscti | 12/23/2008 6:50:42 PM

I've inherited a number of databases with 'export' views, some db's have multiple export views; a generic tool would come in handy :)

3) Export Format
Roland Reddekop | 12/23/2008 7:03:30 PM

Andre,

Exporting data is a common enough a request that we should have a non-programmatic easy way of doing it from anywhere, not just a view, to formats that are actually useful.

For example what is the usefulness of exporting to WKS? No, that's a legacy that has no use especially since a recent MS Excel service pack has made opening of WKS files impossible without registry changes. And exporting to CSV is also problematic for the reasons you've indicated.

Karsten above suggested a cross-platform format and with MS Office 2007 SP3 to support ODF (abeit 1.1 version), there's your cross-platform format that will be usable in both MS Office and Symphony or other ODF editors.

Being able to provide a user with a simple wizard to export data from any database, be it their address book or a sales application is a very common request and we don't do it well.

4) Export update is overdue
Ed Maloney | 12/23/2008 7:36:33 PM

I agree with #3, the export features in Notes are nearly useless by today's standards. There are plenty of free export/import utilities and code samples out there, but the product should include a modern, easy to use export utility.

5) inCREDibley common
Craig Wiseman | 12/23/2008 10:04:07 PM

This is a vastly common thing, so much so that a lot of times I load NotesSQL on the users' PC so they can pull data into Excel when/as they need to.

I really reallly REALLLY appreciate you looking into this - it was overdue 10 years ago.

6) Extremely common request
Michelle O’Rorke | 12/24/2008 12:28:39 AM

I too have come across some monster views. Just yesterday I was looking at a view which contained *every* field on the form. Now I don't know that it was specifically for exporting but I can't see any other reason for it. (At least only the first column was sorted).

I agree that better exporting is needed, but it is needed in the core product not as additional code. I will certainly appreciate your code but lots of developers don't read blogs or download free code examples. Until better exporting and / or reporting is part of the core product Notes will continue to get a bad name in this area.

7) Very Common
John J | 12/24/2008 9:04:19 AM

This is much needed by me. We often have clients asking for extracts of their data, and need to write custom scripts everytime.

Allowing them to select the fields to extract would be great. How about making it web enabled?? It'd make a great Christmas Present.

8) Extremely common
Erik Brooks | 12/24/2008 10:59:25 AM

Our software (we're an ASP built on Domino, delivered on the web) is used by many market research companies, and we've had to write our own exporters that are fairly sophisticated.

Want to really sink your teeth into things? Tackle these challenges:

- make it work on the web

- make it incorporate the user's current regional locale settings as detected by HTTP

- make it work from any view, e.g. via $$ViewTemplate

- make it handle categorized categories, totals, etc.

By the way - running OLE to a spreadsheet is pretty slow, and not universally compatible.

If you want it *fast*, then write the export as HTML to a text file using simple <td> tags.

Make sure the filename ends with .xls, and open *that* with Excel. Excel will open it beautifully as a spreadsheet. Then you can even run it on a Linux server (which obviously doesn't have Excel installed), deliver to a browser, and let their spreadsheet of choice open it.

9) Re: The dreaded export view
Charles Robinson | 12/24/2008 11:20:04 AM

I've done some automated export utilities for users. They want to SEE the data before they commit to exporting it and that has been very problematic. Also, most of my export views are viewed by management periodically and exported daily by other people. So I suppose it's not purely an export view, but it is for the vast majority of the time.

I'd really like to see Notes have better built-in options for searching and exporting. The way it is now is incredibly confusing and frustrating for me, and I have a difficult time helping end users with it.

By the way, your automatic subject filler-inner broke. :-)

10) Thanks in advance....
Rakesh | 12/24/2008 12:04:21 PM

I have seen these views in a lot many places and there is a definite need for a better approach.

Main reasons for the views:

- Users want to select the docs.. most often its all docs within a category

- The output is not necessarily the fields themselves, but the column values (which might be computed)

If you can write a generic utility which takes care of these 2 points.. that would be marvelous :)

11) EXPORTing in many cases is to enable REPORTing on the data
Craig Wiseamn | 12/24/2008 2:16:39 PM

Mr. Robinson, et al, have reminded me that in many cases folks use these export views to generate reports on Notes-sourced data. As mentioned, a lot of times I'll use NotesSQL and a reporting tool (like Crystal, or even Excel or Access) to make spiffy reports.

This area was one of the reasons for Notes Reporter, and while the product is gone, the need to easily produce visually high quality reports has NOT gone away.

12) You know it’s a bad day when you can’t even spel your own name.
Craig Wiseman | 12/24/2008 2:18:49 PM

Regardless, (or is it irregardless?)

Merry Christmas/Happy Hanukkah...

13) Good idea...
Keith Strickland | 12/25/2008 11:26:05 AM

I've seen the reporting views many times and from my experience a lot of them (not most really) have all the columns sortable, big no-no. I've also created custom reports exporting out to excel using OLE, it works but can be a pain.

One of the better things I've used is LEI to export to MS SQL Server, build custom tables using stored procedures and then used the SQL Server reporting services ({ Link } ) to build quality reports. I think something along the line of SQL Server reporting services would be ideal as sometimes LEI can be problematic. This would suit most of the requirements listed by everyone else. It would be web based, export to a variety of formats and allow customization of the data and the report.

14) Would be fasterusing XML
Youssef Chaouchi | 12/26/2008 3:33:56 PM

To extract data from documents within a database you can use external tool such as the one available here { Link } the tool has many features. You can build a similar generic program with the features you need. The main benefits are :

- the tool doesn't require any change in the source database.

- you may use an existing view but this is not mandatory.

When dealing with XL Files, I've always faced issues regarding OLE unstability / XL different behaviour depending of Software release... What works fine in one place may not run in other places.

Having EXCEL installed on a server and use it through COM without server crashes is more than a great challenge.

The only way to have a fast, stable solution I found was to geneate an XML text file. Excel has its own XML data structure. What I did was create the EXCEL file "template" within XL. Ask for an XML export and then have a look to the xml file. It's not too difficult to generate this xml text file from Lotus Notes. Writing to text file is fast and riskless. No need to have XL set up nor to use COM either on the client or on the server.

The XML file is linked to XL so the user may even not notice he hasn't retrieved a XML file instead of an XSL one.

15) custom export
Patrick Kwinten | 12/27/2008 9:20:31 AM

for such an exports I have created a wizard function so users can create their custom exports:

{ Link }

saves a lot of developer time when a new 'customized' export is needed...

16) Java Excel API
Sean Jack | 12/29/2008 9:32:50 AM

If you need to do some exporting from a server or perhaps a machine without Excel (or the right version) there is an open source Java API that will read / write Excel spreadsheets that I have used before for custom export agents. The OLE stuff has always seemed a little flaky to me.

{ Link }

17) Java Excel API (Apache POI)
Carlos | 12/29/2008 3:40:56 PM

I've used the apache poi api to generate excel files on servers where excel is not installed. Here is a simple example of using it in domino --> { Link }

18) Getting closer?
Paul G | 1/12/2009 8:10:35 AM

Hi Andre

I read your blog regularly, and am keen to see what you are going to suggest. We have 2 or 3 versions of our own export routines in most of our dbs at all our projects here.

We have deployed ASND from OpenNTF as a solution for exporting with varying degrees of success.

19) An all purpose export tool
Jakob Majkilde | 2/3/2009 1:54:54 PM

I have made an all purpose import/export tool: { Link }

Key features

- From a simple dialogbox the user can export any view to excel.

- Can export all documents, selected documents, based on a formula or categories only (totals)

- Superuser can create export profiles with ready made settings and Excel templates with charts

Includes scheduled syncronization:

- Can sync between text files, excel files and Notes databases, e.g. import text files daily, sync 2 notes databases or export data to an excel file and send this report in a mail on a monthly basis.

And it is free :-)

20) Connection with Excel would not be the way to go
Martijn de Jong | 2/24/2009 8:22:27 AM

Hi Andre,

I had to do something like this recently and, as I was under tremendous time pressure to finish the design, I did use a view. It started with 9 columns and has since grown to 15. It outputs csv files which are mailed to the (external) requester. I use csv as putting Excel on a server to use OLE to create Excel sheets is an absolute nono. Any standard solution which you come up with (and which I'm very much looking forward to) should in my eyes therefore not use any OLE requests or need other software than Domino on the server or client. I've found the comments by Eric and Youssef very interesting in this regard.

I chose a view for 2 reasons. One was that it was faster to program it that way, but the other reason is my experiences with a db.search in databases with a large amount of documents. I've noticed in the past that using a db.search to on the fly create an export file was simply too slow to finish in the allowable time (especially in the context of a web request).

21) The dreaded export view
Andre Guirard | 2/24/2009 11:11:36 AM

Martijn, I did post the code in this blog on 1/22/09. It supports CSV output as well.

There's no reason you can't use a view to locate the documents you need to export, especially if your selection criteria are the same every time. But you don't need a column for every value you want to include in the export.

Of course, if you're doing it for a web request, you might use HTML option in the view properties and template form, and make the view output _be_ your CSV file. Take care with commas and quotes in the data, though.

22) I thought I saw you show it @Lotusphere :-)
Martijn de Jong | 2/25/2009 4:09:24 AM

Thanks Andre. I should check the dates before posting comments. I thought I saw the code in your example database of your AD214 session and I apparently did. Didn't have time to take a proper look at it.

Yes, as long as it uses a view to select the documents, it should work fine. There's a cost to walking the view and accessing the documents compared to using the ViewNavigator, but I agree that the advantage of flexibility and keeping view indexes smaller outweighs that disadvantage. Good point on making the view the csv file for a web request. That would be the fastest way to do it.

23) Where? This thread says "I will post" and there’s not further mention
Charles Ross | 6/24/2009 11:39:22 AM

For continuity of this thread, it would make sense to close the loop you started...

 Add a Comment
Subject:
   
Name:
Comment:  (No HTML - Links will be converted if prefixed http://)
 
Remember Me?     Cancel

Search this blog 

Disclaimer 

    About IBM Privacy Contact