Introduction
Open Door is a tool for producing management reports, standard documents (invoices, orders), drill-down enquiries and charts. It can output to printers, PDF, HTML, Word and Excel.
Open Door requires an ODBC compliant database. The examples given here are based on SQL Server, but Open Door will work with any ODBC compliant database, including Oracle.
Background
I developed Open Door to support the reporting and 'drill-down' requirements of an accounting application. After hitting several brick walls using commercial reporting tools, I decided to bite the bullet and develop my own. As our company's main business is selling accounting applications, we decided that Open Door would never get the long term attention it deserved as a commercial application, so we decided to release it under an open source license.
In this article, I will give a brief outline of how to use Open Door from a Windows desktop application. If it receives interest from the community, I will follow up with a future article on using Open Door from an ASP.NET application and then more detailed articles on the background to the Open Door logic for laying out reports, an explanation of the grid control, the charting engine, and the database engine.
Using the Code
The package is presented as a series of directories containing the source code for the various components. To open the code, you will need Visual Studio 2008 (The Express version is free and may be downloaded from here). Most of the projects are VB.NET. The PDF exporter and Excel Package are both Open Source C# projects written by other authors. Grateful acknowledgements go to Dr. John Tunnicliffe for the ExcelPackage and Bruno Lowagie and Paulo Soares for iTextSharp. To run the example reports, you will need a SQL Server database (any version from 2000 onwards). SQL Server 2008 Express is free and may also be downloaded from here. The example reports require the pubs database to be installed. Instructions for doing this are in the help file under 'Getting Started', the required script is included in the download package under the SampleDatabaseScripts directory.
The bin directory contains the executables. There are no special installation requirements - it is 'xcopy deployable'.
OpenDoorMenu is a sample project illustrating how to embed Open Door within an application. A second project, OpenDoorMenuMDI illustrates how to embed OpenDoor within a multiple document interface application.
Run OpenDoorMenu.exe in the bin directory and press F1 for instruction to gain familiarity with creating and running reports as an end user.
The menu program stores and loads connection settings and uses a helper DLL, odReportCaller to invoke Open Door itself.
The steps to starting Open Door are:
- Acquire connection settings
- Create an instance of the Open Door database engine
- Call the
odReportCaller
dim ODBCSource$ = "SourceName"
dim UserName$ = ""
dim PassWord$ = ""
dim dbName$ = "pubs"
dim hdb as New nbfDB.HDBC
dim repCaller as odReportCaller.modHost
hdb.logon(ODBCSource$, UserName$, PassWord$)
If hdb.handle <= 0 Then
hdb.Dispose()
hdb = Nothing
MsgBox("Database Logon Failed")
Return
End If
If Not String.IsNullOrEmpty(dbName$) Then
hdb.execute("use " & dbName$)
End If
repCaller = New odReportCaller.modHost(hdb, Me)
repCaller.OpenReport()
The Report Caller provides an interface to the two main Open Door components:
- The report engine (code in ReportComponents\nbfSqlReporter)
- The report edit application (code in ReportComponents\nbfBrowser)
The report engine contains all the logic for laying out and printing the report. The Edit application provides the interface for creating and editing reports.
General Architecture
An Open Door document has three main components:
- A parameters collection
- A SQL sources collection
- A controls collection
Parameters are used to set the scope of the report and may be embedded in SQL statements and report content. For example, an invoice document would require a parameter to pass the invoice number.
A SQL source represents a result set retrieved from a database. SQL sources are defined by entering SQL statements (which may contain embedded parameters).
Open door controls are similar to Visual Studio controls and are used to build the report. Some controls may be bound to a SQL source.
In the paragraphs that follow, I will use illustrative snippets of code. These are not meant to be self-contained, cohesive code blocks. Complete working source code is available from the downloadable zip file.
Report data is held in the report engine (nbfSqlReporter). The nbfBrowseRep class is used to represent a report. The report structure is held in the nbfBrowseFormInfo class.
Public Class nbfBrowseRep
Private pvBFInfo As New nbfBrowseFormInfo
...
Public Class nbfBrowseFormInfo
Implements ISerializable
private pvCaption as String = ""
private pvRepParams as New nbfRepParams
Private pvSQLSources As New nbfSqlSources
Private pvSQLBrowseCtrls As New nbfBrowseCtrls
private pvPrinterName as string = ""
private pvPrinterOrientation as string = ""
...
Controls added to the report may represent various items, such as labels, database fields, lines, images or charts.
Each item is represented by a nbfBrowseCtrl object. The CtlType determines how it is to be displayed.
<serializable()> Public Class nbfBrowseCtrl
Implements ISerializable
Private pvName As String = ""
Private pvCtlType As String = ""
Private pvLocation As New System.Drawing.Point
Producing the report is then a matter of executing the output code for each control.
Some types of control require special handling.
Report and Grid controls are bound to result sets. If the space required to print a report or grid control exceeds the space available, it causes the document to flow onto a new page.
Another consideration is that some controls depend on the content or position of another control. These must be output last.
The code below is used to print a report page. The procedure is called repeatedly until it returns false:
Function PrintPage(ByVal ev As Graphics, ByVal pi As RectangleF, _
Optional ByVal DrawPage As Boolean = True) As Boolean
Dim mpg As Boolean = False
Dim mpgflag As Boolean = False
Dim bc As nbfBrowseCtrl
Dim tLimit As Integer = 100
Dim ThisPages As Integer = 0
For Each bc In pvBFInfo.SQLBrowseCtrls
bc.GrowthOffset = 0
Select Case bc.CtlType
Case "Grid", "Report"
If CurrentPrintPage > 1 And (bc.FlowThrough And bc.PrintReqMorePages) _
And Not bc.ResetOnEachPage Then
bc.PrvRecPos = bc.NextRecordPos
bc.PrvSecPos = bc.NextSectNo
bc.PrvRecPrinted = 0
For Each rs As nbfSQLRepSection In bc.RepSections
For Each bc2 As nbfBrowseCtrl In rs.SectionCtrls
If bc2.RecPrinted < bc.NextRecordPos Then
bc.PrvRecPrinted += 1
End If
Next
Next
End If
If CurrentPrintPage = 1 Or (bc.FlowThrough And bc.PrintReqMorePages) _
Or bc.PrintWhenBlank Or bc.ResetOnEachPage Then
ThisPages = PrintRepPage(mpg, bc, DrawPage, ev, pi, _
PrintPageCount, pvBFInfo.FormSize)
End If
If CurrentPrintPage > 1 And _
(bc.FlowThrough And bc.PrintReqMorePages) Then
If bc.PrvRecPos = bc.NextRecordPos And bc.PrvSecPos = _
bc.NextSectNo And bc.CurrRecPrinted = bc.PrvRecPrinted Then
bc.cLimit += 1
If bc.cLimit = tLimit Then
ShowMessage(DBC, "Not enough room for record " & _
CStr(bc.NextRecordPos))
Return 0
End If
Else
bc.cLimit = 0
End If
End If
End Select
If bc.RichFormat Then
ThisPages = PrintRTFBox(ev, True, bc, pi)
If ThisPages > 0 Then
mpg = True
End If
End If
If mpg Then
mpgflag = True
End If
Next
For Each bc In pvBFInfo.SQLBrowseCtrls
Select Case bc.CtlType
Case "Grid", "Report", "Amalgum"
Case Else
If Not bc.RichFormat Then
PrintBrowseCtrl(bc, ev, pi, pvBFInfo.FormSize, _
DrawPage, False, False, "")
End If
End Select
Next
For Each bc In pvBFInfo.SQLBrowseCtrls
Select Case bc.CtlType
Case "Amalgum"
PrintBrowseCtrl(bc, ev, pi, pvBFInfo.FormSize, _
DrawPage, False, False, "")
End Select
Next
Return mpgflag
End Function
Storing and Retrieving Documents
The nbfBrowseFormInfo class is serializable and implements the ISerializable interface.
Documents are stored and retrieved by serializing to disk and deserializing from disk:
Dim CtlFileStream As Stream = File.Create(tCtlSaveName)
Dim serializer As New SoapFormatter
serializer.Serialize(CtlFileStream, pvBFInfo)
CtlFileStream.Close()
Dim myFileStream As Stream = File.OpenRead(LoadName)
Dim deserializer As New SoapFormatter
pvBFInfo = CType(deserializer.Deserialize(myFileStream), nbfBrowseFormInfo)
In version one of the .NET Framework, optional serialization was not supported. I used the ISerializable interface to produce a version-tolerant serializer. Any mis-matched properties in the serialized file are ignored.
Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)
If info.MemberCount > 0 Then
For Each se As SerializationEntry In info
Try
Select Case se.Name
Case "RepParams"
Dim co As nbfRepParams = se.Value
For Each ci As nbfRepParam In co
pvRepParams.Add(ci)
Next
Case "SQLSources"
Dim co As nbfSqlSources = se.Value
For Each ci As nbfSqlSource In co
pvSQLSources.Add(ci)
Next
Case "SQLBrowseCtrls"
Dim co As nbfBrowseCtrls = se.Value
For Each ci As nbfBrowseCtrl In co
pvSQLBrowseCtrls.Add(ci)
Next
Case "SavedSettings", "PrinterSettings", _
"PrinterName", "PrinterOrientation", "PrinterCopies"
Case Else
CallByName(Me, se.Name, CallType.Set, se.Value)
End Select
Catch
End Try
Next
If pvBrowseStyle = "R" Then
For Each se As SerializationEntry In info
Try
Select Case se.Name
Case "PrinterSettings", "PrinterName", _
"PrinterOrientation", "PrinterCopies"
CallByName(Me, se.Name, CallType.Set, se.Value)
End Select
Catch
End Try
Next
End If
End If
pvSettingsLoaded = False
Exit Sub
End Sub
Public Sub GetObjectData(ByVal info As _
System.Runtime.Serialization.SerializationInfo, _
ByVal context As System.Runtime.Serialization.StreamingContext) _
Implements System.Runtime.Serialization.ISerializable.GetObjectData
xSetSerial(Me, info, context)
End Sub
Private Sub xSetSerial(ByVal Host As Object, ByVal info As _
System.Runtime.Serialization.SerializationInfo, _
ByVal context As System.Runtime.Serialization.StreamingContext)
Dim t As Type
Dim mi As PropertyInfo
Dim ov As Object
t = Host.GetType
Dim pi As PropertyInfo() = _
t.GetProperties((BindingFlags.Instance Or BindingFlags.Public))
For Each mi In pi
Try
If pvBrowseStyle <> "R" And (mi.Name = "PrinterSettings" _
Or mi.Name = "PrinterName" Or mi.Name = "PrinterOrientation" _
Or mi.Name = "PrinterCopies") Then
Else
Select Case mi.Name
Case "Parent"
Case Else
If mi.CanWrite Or mi.PropertyType.BaseType _
Is GetType(nbfBrowseCol) Then
Try
ov = CallByName(Host, mi.Name, CallType.Get)
If Not ov Is Nothing Then
info.AddValue(mi.Name, ov, ov.GetType)
End If
Catch
End Try
End If
End Select
End If
Catch
End Try
Next mi
End Sub
The SOAP serializer is a little out of fashion, but because it produces an ASCII (human readable) file, it is a friendly format, and I have decided to stick with it for the time being.
Report Editor Interface
The report editor is a Windows Form containing a ContentPanel control.
The display is constructed by iterating through the controls in the nbfFormInfo class and drawing them on a bitmap, which is then output to the content panel control.
The following is part of the code that draws the bitmap:
g = Graphics.FromImage(_backBuffer)
Dim bc As nbfBrowseCtrl
Dim pi As New RectangleF
Dim rc As RectangleF
Dim sz As New Size
Dim c As Control
Dim spt As Point
CheckGraphSet()
spt.X = 0
spt.Y = 0
rc.X = 0
rc.Y = 0
rc.Width = _backBuffer.Width
rc.Height = _backBuffer.Height
Dim bkgc As Color
If pvBrowseRep.BFInfo.BrowseStyle = "B" Then
bkgc = System.Drawing.SystemColors.Control
Else
bkgc = System.Drawing.Color.WhiteSmoke
End If
brush = New SolidBrush(bkgc)
g.FillRectangle(brush, rc)
rc.X = spt.X
rc.Y = spt.Y
pi = rc
sz.Width = rc.Width
sz.Height = rc.Height
Dim rci As New Rectangle
Dim rcb As New Rectangle
Dim rcii As New Rectangle
For Each bc In SelectedControls
rci.X = CInt(bc.Left + spt.X)
rci.Y = CInt(bc.Top + spt.Y)
c = GetControlFromBC(bc.Name)
If c Is Nothing Then
rci.Width = bc.Width
rci.Height = bc.Height
Else
rci.Width = c.Width
rci.Height = c.Height
End If
rcii.X = rci.X
rcii.Y = rci.Y
rcii.Width = rci.Width
rcii.Height = rci.Height
rci.Inflate(2, 2)
rcb.X = rci.X
rcb.Y = rci.Y
rcb.Width = rci.Width
rcb.Height = rci.Height
rcb.Inflate(1, 1)
ControlPaint.DrawGrabHandle(g, rci, True, True)
ControlPaint.DrawSelectionFrame(g, True, rci, rcii, pvAppFrmSI.FormBackColour)
Next
For Each bc In pvBrowseRep.BFInfo.SQLBrowseCtrls
bc.GrowthOffset = 0
bc.RTFCharsPrinted = 0
Select Case bc.CtlType
Case "Report"
If pvShowSubReps Then
rc.X = spt.X
rc.Y = spt.Y
rc.Width = _backBuffer.Width
rc.Height = _backBuffer.Height
pi = rc
sz.Width = rc.Width
sz.Height = rc.Height
pvBrowseRep.PrintSubRep(bc, g, pi, sz, "D")
ElseIf bc.CtlType = "Report" Then
If Not (bc.OrderSet = False And bc.Top = 0) Then
pvBrowseRep.DrawCtlLabel_
(bc, spt, g, LineDrawPen, pvAppFrmSI.FormFont)
End If
End If
Case "Grid"
If pvShowSubReps Then
If pvBrowseRep.BFInfo.BrowseStyle <> "B" Then
rc.X = spt.X
rc.Y = spt.Y
rc.Width = _backBuffer.Width
rc.Height = _backBuffer.Height
pi = rc
sz.Width = rc.Width
sz.Height = rc.Height
pvBrowseRep.PrintSubRep(bc, g, pi, sz, "D")
End If
Else
If Not (bc.OrderSet = False And bc.Top = 0) Then
pvBrowseRep.DrawCtlLabel_
(bc, spt, g, LineDrawPen, pvAppFrmSI.FormFont)
End If
End If
Case "Field", "Label", "Total", "Image", "Amalgum", "Chart"
Dim StartXPos, StartYPos, EndXPos, EndYPos As Integer
StartXPos = spt.X
StartYPos = spt.Y
Dim dblLineWidth As Integer = 2
EndXPos = spt.X + bc.Width
EndYPos = spt.Y + bc.Height
rc.X = spt.X
rc.Y = spt.Y
rc.Width = _backBuffer.Width
rc.Height = _backBuffer.Height
pi = rc
sz.Width = rc.Width
sz.Height = rc.Height
If not (bc.CtlType <> "Image" And pvBrowseRep.BFInfo.BrowseStyle = "B") Then
If bc.RichFormat Then
Dim pih As RectangleF
Dim pc As System.Drawing.Printing.PrinterUnitConvert
pih.Height = pc.Convert(pi.Height * 10, _
PrinterUnit.TenthsOfAMillimeter, _
PrinterUnit.ThousandthsOfAnInch) * 10
pih.Width = pc.Convert(pi.Width * 10, _
PrinterUnit.TenthsOfAMillimeter, _
PrinterUnit.ThousandthsOfAnInch) * 10
pvBrowseRep.PrintRTFBox(g, True, bc, pi)
sz, True, False, False, "", "D")
Else
pvBrowseRep.PrintBrowseCtrl_
(bc, g, pi, sz, True, False, False, "", "D")
End If
End If
End Select
Next
Exporting Data
Documents are exported via a two stage process.
The report engine is used to layout the report. Instead of sending the information directly to an output device, a PrintDocInfo object is created. This contains all the information required to print each item in the final report.
The export routine iterates through the items in the PrintDocInfo object, writing out the required syntax (PDF, RTF). The PDF export utilises the excellent iText library.
Some export options require an external program. If a native interface to the external program is available, it is used. Otherwise the default program registered for that file type with the operating system is used.
For example, the Word export option first tries to start Word, but otherwise uses a general process. This method has the advantage that it will still work in cases where Word is not installed, but an alternative RTF editor is available.
If PDI.ExpRtf(full_file$, True) Then
Dim ldOK As Boolean = False
Dim exl As Object
Try
exl = CreateObject("Word.Application")
exl.documents.open(full_file$)
ldOK = True
Catch
End Try
If ldOK Then
Try
exl.ActiveWindow.View.Type = 3
Catch ex As Exception
End Try
exl.visible = True
exl = Nothing
Else
Dim myProcess As New Process()
myProcess.StartInfo.FileName = full_file$
myProcess.Start()
End If
End If
Summary
In this brief introduction, I have only been able to skim the surface of the Open Door program.
I hope that I have sparked your interest enough for you to try the program, see what it can do, then load up the source and start making it do more. Please feel free to integrate it into your own applications as the starting point for your own reporting library.
History
- 24th January, 2009: First release
