Image of Navigational Map linked to Home / Contents / Search Active Reports

by Rupert Walsh - GUI Computing
Image of Line Break

It's faster, cheaper, smaller and better than Crystal Reports. So what's the catch?

If you're writing VB5 code, then put simply, there is no catch. It's easy to use and it's flexible. Even better, if you're a VB coder, you already know how to use it.

Creating a report

Select Add ActiveX Designer from the Project Menu (You may have wandered what this menu item is for - now you know). It's really just like adding a form.

You will be presented with the Active Reports designer screen. From here on in, it's just like using VB with data controls. You add a data control (DAO, RDO or OLE DB) from the Active Reports toolbar, and the bind the supplied textbox, checkbox or image controls to it. Here's a very simple report I prepared earlier of Authors and Titles from the Pubs database, grouped by author. It only took a couple of minutes to design:

At this stage, you may be wondering why I think this is so much better than Crystal Reports. The interface looks reasonable similar, and you place 'bound' fields onto crystal reports in much the same way. It's time to delve a little deeper...

Run-time report design

Power users will appreciate this feature most of all. You can fully define your reports dynamically at run time, using ActiveReports COM objects. You can add fields, even sections. Here's a quick example of generating a report of Authors and Titles similar to the report shown above. The template report rptTemplate could include corporate logos, or details that are required on every report.

Private Sub GenerateReport()
Dim rpt As New rptTemplate
Dim ctl As Object
Dim sec As Object

    ' Create the data source control
    With rpt.Sections("Detail").Controls
        Set ctl = .Add("DDActiveReports.DAODataControl")
    End With
    ctl.Name = "dcRptData"
    ctl.DatabaseName = App.Path & "\Biblio.mdb"
    ctl.RecordSource = "SELECT Authors.Author, Titles.Title " & _
        " FROM Titles, Authors, [Title Author]" & _
        " WHERE Authors.Au_ID = [Title Author].Au_ID" & _
        " AND Titles.ISBN = [Title Author].ISBN " & _
        " AND Authors.Author LIKE 'Z*'" 'Restrict to a small number of authors
    ' Add a group section for the Author
    rpt.Sections.Add "Author", 1, ddSTGroupHeader, 300
    Set sec = rpt.Sections("Author")
    sec.DataSource = "dcRptData"
    sec.DataField = "Author"
    ' Add group header title
    Set ctl = sec.Controls.Add("DDActiveReports.Field")
    ctl.Name = "txtAuthor"
    ctl.Top = 0: ctl.Left = 0
    ctl.Height = 285: ctl.Width = 4 * 1440 ' 4 inches
    Set ctl.Font = Label1.Font 'This is Arial Bold 12
    ctl.DataSource = "dcRptData"
    ctl.DataField = "Author"
    ' Add the detail field for title
    With rpt.Sections("Detail").Controls
        Set ctl = .Add("DDActiveReports.Field")
        ctl.Name = "txtTitle"
        ctl.Top = 0: ctl.Left = 0
        ctl.Height = 285: ctl.Width = 3 * 1440
        ctl.DataSource = "dcRptData"
        ctl.DataField = "Title"
     End With
     ' Preview the final report
End Sub

Running either the pre-formatted or generated reports took about 5 or 10 seconds on my stock standard Pentium 200 machine. It seems even faster though, as the first page came up in just a couple of seconds, while the rest of the report is background processed, page by page. Here's the (admittedly very plain) result of both the reports. Aesthetically speaking, this could be easily improved by adding labels, titles and graphics to the report.

Active reports supports VB code

Double click on the detail section of an active report at design time, and what happens? You get a code window, that's what. Exactly like with a VB form. It uses VB code and can call public functions from your VB code. There are a number of events available to you. Within these events you can do stuff like fill a text box, or change a label caption.

I've also used ActiveReports successfully for a 'monthly summary' report. This report contained many calculated fields such as 'total sales this month', 'sales year to date' etc., but no detail section. It was far simpler and less tedious than creating formulas and passing values as you would with Crystal Reports. I put all the report fields into the PageHeader, and all the code to get and set the data for this report into the PageHeader_Format() event. I used ADO to get the data (I could also have used the ActiveReport_ReportStart() event instead). I added some public properties to the report to set the print year and month for the report, as you would for a class or form:

Private mnPrintMonth As Integer
Private mnPrintYear As Integer

Public Property Let PrintMonth(ByVal nPrintMonth As Integer)
    mnPrintMonth = nPrintMonth
End Property

Public Property Get PrintMonth() As Integer
    PrintMonth = mnPrintMonth
End Property

Public Property Let PrintYear(ByVal nPrintYear As Integer)
    mnPrintYear = nPrintYear
End Property

Public Property Get PrintYear() As Integer
    PrintYear = mnPrintYear
End Property

Each of the report fields was calculated using complex SQL statements, or mathematical functions in VB code. The results were simply sent to Text fields on the report, as they would be sent to text fields on a VB form:

    txtAverageMonthlySale.Text = DisplayAsCurrency(nTotalSales / 12)
    txtMonth.Text = Format$(DateSerial(Me.PrintYear, Me.PrintMonth, 1), "mmmm")
    txtYear.Text = Me.PrintYear

This allowed me to call the report like so:

    rptMonthly.PrintMonth = cboMonth.ItemData(cboMonth.ListIndex)
    rptMonthly.PrintYear = CInt(txtYear.Text)
    rptMonthly.Printer.Orientation = ddOPortrait

As you can see, I've managed to 'encapsulate' the report quite nicely by integrating ActiveReports and some VB5 code.

It can't all be wine and roses

ActiveReports does have a couple of problems. I couldn't get the OLE DB data control to work. I suspect this is a versioning problem, as I have ADO 1.5 installed. Also, centered, underlined text is displayed in the preview window with the underline skewed to the right. When printed however, it is fine. For a version 1 product, these are not really major issues, and certainly are not enough to stop this being your reporting tool of choice. In fact, they are much smaller issues than those associated with competing products.

Is it really that good?

It's not often a product comes along with such a gentle learning curve, yet with so much flexibility. In our office, as in many others, it used to be the case of everyone taking a step backwards when asked to volunteer to do reports. ActiveReports has changed that. In fact, myself, Brett Sheppard and Ross Mack all volunteered to write this review. I can tell you now though, both Brett and Ross would have sung their highest praises for this product: "ActiveReports Rocks", and "ActiveReports is Choice", respectively.

I simply say buy it.

Written by: Rupert Walsh
June '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break