Notes Reporting

Posted on 2006-05-02
Last Modified: 2013-12-18
Hi there,

I have a critical database which I am trying to create various reports for in Excel as it is the client's requirement. Currently, I have a function that exports data (which I picked up from This function exports data neatly from a Notes database directly into a spreadsheet, but I need to format this function so that it does the following:

1. Categorises each month's data, so that when I new month starts, I can view that clearly as a heading in a row in the spreadsheet.

2. Add a chart(pie or whatever type of chart) to the exported spreadsheet to reflect the data in that spreadsheet.

Is this possible?

Here's the function which I am sure you all have been exposed to already:

Agent Information
Name:      Export to Excel
Last Modification:      28/02/2005 04:35:49 PM
Comment:      [Not Assigned]
Shared Agent:      Yes
Type:      LotusScript
State:      Enabled
Trigger:      Manually From Agent List
Acts On:      All new and modified documents since last run
LotusScript Code:
Option Public
Option Explicit
Use "ProgressBar"
Declare Function W32_NEMGetFile Lib "nnotesws" Alias "NEMGetFile" ( wUnk As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer
Declare Function W16_NEMGetFile Lib "_nem" Alias "NEMGetFile" ( wUnk As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer
'use nnotesws for Win95 and WinNT, and _nem for Win16
Sub Initialize
      On Error Goto generalerrorhandler
      On Error 213 Resume Next
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim viewname As String
      Dim doc As NotesDocument
      Dim column As NotesViewColumn
      Dim nbcol As Integer
      Dim i As Long
      Dim j As Integer
      Dim k As Integer
'File Dialog
      Dim strFileName As String*256
      Dim strTitle$
      Dim strFilter$
'Excel Application
      Dim Handle As Variant
      Dim WBook As Variant
      Dim Wsheet As Variant
      Set db = session.CurrentDatabase
      viewname = session.GetEnvironmentString("NameOfView")
      Set view = db.GetView(viewname)
'File Dialog
      strFileName = Chr(0)
      strTitle$ = "Select Excel file to export to."
      strFilter$ = "MS Excel Files|*.xls|All Files|*.*|" 'Use this format for ANY file type
      If IsDefined ("WIN32") Then
            If W32_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
                  strFileName = strFileName & |"| 'We need to do this because the return is a NULL terminated string.
            Else 'The user chose to Cancel the operation so exit the subroutine
                  Exit Sub
            End If
      Elseif IsDefined ("WIN16") Then
            If W16_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
                  strFileName = strFileName & |"|
            Else 'The user chose to Cancel the operation so exit the subroutine
                  Exit Sub
            End If
            Msgbox "Cannot load file dialog window on this operating system." & Chr(13) & "Process Terminated",0+64,"Error"
            Exit Sub
      End If
'Open Excel Application
      Set Handle = CreateObject("Excel.Application")
      Set WBook = Handle.Workbooks.Open(strFileName)
      If Err = 213 Then
            Set WBook = Handle.Workbooks.Add
            If Msgbox ("The export to Excel is about to begin.  All existing spreadsheet contents will be overwritten!" & Chr(13) & Chr(13) & "Do you wish to proceed?",4+48,"Export to Excel") = 7 Then
                  Handle.DisplayAlerts = True
                  Set Handle = Nothing
                  Exit Sub
            End If
      End If
     'Handle.Visible = True 'Uncomment if you wish Excel to be seen
      Set Wsheet = WBook.Application.Workbooks(1).Worksheets(1)
      Handle.DisplayAlerts = False
'Clear contents of worksheet - Method 1...
  'Delete and then re-add the worksheet itself
     'Set Wsheet = WBook.Application.Workbooks(1).Worksheets(1)
'Clear contents of worksheet - Method 2...
  'By not specifying a range for the Cells property, all the contents in all the cells on the worksheet will be erased  
      nbcol = Ubound(view.Columns) 'Determine the number of columns in the view
'Progress Bar
      Dim doc2 As NotesDocument
      Dim p As Long    
'Create a count of all the documents in this view.  This will be used to set the upper bound for the Progress Bar
      Set doc2 = view.GetFirstDocument
      p = 0
      While Not (doc2 Is Nothing)
            p = p + 1
            Set doc2 = view.GetNextDocument(doc2)
      Dim pb As New LNProgressBar(True)
      Call pb.SetText("Exporting View to Excel Spreadsheet." & Chr(13) & Chr(13),"Please wait...")
'We set the range of the Progress Bar to p elements
      Call pb.SetProgressRange(p)  
'Begin looping through the documents in the view and add them into the Excel worksheet starting on row 3. Change the variable "i" to start on a different row.
  'Remember that the column headings will occupy row one.
      i = 3
      k = 0
      Set doc = view.GetFirstDocument
      While Not (doc Is Nothing)
            Call pb.SetProgressPos(i - 3)
            For k = 0 To nbcol 'Populate additional rows and columns in Excel spreadsheet from Notes documents in view
                  Wsheet.Cells(i,k + 1).Value = doc.ColumnValues(k)
            i = i + 1
            Set doc = view.GetNextDocument(doc)
'Create column headings in Excel spreadsheet  
      j = 0
            Set column = view.Columns(j)
            Wsheet.Cells(1, j + 1).Value = column.Title
            j = j + 1
      Loop Until j = nbcol + 1
'Perform formatting in the Excel spreadsheet
      Wsheet.Rows(1).Font.Bold = True
      Wsheet.Rows(1).Font.Size = 8
      Wsheet.Columns(7).NumberFormat = "################" 'Formats the seventh column to display the account numbers properly. Comment out or modify for your specific needs.
       'Other number formats can include: "$#,##0.00" or "hh:mm:ss".  See the "Vbaxl8.hlp" help file for more information.
      For k = 1 To nbcol + 1
'Terminate the progress bar
      Call pb.PBDelete
'Close Excel application
      Handle.ActiveWorkbook.SaveAs strFileName
      Handle.DisplayAlerts = True
      Set Wsheet=Nothing
      Set Wbook=Nothing
      Set Handle=Nothing
      Msgbox "Export to Excel completed successfully.",0+64,"Export Complete"
      Exit Sub
      Msgbox "Error " & Err() & ": " & Error(),0+64,"Error"
      If Not (pb Is Nothing) Then
            Call pb.PBDelete
      End If
      Handle.DisplayAlerts = True
      Set Handle = Nothing
      Exit Sub
End Sub
Query 2

Is there any free reporting tools for lotus notes which may meet my above requirements?

If not, is there a paid service, where from and what's the apporximate cost?

Thank you in advance.

Question by:varvoura
    LVL 46

    Expert Comment

    by:Sjef Bosman
    1. The categories-thing might be easily done when you create a categorized view, and you use a NotesViewNavigator with NotesViewEntry objects instead of the NotesView and NotesDocuments.
    2. If you want to know how to add a pie chart, then create the spreadsheet first, switch on macro recording, create your pie chart and stop recording again. In VBA editor mode (Alt-F11 or something), you'll see exactly the code required to produce a chart. You can almost copy the code to Notes, "all" you have to do is make it spreadsheet- and position-independent. Which may be a lot, unfortunately.
    3. Free reporting tools? Not even Excel is free. OpenOffice is "free". Other reporting tools are NotesToPaper, Midas, Crystal Reports, e:PDF, etc.

    Author Comment


    How would you use Crystal Reports to create great reports from  a notes database?
    LVL 46

    Accepted Solution


    Author Comment

    Thank you!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This is an old article, please see an updated version of this article, located here:
    Article by: Rob
    Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now