Active Reports
by Rupert Walsh - GUI Computing
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
rpt.Show
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
rptMonthly.Show
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.
