Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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"
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.

  • 2
  • 2
1 Solution
Sjef BosmanGroupware ConsultantCommented:
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.
varvouraAuthor Commented:

How would you use Crystal Reports to create great reports from  a notes database?
varvouraAuthor Commented:
Thank you!!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now