TB REPORT BUILDER 3.0 Author: Ivaylo Todorov Copyright 1998 All rights reserved.# e-mail:ivokt@hotmail.com; ivokt@usa.net No warranty expressed or implied. This program remains the property of Ivaylo Todorov. You are hereby granted a license to use this program. You may not reproduce, sell, distribute, publish, circulate, or commercially exploit the program(s), or any portion thereof, without the written consent of Ivaylo Todorov. This software is being made available to you as is, and Ivaylo Todorov is not liable for any damages or loss from the use of this program. Use of this program constitutes an agreement with these terms and the terms of the software agreement, license and warranty 1. Short description TB REPORT BUILDER is a package of 3 INFROMIX utilities allowing a database connectivity (tbed), creation of a comprehensive set (tbrb) of reports and browsing the report files (rtpview). Here will be described only the tbrb utility. (the description of the other utilities is included in the zip file - they are also downloadable as separate utilities from IIUG) It is especially designed for allowing for reports with columns of the same type and very sophisticated totals building. It implies the existance of one (temp) table upon which the report is build (according to my experience this is the best manner to produce fast and locking-independable reports). This (temp) table can be build using the tbed utility. The main features of tbrb are: - creation of vast diversity of reports through a intuitive form interface - no programming is required - access to the report data through a popup-menu for viewing, preprocessing and preformatting - availability of many formatting tools like: = setting widths, names and delimiters for the columns in the report = formatting defaults provided based on the type of the columns = entering page headers directly in vi or loading them from ready-to-use reports = adjusting automatically the page headers to the column widths = formatting the values in the columns = setting programming variables or using Informix variables (like pageno, lineno) throughout the report = availability of dynamic formatting - the values of some column positions in the report depend on the values of another column during execution of the report - diverse, sophisticated and dynamical totals building: (for more details look in the 'Reports examples' section) = setting of the totals columns through a form = dynamically arranging the totals - by ordering the data in a popup-menu is the break-down fixed = multiple-level totals = grouping totals - after the breakdown the list of totals for a group of column values is displayed (see examples) = totals formatting possible (for example:no totals for 1 row) - possibility for report's formatting and totals defaults saving to a database for later use - possibility for user rights-allocation for restricting the access to formatting and totals building options 2 Reports examples Here are described the features of the report builder using an example of a database in a manner of increasingly complicated reports. For explanation how to build this reports refer to the 'Using TBRB` section. Suppose we have a (temp) table with invoices with diverse attibutes - invoice number, customer, product, clients address - country, state, city, currnecy, invoice value, tax on the invoice and payments on the invoice. ( if this data is contained in several tables it can be joined in one temp table which will encompass all the needed rows ). - Formatting First one may want to do some formatting on the rows - we can set the column widths and names - let say invoice number - (inv num , 10); customer - (customer name, 14); product - (product name, 14); country - (country , 8), state - (state, 5); city - (city,14); currency - (curr 4); invoice value - (value,6); invoice tax - (tax,6),payment - (paid, 6). Then we can make some justification on the fields - we may want to left justify the invoice number, customer, product, state, city, phone fields and to right justify the invoice value, invoice tax and payment fields. Then we may not want to have repetition for all the fields except payment when there is more than one payment on the invoice. The generated report will now look like: Example 1 : ----------------------------------------------------------------------------------------------- | inv num |customer name| product name |country |state| city |curr|value | tax | paid | ----------------------------------------------------------------------------------------------- |001 |customer1 |product1 |USA |OH |Columbus |USD | 100| 6| 20| | | | | | | | | | | | |002 |customer2 |product2 |USA |MI |Saginaw |USD | 200| 12| 30| | | | | | | | | | | 40| | | | | | | | | | | 50| | | | | | | | | | | | |003 |customer3 |product3 |France | |Lion |FRF | 500| 50| | ............ In addition to this layout we may also add report name, page numbers and some page headers. We may also insert some customer defined heading instead of the generated one and then adjust the columns automatically to fit in. - Totals building The totals building consists of 2 phases: setting the totals columns by the definition of the report and indicating the breakdown columns by running of the report. According to the concept of this report some feautures are put at the disposal of the end-user like defining the range of the data to be encompassed by the report and the breakdowns for the totals so they are accessible during run-time. Of course this settings can be influenced by defaults set by defining the report or disabled by user-rights protection flags. During the first phase we may mark the invoice value, invoice tax and payment fields for totals. Then during run-time the end-user may require totals for customer (this is done by ordering the data by the customer field) or for product and he will get accumulation for any customer (product) in the invoice value, invoice tax and payment columns. it is possible also to get the totals for product and each customer within this product (this is done by ordering the data by product and customer). There is also another type of totals possible - grouping totals - i.e. during defining of the report some groups of columns can be set and at execution for this groups will be listing after each breakdown. If we set the group to be country, state and the totals for this group - invoice value, invoice tax and at run time we order the data by product we will have after each product listing of all the country/state variation within this product with the according totals. The report will look like: Example 2 : ----------------------------------------------------------------------------------------------- | inv num |customer name| product name |country |state| city |curr|value | tax | paid | ----------------------------------------------------------------------------------------------- |001 |customer1 |product1 |USA |OH |Columbus |USD | 100| 6| 20| | | | | | | | | | | | |002 |customer2 |product1 |USA |MI |Saginaw |USD | 200| 12| 30| | | | | | | | | | | 40| | | | | | | | | | | 50| | | | | | | | | | | | |003 |customer3 |product1 |USA |OH |Akron |USD | 40| 6| 10| |004 |customer4 |product1 |France | |Lion |FRF | 500| 50| | Total product1: USA OH 140 12 USA MI 200 12 France 500 50 |005 |customer5 |product2 |USA |NY |Rochester |USD | 300| 18| | ............ We can have more than one breakdown level - we can again group by product and customer and we will get this listings for each product and for each customer within any product. We can have also several groups: Let's suppose we have another column - bank - that is the bank at which the payment is made. Then we can define the bank as second group and the 'paid' column for total for this group and we will have except for the listing by country/state after each breakdown also a listing by bank with totals for the paid column after this breakdown. It is also possible to combine the grouping totals with the usual totals and to have both listings and totals after each breakdown. So if we apply all the above-described possibilities (the total is for tax) and order by product the report will look like: Example 3 : ------------------------------------------------------------------------------------------------- ..|customer name| product name |country |state| city |curr|value | tax | bank | paid | ------------------------------------------------------------------------------------------------ |customer1 |product1 |USA |OH |Columbus |USD | 100| 6|NBD Bank| 20| | | | | | | | | | | | |customer2 |product1 |USA |MI |Saginaw |USD | 200| 12|Comerica| 30| | | | | | | | | |NBD Bank| 40| | | | | | | | | |Comerica| 50| | | | | | | | | | | | |customer3 |product1 |USA |OH |Akron |USD | 40| 6|Comerica| 10| |customer4 |product1 |France | |Lion |FRF | 500| 50| | | Total product1: 74 USA OH 140 NBD Bank 60 USA MI 200 Comerica 90 France 500 ..|customer5 |product2 |USA |NY |Rochester |USD | 300| 18| | | .......... And at last in addition to the reports above we can have also a report containing only the totals: Example 4: ------------------------------------------------------------------------------------------------ ..|customer name| product name |country |state| city |curr|value | tax | bank | paid | ------------------------------------------------------------------------------------------------ Total product1: 74 USA OH 140 NBD Bank 60 USA MI 200 Comerica 90 France 500 Total product2: 35 USA CA 24 NBD Bank 24 USA NY 140 Comerica 100 3. Installation The package conatins .4GL, .per, script and Makefile files. Its installation implies the existance of the Informix commands c4gl, form4gl. In order to install it and start it follow the steps: - unzip the tbrb.zip file in a new directory - start the make command to compile the programs i_tbed.4ge and i_rptmn.4ge using the Makefile - start the formcp script to compile the forms - edit the tbed script to point to the i_tbed.4ge program, place it in a directory in which You have PATH to - start the tbed script - browse (temp) table thru tbed and from the browsing menu enter 1 or press to go to build report on that table (see tbed.txt) 4. Using the TBRB utility The report building consists of 2 phases - formatting (which is coceptually the definition of the report and is done by its initizlizing) and the data set manipulations (which is conceptually accessible during run-time for browsing and changing the data set, ordering and brakdowns of the report, but it can be influenced also during the 1 phase through defaults and user rights). Here these 2 phases are merged in one step by step process. Browsing the data set Once You have started the report builder a popup-menu starts representing the whole data set selected. You can browse it by: arrows left, right - to the left and right of the column list; arrow up down - up, down of the data rows; , - begin, end in direction width, , - next, previous screen, , - first, last screen. Report formatting 1 Next You can go ahead formatting the report's columns - press . A form pops up. You are supposed to enter the name of the columns in the report in the 'name in the report' column and its lengths in the 'length' column: [ Configuration of the popup-menu ] [The configuration is not saved by default] DB[db_invoices ] table[invoices ] DB name name in the report length ---------------------------------------------------------------- [inv_num |inv num |10 ] [cust_name |customer name |14 ] [product |product name |14 ] [country |country |8 ] [state |state |5 ] [city |city |14 ] [currency |curr |4 ] [inv_val |value |6 ] [inv_tax |tax |6 ] [payment |paid |6 ] [ | | ] sum of length(width=72)[ 87 ] Restricting and reordering the data set By pressing F8 a form appears: INCLUDE [1|inv num | ] [2|customer name | ] [3|product name | ] [4|country | ] [5|state | ] [6|city | ] ... EXCLUDE [1|inv num | ] [2|customer name | ] [3|product name | ] [4|country | ] [5|state | ] [6|city | ] ... ORDER [ ] You are supposed to enter criteria for inclusion and exclusion of rows in the data set. In the 'ORDER' section You can order the data - You should enter the number of the column to be order by - like '3' - order by product or '3,2' order by product, customer. The reordering sets breakdown levels for the report builder. After the order section You enter an option group with options: 'Starting the report', 'Starting the totals report' and 'Starting the report and the total report' - here You can indicate whether You want also the totals report (see 'Example 4'). The navigation thru the options is using the keys - () and (). Report formatting 2 Next you should set the totals and some other formatting options - press . A form pops up: [ Configuration of the report ] name of the report [Invoice survey ] total sign justif rept delm DB name report name -------------------------------------------------------------------- [ |L] [R ] [||inv_num |inv num ] [ |L] [* ] [||cust_name |customer name ] [ |L] [* ] [||product |product name ] [ |L] [* ] [||country |country ] [ |L] [* ] [||state |state ] [ |L] [* ] [||city |city ] [ |L] [* ] [||state |state ] [ |L] [* ] [||currency |curr ] [* |R] [* ] [||inv_val |value ] [* |R] [* ] [||inv_tax |tax ] [* |R] [ ] [||payment |paid ] [ | ] [ ] [|| | ] The 'DB name' and 'report name' columns are filled in automatically from the data entered previously; the 'delm' column determines which delimiters should separate the columns in the report -it is entered (the defualt is '|'); the 'rept' column sets that by repetition of the inv_num filed (the 'R' filed) the 'R' and '*' marked fileds will not be repeated; the 'justif' column sets left ('L') or right ('R') justification; the 'total sign; column determines for which fields there will be totals - in this case this are 'inv_val', 'inv_tax' and 'payment' (the breakdowns are still determined during the 'Restricting and reordering the data set' phase - see before). Having set the form in this matter will result in generating the report from the 'Example 1' (see `Reports examples` before). To generate the report in 'Example 2' -i.e. to add the grouping totals You may need to modify the 'total sign' columns in the following manner. [ Configuration of the report ] name of the report [Invoice survey ] total sign justif rept delm DB name report name -------------------------------------------------------------------- [ |L] [R ] [||inv_num |inv num ] [ |L] [* ] [||cust_name |customer name ] [ |L] [* ] [||product |product name ] [g |L] [* ] [||country |country ] [g |L] [* ] [||state |state ] [ |L] [* ] [||city |city ] [ |L] [* ] [||state |state ] [ |L] [* ] [||currency |curr ] [g* |R] [* ] [||inv_val |value ] [g* |R] [* ] [||inv_tax |tax ] [ |R] [ ] [||payment |paid ] [ | ] [ ] [|| | ] Here with the character 'g' (arbitrary choosen) are marked all the fields from the group and with 'g*` are marked all the fields for which there will be totals for any encountered variation of the 'g' fields. To generate 'Example 3' You may need to enter in the 'totals sign' column the following values: [ Configuration of the report ] name of the report [Invoice survey ] total sign justif rept delm DB name report name -------------------------------------------------------------------- [ |L] [R ] [||inv_num |inv num ] [ |L] [* ] [||cust_name |customer name ] [ |L] [* ] [||product |product name ] [g |L] [* ] [||country |country ] [g |L] [* ] [||state |state ] [ |L] [* ] [||city |city ] [ |L] [* ] [||state |state ] [ |L] [* ] [||currency |curr ] [g* |R] [* ] [||inv_val |value ] [* |R] [* ] [||inv_tax |tax ] [h |L] [ ] [||bank |bank ] [h* |R] [ ] [||payment |paid ] [ | ] [ ] [|| | ] Here is kept the same princliple - with the characters ('g', 'h' respectively) are marked the the group building fileds and with '*' are marked their totals, with '*' is marked the column for which there will be only totals. Page Header To modify the page header press while in the above form. An option menu will pop up - navigate using (), () to come to the desired options. The options are: -"Automatic generation of the PAGE HEADER" - the page header is generated, -"Extracting of saved PAGE HEADER" - the Page Header is extracted from the saved in the configuration tables PAGE HEADER, -"Inserting of PAGE HEADER in vi" - the page header is entered in vi - at the beginning the generated page header is proposed (but it is commented and will be ignored if not uncommented) it can be used for builing of a new page header or just for adjusting the new page header to the column widths; if it is desired that the columns be aligned with the new page header (it could have been loaded from somewhere) than a line like:"% | | | |" is included which means that the columns widths should be adjusted to match the lengths set by '|'. You can put the 'pageno', 'lineno', 'today' variables in the page header and they will be replaced during execution. It is possible also to set another programmable variables but this requires setting them programmatically in the code. If the page header is more than 80 characters it is inserted in several files which are opened at once at the beginning and than after saving are pasted together. The insertion of the page header may look like: #----------------------------------------------------------------------------------------------- #| inv num |customer name| product name |country |state| city |curr|value | tax | paid | #----------------------------------------------------------------------------------------------- Sales Int Invoice Report TODAY PAGENO ----------------------------------------------------------------------------------------------- | I n v o i c e d e t a i l s | inv value | payment | ----------------------------------------------------------------------------------------------- | inv num |customer name| product |country |state| city |curr|value | tax | paid | ----------------------------------------------------------------------------------------------- % | | | | | | | | | | -"Loading of PAGE HEADER from file" - the page header is contained in some file and just extracted from there (it is useful if some old report should be reprogrammed) Except for the Page Header also the LAST ROW can be configured the same way also by pressing of . Report running After having finished working on the formatting 2 form press and the report generation will start. The generated report(s) will be opened with the rptview utility. 5. Notes This report builder was created by me for surving the purposes of large financial-accounting software system. Its goal was not only to automate the report building of the overall system, but also to contribute for the standartization of the dramatically growing number of reports and to bring some grade of compatibility between the reports algorithms and layouts. It was also aimed at providing the end-user with some flexibility for handling the data and the breakdown levels of the report. That is why it is in some respect specialized and may lack some flexibility in building a vast diversity of reports. Some of the features may not work in different DB environment - so for example the totals are build using decimal(15,2) arrays (which was our standart for values) and may not calculate correctly on money data type. Also in the context of this package no rights allocation is foreseen (but it is possible if using separetely the code). Further on no saving of the entered formatting parameters for the reports is feasible altough in the program is implemented saving of this parameters to database tables. In conclusion I would like to say that this report builder was designed and implemented from the bottom up - no 4GL report building feature was really exploited (altough it was possible but for reasons of future development plans was avoided). So it can be rewritten easily in any other program language like say C and will work in the same way because the algorithms use essentialy only the 'print' report statement of 4GL (no AFTER/BEFORE GROUP, no formatting 4GL options). Have great time using this utility. Ivaylo Todorov 03/26/99 Farmington hills MI