Genii Weblog

Working on a reports sample db for Midas

Wed 21 Apr 2004, 11:57 PM

by Ben Langhinrichs
In writing about 10 Ways to Use Midas #6, which covers report writing, I realized that while I have helped lots of customers write reports with Midas, and have used Midas to generate Fix List reports for the Business Partner Forum, I have never published a sample database that was explicitly aimed at report writing.  What an oversight!  

To start, I looked around and found a sample I started writing, which generates some very cool tabbed table reports.  The date on the agents was Sept. 2000, which means that this quite useful sample has been sitting there unused for three and a half years.  <Sound of head banging on wall repeatedly>  Oh well, better late than never.

I left that first report agent in with only a couple of minor modifications, but have also added a bar chart report which is pretty awesome.  The bars are colored, and each is also a hotspot to show a different value, so the chart is both highly visual and somewhat interactive.  It only took about an hour and a half to think up and execute, but I think by using the technique you could create a similar report in much less time.  I have published the code for this chart agent below, but to try it out you will need to download the Midas Report sample when it is ready (which I will mention here).   Take a look at the code first and see if it is clear what is happening:

Agent code
Sub Initialize
   ' *** Notes class objects
   Dim ws As New NotesUIWorkspace
   Dim session As New NotesSession
   Dim db As NotesDatabase
   Dim doc As NotesDocument
   Dim view As NotesView
   Dim viewcollection As NotesViewEntryCollection
   Dim viewentry As NotesViewEntry
   ' *** Midas class objects
   Dim rtitem As New GeniiRTItem
   Dim rtchunk As GeniiRTChunk
   Dim rtCell As GeniiRTChunk
   ' *** Miscellaneous processing variables
   Dim state As Integer
   Dim state_name As String
   Dim colors(2) As String
   Dim fonts(2) As String
   Dim count As Integer
   Dim qty As Single
   Dim props As String
   ' *** Set up the report document and the view entry into the view
   Set db = session.CurrentDatabase
   Set doc = New NotesDocument(db)
   doc.Form = "Report"
   doc.Subject = "Order Quantity by State"
   Set view = db.GetView("All Orders\by State")
   Set viewcollection = view.AllEntries
   Set viewentry = viewcollection.GetFirstEntry
   ' *** The colors represent the alternating bar colors
   ' *** and the fonts are for the text on the bars
   colors(0) = "red"
   colors(1) = "yellow"
   colors(2) = "blue"
   fonts(0) = "white"
   fonts(1) = "black"
   fonts(2) = "white"      
   Call rtitem.CreateBackend(doc.Handle, "Body", True)
   If Not rtitem.IsConnected Then Exit Sub
   rtitem.CacheRecords = True
   Set rtchunk = rtitem.DefineChunk("Everything")
   Call rtchunk.AppendText(Chr(0) & "The following bar chart represents the total number of units " & _
   "sold per state.  Click on each bar to see the invoice total for that state." & Chr(0) & Chr(0))
   state = 1
      state_name = viewentry.ColumnValues(0)
      If state = 1 Then
         Call rtchunk.AppendTable(1, 2, "TableWidth=FIXED CellBorder=None ", _
         |Text='| & viewentry.ColumnValues(0) & |' TextFont="Default Sans Serif 10pt" Width=0.3|,  _
         |Text=' ' TextFont="Default Sans Serif 10pt" Width=6.5| )
         Set rtchunk = rtitem.DefineChunk("Table 1")      
         Call rtchunk.AppendRow(False, 0, |Text=' ' TextFont="Courier 2pt"|,  |Text=' ' TextFont="Courier 2pt"|)
         Call rtchunk.AppendRow(False, 0, |Text='| & state_name & |' TextFont="Default Sans Serif 10pt"|,  _
         |Text=' ' TextFont="Default Sans Serif 10pt"|)
      End If
      state_name = viewentry.ColumnValues(0)
      count = 0
      qty = 0.0
         Set viewentry = viewcollection.GetNextEntry(viewentry)
         If viewentry Is Nothing Then
            Exit Do
         Elseif viewentry.ColumnValues(0) <> state_name Then
            Exit Do
         Elseif Not viewentry.IsCategory Then
            If viewentry.ColumnValues(4) <> "" Then
               count = count + Cint(viewentry.ColumnValues(4))
               qty = qty + viewentry.Document.InvoiceTotal(0)
            End If
         End If
      Set rtCell = rtchunk.DefineSubChunk("Row " & Cstr((state*2)-1) & "; Inside Column 2")
      Call rtCell.AppendHotspot("Popup", |"Invoice total of | & Format(qty, "Currency") & |"|, "", " ")
      ' *** Calculate the properties for the graphic, including width which is related to the count
      props = "GraphicWidth=" & Cstr(count*3) & " GraphicHeight=15 Caption=" & Cstr(count) & " & _
      CaptionPosition=Center & CaptionFont=" & fonts(state Mod 3)
      Set rtCell = rtchunk.DefineSubChunk("Row " & Cstr((state*2)-1) & "; Column 2; Hotspot 1; Text 1")
      Call rtCell.InsertResource("Image", colors(state Mod 3) + ".gif", props)
      state = state + 1
   Loop Until viewentry Is Nothing
   Call rtitem.Commit()
   ' *** If the following line were uncommented, the reports would be saved into the Reports view
'   Call rtitem.Save()
   Call ws.EditDocument(False, doc)
End Sub

What the result looks like
Inline JPEG image

Oh, and before anyone asks, you don't need Midas to view the report, just to generate it, so one person can create reports for many others.

Copyright 2004 Genii Software Ltd.

What has been said:

No documents found