Creating On-Demand Cost Estimating Books with ActiveReports

Introduction

My name is Jean Barnett, and I have been an active Visual Basic Developer since Visual Basic 1.0 was released. My history with ActiveReports goes back to ActiveReports 1.0 when it was sold by Data Dynamics and delivered on floppy disks.

Problem Definition

Business Challenges

SDSI has been working with HomeTech Publishing (formerly HomeTech Information Systems, Inc.) since 1995. We developed a Windows-based desktop application called HomeTech ADVANTAGE and licensed it to HomeTech, incorporating their residential real estate and remodeling cost data into the application.

For many years, HomeTech maintained the database of costs and eventually began printing on-demand versions modified for over 200 local areas across the country.

HomeTech also offers printed versions of their cost data that were originally offered for estimators to prepare estimates manually. Still available today, these books are popular as reference manuals or field guides by residential estimators, even those who have long been computerized. The books are in their 51st year of publication.

Editions published prior to computerizing the data contained static costs for each item. The estimator also received a Modification Index that could be fastened to the cover that contained multipliers to manually adjust the printed costs. We created the publications using the WordPerfect word processing program.

Between 1990 and 2000, we created databases and developed a Visual Basic program for HomeTech that populated the WordPerfect documents using insertion codes as shown below:

The program read through each WordPerfect document line by line, character by character, first identifying the database item code, retrieving the database item for that code and replacing the //M (material), //L (labor), //T (total), and //P (price) place holder with the corresponding database field. As each item was processed it was adjusted by a local area modifier prior to printing.

We used this program to generate the PDF files twice a year beginning in the early 2000s. In 2010 the company changed hands and became HomeTech Publishing. By that time the original authors of the software were no longer available to maintain the program. In addition, the static nature of the WordPerfect documents made it virtually impossible to add new items without being able to make adjustments to the program.

While the program was still functional, it became less and less compatible with newer operating systems. By 2014 it became apparent that it was time to develop a new application to replace it.

Development Challenges

In approaching this project, it made sense to develop a program that would not only generate the books dynamically without the use of a word processing program but would also streamline the process of updating the estimating software from the same database.

Rather than starting with the data in the original book creation program, we decided to create the books using the version of the database in the ADVANTAGE program. The challenge was to make the books look as close as possible to previous versions for existing users of the books.

Alternatives Considered

As developers working with Microsoft Visual Studio, we have tried different printing tools over the years. At one point Microsoft bundled Crystal Reports with Visual Studio but withdrew it in subsequent versions. We originally released the ADVANTAGE program in Visual Basic 4 with ActiveReports 1.0. After moving to Visual Basic 6 (we skipped 5), we upgraded to ActiveReports 2.0. We also included some reporting done with ComponentOne reporting tools in later releases that were compatible with Visual Basic 6.

Prior to starting on the new book program, we compared the capabilities of the current versions of Crystal Reports, ComponentOne Reports and ActiveReports. ActiveReports was the obvious choice for this project.

Why ActiveReports?

Having worked more with ActiveReports over the years than the other products, we had extensive experience with it and had little or no problems with it.

The feature that was the deciding factor for me was the ability to easily combine multiple reports into one document, which was exactly what we needed to create a single-book PDF with multiple page formats.

Creating the Master Cost Book Application

Environment

We created the Master Cost Book application using Microsoft Visual Studio 2012 with Visual Basic. We created the back-end database in SQL Server Express 2012. We employed the following third-party tools in the application:

• ComponentOne Studio Enterprise for Winforms
• TxText Control
• ActiveReports

Screen Shots

This is the login screen for accessing the Master Cost Book application.

We use the initial application screen to maintain item cost data, add new items, delete items, and to change descriptions, specifications, pricing, and other information as needed.

We can preview book files individually as shown below.

When the book files are ready, we create book PDF files in batches.

"The batch process uses ActiveReports to generate and combine reports into the ActiveReports WinForms Viewer, then uses the viewer's printer function to generate PDF output. Here is the Visual Basic routine that generates the Handyman books. The process calls five different ActiveReports report templates, populates them, and then joins them in a specific order."

Private Sub CreateHNDPDF()
    '*************************************
    ' Generate Handyman Book for Printing
    '*************************************

    Dim rpt As New arTextPage
    Try
        ' Setup a new instance of the Report

        Dim rpt1 As New arHNDOverview
        Dim rpt1a As New arTextPage
        Dim rpt2 As New arHTRToC
        Dim rpt3 As New arTextPage
        Dim rpt4 As New arTextPage
        Dim rpt5 As New arTextPage
        Dim rpt6 As New arSketch
        Dim rpt7 As New arSketch
        Dim rpt8 As New EstimatorTemplate
        Dim rpt9 As New arBookIndex

        'Run the report, and set it to the viewer control on the form
        ' Title Page
        rpt1.Run()

        'Abbreviations
        sBookSection = "Overview"
        sPageTitle = "Abbreviations"
        rpt1a.Run()

        'Preface - Part 1
        sBookSection = "Overview"
        sPageTitle = "Preface Page 1"
        rpt3.Run()

        'Preface - Part 2
        sBookSection = "Overview"
        sPageTitle = "Preface Page 2"
        rpt4.Run()

        'How to use Handyman
        sBookSection = "Overview"
        sPageTitle = "How To Use Handyman"
        rpt5.Run()

        'Sample Estimate 1
        sBookSection = "Overview"
        sPageTitle = "HND Sample Estimate 1"
        rpt6.Run()

        'Sample Estimate 2
        sBookSection = "Overview"
        sPageTitle = "HND Sample Estimate 2"
        rpt7.Run()

        '***** for All Divisions **********
        sFromDiv = "01"
        sToDiv = "25"
        sFromItem = "01.0000."
        sToItem = "25.9990."
        iPage = 0
        sBookSection = "Cost Estimator"
        sPageTitle = "Handyman Introduction"
        rpt8.Run()

        '*********** Run Table of Contents ****************
        'Table of Contents after page numbers updated
        rpt2.Run()

        'Update Page numbers on Book Index
        UpdateIndexPageNumbers()
        iPage -= 1  'adjust first page no of index
        rpt9.Run()

        rpt.Document.Pages.AddRange(rpt1.Document.Pages)
        rpt.Document.Pages.AddRange(rpt1a.Document.Pages)
        rpt.Document.Pages.AddRange(rpt2.Document.Pages)
        rpt.Document.Pages.AddRange(rpt3.Document.Pages)
        rpt.Document.Pages.AddRange(rpt4.Document.Pages)
        rpt.Document.Pages.AddRange(rpt5.Document.Pages)
        rpt.Document.Pages.AddRange(rpt6.Document.Pages)
        rpt.Document.Pages.AddRange(rpt7.Document.Pages)
        rpt.Document.Pages.AddRange(rpt8.Document.Pages)
        rpt.Document.Pages.AddRange(rpt9.Document.Pages)

        Viewer1.Document = rpt.Document
        Viewer1.Document.Printer.PrinterName = "Adobe PDF"
        Viewer1.Document.Printer.PrinterSettings.PrintFileName = sBookName & ".pdf"
        Viewer1.Document.Name = sBookName
        Viewer1.Document.Print(False, False, False)

    Catch ex As GrapeCity.ActiveReports.ReportDataException
        MessageBox.Show(ex.Message, Me.Text)
    End Try
End Sub

We send the resulting output to a folder where all 880 book PDF files accumulate. Upon completion, we copy the files to a CD or flash drive and overnight them to the printing company.

This is a partial list of the output files. As you can see, each PDF file is approximately 3.4 MB and the timing shows that they take just over a minute to generate.

Benefits

The original book creation program took approximately 8 minutes to generate one PDF file, so the time it takes to generate all 880 files is dramatically reduced.

The last run of books with the original program was done in July, 2014. We started working on the new application in May, 2014, and successfully produced the January, 2015 books with the new program. We have successfully run the batch process each January and July since then.

Looking Forward

We are using and will continue to use ActiveReports as a key component in all of our application development (desktop, cloud, or web) since it has proven to be reliable, feature-rich, quick, and adaptable.

For more information on the HomeTech Cost Estimating Publications, visit www.hometechpublishing.com