D Sample Report
e Overview
BC Registry Services (BCRS), among other things, is the official government register for corporations in the province of British Columbia, Canada. Its legal obligations are keeping the records, issuing certificates of registration and providing the clients with legal documents, such as annual reports. A couple of decades ago all those obligations have been handled via paper reports. Things started to change with the introduction of Mainframe based solutions. At the present day all new records are entered via web and kept in the database and generated emails are in some instances equal to the legal documents. However, paper reports are still essential for day-to-day operations of the register.
Being responsible for all new reports, my responsibilities included designing reports, configure the infrastructure if necessary and ensure that generated files are sent to the proper places for further processing. While working at BCRS I have been exposed to a number of different reporting platforms. What they all have had in common was the ability to generate reports in PDF format and their data source being always Oracle database.
C Design
When designing any report I always try to separate layout from logic. I prefer to store the logic in the form of a database view. This way the logic can be developed and tested independently from the layout. It can be even developed by a different developer, knowledgeable about the data model. Created view can be also re-used by another report or another resource. It is also possible to create an alternate layout, or layout based on another technology, using the same view for its logic. Storing the logic in the view is also more efficient from a database point of view. Instead of executing a number of small queries by the client application, view is executed on the server, with its execution plan known in advance.
Having complete sets of data is essential to any report development. Working on actual data would be preferred, but it is not always feasible. What is always possible is to create dummy data with appropriate data access. For instance I test displaying of maximum characters for every text field. And I use character 'W' for letters and '0' for numbers (I found them to be the widest).
At BCRS I have had set up testing data for every report and its variation. This way it was easy to test the outcome when, for instance, adding to a report subsection used in multiple reports. It was also important for general management as a reference of all their reports.
Some reports at BCRS, mostly the batch reports, are using data buffer. Because the content of a database can change over the time, the use of a buffer ensures that the report, when re-run, will show desired information (generated reports are legal documents and keeping track of their furnishing can be required). Not all the data should be buffered. For instance mailing addresses should not be buffered, so the latest address is always displayed.
The best advice for designing layout I can give is to use background colors for development. This way it is much easier to get a sense of all those canvases, frames and layouts generally used for report development. To make the layout uniformed, it is advisable to stick to default values as much as specifications allow.
Printed report is a physical medium. Every report should be printed when designed, preferably with intended printer. I have a good look at printed report, looking for the flow, empty spaces, borders, fonts and headers and so on. Report must be intuitive and balanced on one side and cost efficient on the other.
9 Implementation
Note that Sample Report is an actual report used at BCRS. Presented image was edited for security and legal reasons. All data shown is dummy data; all addresses, postal codes and various numbers and ID's have been altered. Government logo and Registrar's signatures have been covered with beige box. Also word SAMPLE has been added across the image.
The report was designed to run on JasperReports platform and Oracle database, created with Jasper's iReport 3.7.1 IDE.
The requirement for the layout was to make it as much identical as possible to the official government pre-printed form already available to the public. The official form is intended to be filled by hand. I have had access to the paper form, but not to its original layout. So I had to determine fonts, borders, bars and their sizes visually, often by placing original form and printed reports on top of each other and looking against the light.
Internally, the layout consists of one main template and a number of subreports. Each section identified with a capital letter, as well as the Registrar seal, has its own subreport. Section A. (Mailing Address), Registrar seal and their surrounding information are also nested inside another subreport.
For mailing the client is using official windowed envelopes. So the name and address, referred together as mailing address, don't get printed on the envelope, but on the report itself. And report is folded in such a way that the mailing address is visible. There are many reports at BCRS, such as this sample report, which are showing mailing addresses.
Fitting all the information is always a challenge. Often the full legal company name with various designations is longer than the envelope window allows. And there are always that many rows that could be printed. Some software workarounds could be implemented to format the data. Sometimes, I had to print the report, fold it, stuff it into the envelope and perform tapping test (tap filled envelope on its edge so the content will move) to ensure that all mailing related information is visible and nothing else (such as NAME APPROVAL NO. just above the mailing address in case of this report).
Some documents, apart from the usual legal designation in the header, are furnished with additional legal features. It can be Registrar's signature or, in case of this report, a Registrar's seal. Registrar's seal is build as a separate subreport. Because of its nature, an additional care has been taken to ensure it's proper use (even the example shown was modified to give an idea and yet not to misrepresent).
As shown on the picture, there are two kinds of identifiers. First one, DEV, is a database identifier and means the report was run against development database. Other possible values are TST for test database and blank for production database. Another identifier is report specific unique ID. Some ID's are filing specific (such as this one), some are run specific (each report run has its own sequence based ID; useful for batch reports). The use of unique ID's for report identification was my own initiative that was adopted by the client as a standard.
Most reports spread across multiple pages. Defining report header and footer is often a necessity. In case of a well defined report such as this, an additional steps have had to be taken. So for instance there was a requirement to show at least 2 partners in section H., with their names and addresses. Or, if no partners present, to show blank fields but with their headers. As you can see from the image, there is a second page to this report with nothing but header, footer and blank PARTNER NAME and RESIDENTIUAL OR REGISTERED ADDRESS sections (page not shown).