This is a demo of a Java SQL report generation application.This application is used to create a formatted document for rows selected from a database. The application uses meta-data retrieved from the database to populate table and field names, and a syntax tree to evaluate values and filter data. The document is laid out in a graphical editor to creating a template files. The template are used in conjunction with a database and batch application to generate a formatted postscript output for each detail row selected.
Table Name
Description
person
Identifies different people by id and name
address
Multiple addresses with only one active
address_type
Valid types of addresses and text
values
Arbitrary detail rows containing amounts
Database Tables: A list of tables created in a relational database as a simple test example.
There are three test cases.
Person Id
Description
1
A basic small number of data rows example
2
An alternate basic small number of data rows example
3
Many detail rows to show the effects of grids and pages
Test Cases: Three different Ids of individuals to create documents for. These IDs represent different cases to show from simple to more complex test cases.
For this document:
LHS stands for left-hand side.
RHS stands for right-hand side.
Slide 1: This figure shows the reports application with an open database definition. The first step to creating a document is associating the tables on the database. Here 'person.sdb', an application file, has been loaded. In the database window there are left and right side tables, between these views are the left side table’s columns and the right side table’s columns. By selecting a table and one or more columns from each side a relationship can be created with the buttons below these controls. (note there should be the same number of columns selected for each side). The relation list at the bottom of the page on the left shows the tables related to the table selected in left tables list, the lower right box shows the columns that are related between the tables. The table names and columns are loaded from a database dynamically, however the relationships are added manually to indicate desired traversal.Slide 2: When data is processed to generate documents the application needs a starting point. LHS Table->Properties displays the initial rows property dialog. The check box indicates that this is the table to select the rows to generate formatted documents. An entry field is given enabling the user to specify a “where clause” for the table to isolate rows. Only one table can be indicated as the initial table. The a where clause is specific to this table.Slide 3: With the database definition defined a form can be created. Here address information has been laid out followed by an area for repeating detail rows. The grid definition, defined later, is the same width as this area. A area can repeat on different pages and have different height but the width remains constant.Slide 4: If the grid does not have enough room on a page to display all the rows, the data will flow over to another page that contains the same area identifier unless the grid causes page repeat. If checked the page will be repeated as many times as required to display all the rows from the database.Slide 5: The grid data can also flow over to a different page if it has a definition for the same grid. Here the heading is different and the grid contains the full page. In the example this page is set to repeat. This will be noted later when looking at the document preview.Slide 6: Grids consist of a header, detail row, and a footer. The header is printed once per page area. The detail rows repeat for each detail for the fields listed. Here the header contains just static text, the detail rows contain two fields in the values table, and the footer contains static text and a total amount computed from the included detail rows. A filter can be assigned to the detail rows limiting the rows that are displayed.Slide 7: The application uses filters that can be applied to document sections to limit the data shown. The filters are given a name, description, and filter text to use in the application. The application parses the rule to generate a syntax tree that is evaluated at runtime. In the case of filters the result needs to be true or false, however the AST logic can return any supported datatype and can be used for display on documents. Here the filter is not realistic, it merely shows that fields and values can be compared as well as used for computations. The filters are parsed at load time generating a tree instance that can be reused on each case without re-compilation. Prior to submission, validation is performed to on syntax and field names.Slide 8: This dialog shows the available filters available to add, as well as the selected filters. Multiple filters can be used on a row. The filters can also be applied to single fields in a document to show a specific value.Slide 9: In the footer of Slide 6 is a computed field, this is added for a field to total, and a radio button is used to select the computation. In the grid earlier, the example row data was summed. In this screen-shot the maximum value would be returned.Slide 10: This is another example of a page layout with a grid. This is used to display the person's addresses in a list.Slide 11: This grid section will display addresses without a footer. Again, the grid width matches the document layout width.Slide 12: Once a document layout is fully defined it can be previewed with data from the database. Here in this test case the data for the address and detail rows has been populated from the database. The footer of the detail section contains a total of the selected rows.Slide 13: For generating a preview, a where clause is used to select a specific case. Because the person table was listed as the starting table for generation, all the data relationships will branch off person using person.id == 1 for creating the preview.Slide 14: The preview will contain all the pages of the generated document. Here the only other page shows address details.Slide 15: Once the database has been mapped and the document templates created, documents can be generated as either PDF or PS output in batch mode. In this example the Java batch application is run using password 'fred' and the 'person.sef' template generating postscript output for each of the three people in the database.Slide 16: The output for person.id == 3 is shown in a postscript viewer. It shows similar data as the preview using the initial page template followed by a different full detail page.Slide 17: The long detail page repeats until there are no more detail rows.Slide 18: This Demo concludes with the last page showing multiple address rows for the test case. Here the addresses are different from the one on the first page because of the filter on the detail rows. Finally, for paper correspondence, the generated document files would be sent to a printer.
