Genii Weblog
Working on a reports sample db for Midas
Wed 21 Apr 2004, 11:57 PM
Tweetby 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
Do
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")
Else
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
Do
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
Loop
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
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: