A Lotus Technical Blog by the MartinScott Team

MSG - the MartinScott Guide to all things Collaboration

Simple agent to help you create ad-hoc reports via HTTP

Daryl Rochette  August 5 2009 02:55:30 PM
At the bottom of this post, you'll find a handy little agent (XMLExport) written by Gary Devendorf (http://interoptips.com/), I’ve made some changes to the original agent, adding support for @Formulas in the return results and also fixing some items to make Excel happy, but the bulk of the agent is Gary's. XMLExport is simple, works great & has become a staple in my Domino toolkit.

XMLExport allows you to create ad hoc queries, via an HTTP request, into any Domino database on your server to which you have access. The results are returned in well-formed XML which 3rd party products such as Excel will translate easily into tables. Take a look at the data imported into Excel & compare it to the results using ?ReadViewEntries.

This is a simple view with 3 columns (ClassName, Department, ClassDays), imported using $ReadViewEntries.
(http://www.martinscott.com/samples/classlist.nsf/all?ReadViewEntries)


Here is the same view, accessed via the XMLExport agent.
(http://www.martinscott.com/samples/classlist.nsf/XMLExport?Openagent&samples/classlist.nsf!View!All!ClassName!Department!ClassDays)


The syntax for calling this agent is as follows:
http://www.yourserver.com/yourdb.nsf/XMLExport?openagent&querydb.nsf!CMD!CMDParam!FieldList
Where:
  • yourdb.nsf is the database where you placed the XMLExport agent.
  • querydb.nsf is the database which you are querying.
  • CMD specifies the query method. CMD must be either View or Search.
  • If CMD is View, then CMDParam must contain a valid view name in the querydb. All documents in the named view will be returned in the query.
  • If CMD is Search, then CMDParam must contain a valid search string using standard Notes search syntax, URL encoded. For example:
    • %5BForm%5D=(MainForm)AND%5BOwner%5D=(Daryl%20Rochette)
  • FieldList is the list of field names to return in the query, separated by !. @Formulas can be used here as well.

There are many useful applications for XMLExport. A simple, powerful use is to use the Import Web Data function in Excel to import real-time data in Excel, which you can then use to drive custom charts & reports.

To use, import the file (xmlexport.lss) below into a new agent. Set the agent trigger to On Schedule/Never. Security - Run as web user. This is very important otherwise you expose ALL data on your server at the access level of the agent signer. You may want to limit this exposure by editing the agent & removing the querydb parameter, allowing it to only query the database the agent is published in.

Here are a few sample queries I've setup on our server. The sample database is a fictitious list of classes at a university. This agent has the querydb parameter disabled.

This link returns all the documents in the database with the form name = Class.

This link returns the same documents via a view named All.

This link illustrates the ability to use an @Formula in place of a field.

This link returns all classes offered in the Econ department.

This link returns all classes offered that are 5 credits.

This link returns all classes offered which meet on Thursday.

In my next post, I'll publish a simple Excel template I put together which allows end-users to build the search queries & what fields to return with no coding.

File: