Writing Reports

Eco Time allows you to get at system information by printing reports, to a printer, to a file, to the screen or via a Web Server engine to a standard web browser. You can also modify the existing reports to suit your taste or even write your own as Eco Time comes with its own built in Report Writer.
Another way of getting at the data is by exporting it from Eco Time and then importing it into another package (one you're more used to, like Excel or Lotus 123).  Because you can define your own export files, you can ensure that the information you require is at your fingertips.
This manual is used by anyone requiring specialized reports or file exports (including payroll exports) from the system. By using this module all of the useful data received and stored in Eco Time can be extracted.  Users can use this module to design, or modify, their own reports. 
For the sake of clarity, unless otherwise specified, a report can mean either a human readable report or a data export to another program as they both work in the same way.
 
CONTENT

1. Understanding the basics
   1. Database structure
   2. File relationships
   3. Fields
      1. Field pictures
      2. Field types
      3. Local and global fields
   4. Report elements

The key to writing reports is file structures.  Knowing how Eco Time files fit and work together is vital.  File structures might sound intimidating, but as most things, when you break it down, it becomes quite simple.  The vast majority of reports use the same few files, so let’s start with those.
Important files:

• employee - contains all your important employee information like employee number, name, paynumber, etc.
• clocking - employee clockings
• dailyhou - employee daily hours
• weeklyho - employee payroll hours
• errors - employee errors
• warnings - employee warnings
• leave - employee leave records
• classifi - departments, cost Centres, categories, etc.
• dates - a list of dates that hours have been processed for

To try and learn all the fields in these files is pointless.  We’ll go through the critical fields and the rest you can always just research if you need it.  Using Eco Time’s report “Toolbox” will become second nature when you write reports.
But before we tackle files structures and their relation, let’s talk about the Eco Time database.  What are keys, tables, fields?

In Eco Time your data is contained in multiple database files (*.tps), where each file has one or more tables. A table contains records.  A record contains fields.
Below is an example of a database table containing employee information:

A row equates one record and one cell is a field.
Each table has k_eys_which sorts records in a useful way and is saved to file. The Primary Key determines how the records are sorted per default - usually the primary key must be unique. Using the above table as an example: if the Primary key is the employee number, then all the records in the table are by default sorted by the employee number.  The benefit of keys is obviously that an application does not have to sort the records and it also speeds up the retrieval of data.  Other keys can exist, for example, a Surname key which sorts all the employees by Surname.
Below is the same table by Surname key:

The purpose of reports is to extract data from the database and make it look pretty.  Usually the information that you want to extract does not only sit in one file.  Understanding how the above mentioned files fit together, makes you understand what you’re doing, rather than just guessing and hoping that your clicking and typing will make the report print right.
The common denominator between most of the files are the employee number e.g. in the clockings file each clocking record has a field containing the employee number of the employee that clocked.  In the Employee table you have a field for each classification (e.g. Department) which contains the code of the department found in the file containg the codes and descriptions of the classifications (classifi.tps).
The table below illustrates the relationship between the employee and clockings files.  It contains clocking records for the employees in the above tables sorted in record order:

As you can see there are two clocking records for employee Joe Soap (001) and one for John Daley (002).

As mentioned before a field is the value of one cell in a data table. In the report you are able to print the value of a field or you can create a custom report field to, for example, format the value into a human readable value.
When writing a report you can use the Formula Editor to lookup or select a file or custom field.

What you will notice in the table in the File Relationships section is that the date and time are numbers.  Eco Time is written in Clarion which has a standard format for date and time: a Clarion standard date is the number of days that have elapsed since December 28, 1800 and time is stored in hundredths of a second where a value of 1 is equal to midnight.  Of course, no one will understand the report using these figures so we will need to format it into a human readable form.
The way to do it is to create a custom report field with a specific picture.
The main pictures used are:
@S - used for text fields
@D - formats the date into a human readable form
@T - formats the time into a human readable form
@N - used for numbers / currency
@G - enables Employee Select in the Report Options
@C - creates a tick box in the Report Options
When you create a new field in the report writer you will see the different fields available for use when you have to select the picture.
Example: We've created a cDate field with a picture of @d6 and with the value of the Date field and a cTime field with a picture of @t1 and the value of Time:

See the Report Pictures document for a list and examples of the different report pictures available.

There are three types of custom report fields:

• Computed.  The value of this field is set when the field is defined.  The value can also be set through a calculation in the report itself.
• Conditional.  The value of this field is determined by a specified condition.  You can specify a value for true or false.
• User.  The value of this field is determined by the user printing the report e.g. specifying a date in the Report Options.  You can assign a default value.

Local fields are custom fields that are only accessed by the report they were created in.  Global fields are custom fields that can be used by all of the reports. 
Important:  When defining a field, NEVER use the name of an existing global or file field as this may have unexpected results.

There are seven elements that are the building blocks of a report:

1. Report Header.  The contents of the Report Header will be printed at the top of the first page of the report.
2. Primary File.  This is the main file through which the report engine will loop to retrieve data from.
3. Secondary Files*. *You can specify other files through which the report engine must loop for each of the records contained in the Primary file.
4. *Lookups. *Use a lookup to call up information out of a file that is not defined as a Primary or Secondary file.  For example, if you want to print an employee’s surname and his department description, you can make the Employee file the primary file and then do a lookup on Department to find the description.
5. Details.  In this element you define what the report must print.  Every report must have at least one Detail otherwise it is useless.  When viewing a report definition, a Detail is displayed as Print Detail Name.
6. *Calculations. *In this element you can calculate and assign values to a Report Field based on a condition that you specify.
7. Report Footer. The contents of the Report Footer will print at the bottom of each page.

We will go into more detail on how to use each of these at a later stage.