Notes Reporting

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 notes.net/searchdomino.com). 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"
'WIN32
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
'WIN16
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
      Else
            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
      Else
            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
                  WBook.Close
                  Handle.DisplayAlerts = True
                  Handle.Quit
                  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
     'Wsheet.Delete
     'WBook.Worksheets.Add
     '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  
      Wsheet.Cells.ClearContents
      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)
      Wend
      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)
            Next
            i = i + 1
            Set doc = view.GetNextDocument(doc)
      Wend
'Create column headings in Excel spreadsheet  
      j = 0
      Do
            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
            Wsheet.Columns(k).Autofit
      Next
'Terminate the progress bar
      Call pb.PBDelete
'Close Excel application
      Handle.ActiveWorkbook.SaveAs strFileName
      Handle.ActiveWorkbook.Close
      Handle.DisplayAlerts = True
      Handle.Quit
      Set Wsheet=Nothing
      Set Wbook=Nothing
      Set Handle=Nothing
      Msgbox "Export to Excel completed successfully.",0+64,"Export Complete"
      Exit Sub
generalerrorhandler:
      Msgbox "Error " & Err() & ": " & Error(),0+64,"Error"
      If Not (pb Is Nothing) Then
            Call pb.PBDelete
      End If
      WBook.Close
      Handle.DisplayAlerts = True
      Handle.Quit
      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.
Varvoura