DB-Description and Template architecture

[Top]  [Chapter]  [Previous]  [Next]

When speaking of "reporting" we mean the process that data which comes from a database or has been calculated is merged with the text and layout objects stored in a report template to create a new document. Already the basis version of TextDynamic has a powerful method to mix text and data, called mail merge. In contrast to this easy to use method the "reporting" we talk about here allows that areas of the report template can be looped for each row in the input data which is typically the result of a database query.

 

Using the "Token to Template Conversion" it is possible to create powerful templates which can be used to create complicated documents. But since it is based on simple text it does not offer, apart from syntax highlighting, much possibilities to detect errors in a template (for example a data field defined by a sub query is used outside of the group which enumerates the records in this query).

 

To be able to detect such errors it is required for the editor to know the underlying database structure. Since TextDynamic does not implement database access (except for DAO) the structure must be made known using a special description, the "DB-Description".

 

To make it possible

 

Of course the looping allows recursion to work with relationally organized data.

 

Example of a customer organization application:

 

We need a database with two tables, one table contains the names and addresses of the customers of a company. Each customer has a unique number: "CUST_ID".

 

The second table contains all orders received by the sales department. To identify that a certain order was placed by a certain customer the (lookup-)field CUST_ID receives the value of the field CUST_ID found in the first table, the customer table.

 

clip0111

 

 

In a real application we would need additional tables to group orders by dates, to store received payments, to cancel orders and so on. But we want to keep things simple, only show the basic concepts.

 

Creating the data input form for the two tables would be fairly simple, but the code which creates and prints the invoice can be tricky.

 

TextDynamic simplifies this problem for you by separating the necessary tasks into several steps.

 

(a) DB-Description (XML)

 

Collect the required fields from the involved tables and add into virtual groups.

 

What is done here is basically the creation of a XML structure which represents the graphic above. In this step you also add group variables which are used to sum up values to create totals.

 

The XML DB-Description can be edited as text and loaded into TextDynamic or you can use a special API to build the template. We added a feature to auto create such a template from DAO compatible databases, such as MS Access. (See Example)

 

The DB-Description is maintained by the IWPReporter interface.

 

(b) Template (RTF with extensions)

 

From the XML structure created in (a) TextDynamic can create and maintain a raw reporting template.

The devloper can now format this template, add or remove fields and text.

You can also enable the end users to adjust the reporting template to their needs.

An external application is not required for this, all is done with TextDynamic.

 

(c) Events used to retrieve and locate data

 

Create the necessary programming logic to provide the data for the groups. Usually you will create SQL queries for the inner groups each time one row of the outer group is processed. (Although SQL allows the GROUPBY mode in the SELECT method, we recommend to execute multiple SQL statements for the inner groups. So you have more control over the ordering of the rows and deeper nesting levels are no problem)

 

 


[reportingbasics.htm]    Copyright © 2007 by WPCubed GmbH