Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / Visual Basic

Open Door - Reporting, Charts, Enquiry Drill-Downs

Rate me:
Please Sign up or sign in to vote.
4.37/5 (11 votes)
2 Feb 2009CPOL6 min read 39.1K   2K   59   5
A utility for generating user editable reports, charts, documents, enquiries
Image 1

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:

  1. Acquire connection settings
  2. Create an instance of the Open Door database engine
  3. Call the odReportCaller
VB.NET
dim ODBCSource$ = "SourceName"
dim UserName$ = "" ' using Windows Authentication
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.

VB.NET
Public Class nbfBrowseRep
Private pvBFInfo As New nbfBrowseFormInfo
...

<serializable />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.

VB.NET
<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:

VB.NET
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"
                'no action
            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:

VB.NET
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.

VB.NET
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"
                        'no action
                    Case Else
                        CallByName(Me, se.Name, CallType.Set, se.Value)
                End Select
            Catch
                'no action
            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
                    'no action
                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
                'no action
            Else
                Select Case mi.Name
                    Case "Parent"
                        'do not serialize
                    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
                                'no action
                            End Try
                        End If
                End Select
            End If
        Catch
            'no action
        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:

VB.NET
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
                    'cvt to 100ths of an inch
                    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.

VB.NET
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      'Print view
        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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionGreat article and very informative. Is there an update or ASP.NET information available? Pin
Randy Friend24-Aug-12 5:16
Randy Friend24-Aug-12 5:16 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey6-Mar-12 21:47
professionalManoj Kumar Choubey6-Mar-12 21:47 
QuestionWithout SQL server? Pin
Niemand259-Feb-09 23:57
professionalNiemand259-Feb-09 23:57 
AnswerRe: Without SQL server? Pin
Ian Richards10-Feb-09 2:48
Ian Richards10-Feb-09 2:48 
GeneralLooks great! Pin
Shane Story9-Feb-09 10:07
Shane Story9-Feb-09 10:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.