Reporting is the ‘dark art’ of programming because it involves three separate states for your application data. Many applications show large amounts of data in a listbox or a grid format and is meant to be manipulated graphically by the user via scrollbars and other on-screen controls. That’s the easy part.
However, most users want to print the report to paper for a variety of reasons. In the old days with dot matrix printers you’d just throw the data at the printer port and be done with it. Nowadays, applications are expected to show a print preview so the user can adjust printer settings and verify that the data they expect to see is indeed being shown and it all has to be printed exactly as it’s seen on-screen. (It’s debatable if this really saves paper!)
So that’s three separate functions to show the same data. Using a listbox or a grid in your window is easy but how do you display a print preview? Even better: how do you display a print preview that will actually look like your final output on paper? How do you deal with landscape mode versus portrait and how do you deal with the amazing number of paper sizes available around the world?
So what’s a programmer to do? Like anything else in Real Studio you start off by saying, “How hard can it be? I can do this easily.” You’re a smart programmer. You know that printing is nearly the same as drawing to a canvas. So you put a canvas on a window and then you start looking at a report header, then a page header and then the data. Oh, and don’t forget page footers and figuring out how many pages are in a report. Do you need to summarize or do other types of mathematical functions on the data?
Yeesh! Before you know it you’ve got some serious spaghetti code that no one but you understands – maybe. What I’ll attempt to do is show you various methods of reporting. Along the way we’ll hopefully learn a few things about programming.
What do you want to print (Probably the single hardest question). Is your report a single page or multiple pages? Do you need page headers and footers? What about report headers and footers? Do you need to print a logo or have a bar or pie chart?
So what we’ve done is come up with some sample projects that try to imitate a real-world application as close as we can. In all of our examples, we’ll use an ‘Items’ table that could represent any sort of list that we’ll report against.
The database we’re using is a REALSQL Database that comes standard with REALbasic 2005 and above. The database has only one table called “Items” with the following fields:
iItemid – The unique id for the record
sName – Name string
sType – Type string (Inventory, Non-Inventory, Service, Other Charge, etc.)
dCost – Cost of the item in double
dPrice – Retail price of the item in double format
sMarkup – The Markup method string (Cost + 10%, X 2, etc.)
sPurchaseDescription – Purchase description string
sSalesDescription – Sales description string
sCategory – Item category string (Air Conditioner, Furnace, Sprinkler, etc.)
dReorderPoint – The reorder point in double format (why double? If you have a case of something you might reorder when you have .5 left)
dQtyOnHand – How many do you have in-stock in double format.
In REALbasic 2009 Release 4, REAL Software released an integrated reporting engine for REALbasic. Unlike any other reporting tool available for REALbasic, REAL Reports has the report designer integrated into the IDE. With this one release, despite any flaws it might have, it now becomes the defacto reporting tool for use with REALbasic.
REAL Reports is brand new in Release 3 and as you might expect from a new release, it has some serious flaws. Depending upon your reporting needs it might be good enough for you. What we’ll do in this article and in the accompanying demo project is show you how to recreate the same report that we’ve created in all of our other demonstration projects.
We’ll take our project that we developed for the Graphic Object method we developed in Part 1 and instead of using the primitive graphics object method we’ll use REAL Reports. The example project uses a Container Control so depending upon your version of REALbasic you may not be able to edit it.
Download the project file: REAL Reports.zip
Creating the Report
From the project tab, you need to create a new report object just like you would create a new window. For our project we’ll name it Report1. Double clicking on it opens the report editor.
The report editor has a similar feel and function to the window editor. On the left there is a listing of controls available for placement onto the report. The center part of the window is the graphical equivalent of a report and broken up into ‘bands’ which we’ll discuss further in a moment. On the right is the properties list that changes depending upon what object is selected.
REAL Reports is a banded report designer meaning that there are specific bands, or areas, that stretch the complete width of the page. By default, a report comes with a PageHeader, PageFooter and Body bands. The Page Header/Footer bands will print at the top and bottom of every page while the body is generally where most of the data is shown. You may also add Group Sections which allow you to do some fun things with data grouping without resorting to code.
You start designing your report by dragging and dropping controls onto it. In our example we drag a picture object and select our logo and set the stretch property to have the image fit properly. Notice that the properties list has a DataField property that lets us take an image from a database or other data source (we’ll get to this in a bit as well). For now we’ll leave this blank. Add a label named lblName and stretch it and center it because it will become our report title.
Since we want to do some grouping of data, we’re going to add a Group Section. We’ll add labels to the GroupHeader1 to give us column headers at the top of each section. Then we’ll add fields to for each item we want to display of our record. In our case, we’re adding fields for sCategory, ItemID, sName, sDescription, dCost. For the dCost field we’ll set the format to $###,###.00.
If you select the field and take a look at the DataField property we’ll simply add the field name. We will use this information later when we get our data.
In our GroupFooter1 we’ll add another two fields. The first is another sField and another dCost. We want to summarize the data for dCost so we will set the SummaryFunc to SUM and the SummaryType to 3 – Subtotal. We’ll also set the format to $###,###.00.
Finally, align and adjust your labels, fields and make the bands smaller. You should have something similar to the following figure.
You can treat the report object in a similar manner as windows, meaning you can add code that gets run at runtime. Enter the code editor and add the string property property mTitle and add this method:
Sub SetTitle(s as String)
mTitle = s
REAL Report object have a BeforePrint and AfterPrint event that allows you to do some fun things at runtime. In our example we are going to control what our report title is at runtime. Add this to the BeforePrint event of the lblName label:
me.text = mTitle
If you run the project it doesn’t do anything yet because we haven’t told the report to run, get the data or anything. I won’t go into how to set up the ReportViewer as I’ll leave that as an exercise for you. But we will talk about how to prepare the report for the ReportViewer.
In the Open Event of Window 1 we’ll start by creating a new instance of our report object.
Dim rpt As New Report1
We’ll then set our report title.
rpt.SetTitle “Bob’s Item Listing”
We then setup our PrinterSetup object so it knows how to format the page properly.
Dim ps As New PrinterSetup
if gPrintSettings “” then
ps.SetupString = gPrintSettings
Then we get the data from the database using a simple query and recordset.
dim rs as RecordSet = db.SQLSelect(“Select * from Items where dCost > 0.005 Order By sCategory”)
Then we need to create a new Report.RecordSetQuery by passing in the recordset we obtained earlier.
dim rsq as new Reports.RecordSetQuery(rs)
Finally we tell the report to run and we pass in the RecordSetQuery and the PrinterSetup object.
If rpt.Run(rsq, ps ) Then
If rpt.Document Nil Then
What you get will look something like this:
To print the report to a printer we’ll delve briefly into the ReportViewer container control. This is the magic behind printing:
dim ps as new PrinterSetup
if gPrintSettings “” then ps.SetupString = gPrintSettings
dim g as graphics
If ps.PageSetupDialog then
If g Nil then
And that’s pretty much the basics of printing. I urge you to explore the sample application and play with it some. All in all REAL Reports is fairly easy to use.
Shortcomings and Drawbacks of REAL Reports
I said this earlier, REAL Reports is brand new. It has some obvious bugs that may or may not affect you. The most significant of those bugs is that you cannot put any objects in the page footer without a hard crash.
It has no ReportHeader or ReportFooter options giving you the ability to print things ONLY on the first and last pages. There is currently no way to have a band grow based on the height of the objects inside of it.
There is also no way to currently see any of the report objects in the debugger. This is a huge oversight that I expect to get fixed in the next release.
There is also no drop-in report viewer. You’re on your own to make a fully-functional report viewer that changes pages and changes the zoom level. This isn’t very hard, but it can be challenging to come up with all of the permutations.
Another major issue that is that you can’t have end users of your application make simple changes to the reports. This means that any changes will have to be recompiled into your executable. Depending upon your app this may be a deal breaker.
REAL Reports is long overdue as part of the REALbasic toolset. It has some obvious flaws that will eventually be corrected. Will this tool meet everyone’s needs? Probably not. If you need some basic reporting without any frills then it’s probably good enough though only testing will reveal if the bugs in the initial release bite you or not.