Lotus Notes is great at what it does. However, there are things it needs help doing like reporting and sophisticated formatting for printing. Excel, with its cool charts, pivot tables, and familiar user interface, is a more commonly used tool for reporting on numeric data. Word, which may be the most used application on the planet, is often the preferred application for creating documents. By linking Notes data with Excel and Word, we overcome the challenges of using Notes alone. This article will show in detail how to create great solutions with tools you most likely already have — Office and Notes.

There are lots of examples of linking Office applications with Notes using COM. I am not going to talk about COM. Instead, I will use HTTP to make our connections. I start with an incredibly simple and powerful demo of Excel and Notes/Domino with zero code and can be done in one minute. Don't let your boss know how easy it is.

Excel 2007 and later versions have a very useful feature for linking web content to tables in spreadsheets. Domino provides web publishing of Notes data as a built-in feature. We are just going to put these built-in features together. Here is my sample Notes web view URL.

You will want to copy this URL to your clipboard and go to an empty Excel spreadsheet. There you use the ribbon menu to select "From Web" under the "Data" tab in the "Get External Data" Group.

In the "New Web Query" dialog, paste our URL in the "Address" text box and click the "Go" button. The Domino web page containing the Products by ID View appears along with yellow and black arrows pointing to sections of the web page. Select the arrow pointing to the HTML table holding the view data. Note the table becomes highlighted. Click the "Import" button.

An "Import Data" dialog appears to ask where in the spreadsheet you want to place the table data. Just click "Ok" to accept the default location.

Our spreadsheet now has Notes data in it.

Better yet, the data continues to be linked to Domino. To prove this, simply delete some of the cells in the spreadsheet. Then on the ribbon's "Data" tab in the "Connections" group, pull down the "Refresh" icon and select refresh.

Behind the scenes, the refresh causes Excel to call the URL for the web View and re-reads the HTML table data and maps it into the spreadsheet. To see the options for our link to Domino, again on the ribbon's "Data" tab, in the "Connections" group, select "Properties".

The "External Data Range Properties" dialog shows many options like refreshing every so many minutes or when the spreadsheet is opened. Explore these options.

Before we talk about the cool reporting features of Excel 2010, let's fix a major problem with using this method. In our example, we only got one page of documents from the view. Remember we are reading an HTML table which represents just one "page" of the view. In this case that is 31 documents. This database has 50 documents in the view. The good news is the fix is easy. Just put "&count=-1" at the end of our view's URL. (i.e: http://www.msdomino.net/products.nsf/products by ID?openview&count=-1 ) This tells Domino to make the "Page" size the maximum length which is 1,000 by default. You can set the maximum to a larger number with a server setting. Mine is set to 10,000; if you want to try it, I have a database with nearly 2,000 documents in the web view.

The live Notes data is being presented in Excel, so let's take advantage of this rich client. There are books on how to create cool reports with Excel, but I'm only going to show you a few cool techniques. First we will add a "Pivot Chart". Remember, the chart data is in Notes, and the chart will change as data in our Notes web view changes.

Start by selecting one cell in our data table. Then, using the "Insert" ribbon tab, pull down the "Pivot Table" Icon and select "PivotChart". A "Create PivotTable with PivotChart" dialog appears.

In the dialog, note the "Table/Range" value is already set because our data table had focus when we selected one cell in the table. To set the location of PivotChart, check the "Existing Worksheet" radio button then click on the spreadsheet where you want the PivotChart to be inserted and click "OK".

A wizard presents you with the tools needed to design your PivotChart. Drag the "Location" field to the "Axis Fields" box and the "Description" field to the "? Values" box and see the chart respond.

You can just type the title text and you are done. Save your Excel file and remember where you put it.

The Excel report is pretty compelling. However, reports usually need commentary and that means words. So we are going to take our report to Word. There are many ways to incorporate Excel information into Word. I'm going to show you a very quick and easy method.

In Microsoft Word 2010 on the "insert" ribbon tab find the "Text" group and select the "Object" pull-down.

This brings up the "Object" dialog.

Here we select the "Create from File" tab and browse/select our excel file we saved before. If no boxes are checked, a copy of the Excel file is inserted into the Word document. Readers can click on the content to launch Excel to edit it. By checking the "Link to File" check box, according to the help:

"Inserts the contents of the file into your document and creates a shortcut to the source file. Changes to the source file will be reflected in your document."

If you check this box only, you will see the table data and chart inserted into your Word document. It is linked to the original Excel file. Readers can right-click on the content to edit it in Excel and save the results. You should try it. By checking the second box "Display as Icon", the content is not displayed until the icon is clicked. (You can see this article in a Word2010 doc with the embedded table data here — you need Word 2010 for the Link to File to work).

To recap: In this article, I showed how to easily create living reports on Notes/Domino data in a format that empowers the end user. Yes you need Office 2010 for some of these features. Just think of the value you provide to your organization with such little effort. Extend the value of your existing Notes applications while building your skill set. This is just one example of how Microsoft products can work with Lotus Notes. A quick Bing search will turn up many more. As solution providers, the more tools we have, the better we can meet the needs of our customer. Feel free to use my Domino server used in these examples to try other integration methods.