CS Time/TNA Documents

username password

Writing Reports

    CS 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 CS Time comes with its own built in Report Writer.

    Another way of getting at the data is by exporting it from CS 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 CS 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

    Understanding the basics

    The key to writing reports is file structures.  Knowing how CS 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 CS 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 CS Time database.  What are keys, tables, fields?

    Database structure

    In CS 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:

    EmpNumber FirstName Surname CardNumber Paynumber
    001 Joe Soap 0015 SOJ001
    002 John Daley 0008 DAJ002
    003 Sarah Caldwell 0010 CAS003

    A row equates one record and one cell is a field.

    Each table has keys 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:

    EmpNumber FirstName Surname CardNumber Paynumber
    003 Sarah Caldwell 0010 CAS003
    002 John Daley 0008 DAJ002
    001 Joe Soap 0015 SOJ001

    File relationships

    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:

    EmpNumber CardNumber Date Time Direction
    001 0010 77,120 2,678,901 I
    002 0008 77,120 2,707,101 I
    001 0010 77,120 6,711,601 O

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

    Fields

    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.

    Field pictures

    What you will notice in the table in the File Relationships section is that the date and time are numbers.  CS 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:

    EmpNumber CardNumber cDate cTime Direction
    001 0010 20/12/2012 7:26 I
    002 0008 20/12/2012 7:31 I
    001 0010 20/12/2012 18:38 O


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

    Field types

    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 and global fields

    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.

    Report elements

    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 FilesYou 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.

     

     

     

     

     

     

     

    Permalink:  http://tinyurl.com/bcqhv37

    Page last modified 10:38, 27 Jan 2015 by cay-lynne CS Time/TNA Documents > Reports > Writing Reports

    Comments0

    You must login to post a comment.
    Attach file

    Files63

    FileSizeDateAttached by 
     AddCalc.jpg
    No description
    28.49 kB14:55, 31 May 2012CareenActions
     AddLookup.jpg
    No description
    32.01 kB11:32, 18 May 2012CareenActions
     AddSecondary.jpg
    No description
    21.74 kB12:28, 30 May 2012CareenActions
     BrowseREportLayouts.jpg
    No description
    144.33 kB10:15, 16 May 2012CareenActions
     CalcMoved.jpg
    No description
    47.16 kB14:55, 31 May 2012CareenActions
     CalcProperties.jpg
    No description
    42.38 kB15:00, 31 May 2012CareenActions
    ClassEmpDetails.jpg
    No description
    126.67 kB11:33, 4 Jun 2012CareenActions
     ClassFooter.jpg
    No description
    113.47 kB11:06, 4 Jun 2012CareenActions
     ClassHeader.jpg
    No description
    138.88 kB11:06, 4 Jun 2012CareenActions
     ClassReportOptions.jpg
    No description
    135.44 kB11:34, 4 Jun 2012CareenActions
    ClassSecondary.jpg
    No description
    138.45 kB11:33, 4 Jun 2012CareenActions
     ClockingsField.jpg
    No description
    85.24 kB13:21, 30 May 2012CareenActions
     CounterField.jpg
    No description
    75.25 kB15:05, 31 May 2012CareenActions
     DateFields.jpg
    No description
    70.7 kB13:21, 30 May 2012CareenActions
     DatesProperties.jpg
    No description
    56.78 kB14:01, 30 May 2012CareenActions
    DeptHeader.jpg
    No description
    21.65 kB12:57, 30 May 2012CareenActions
     DeptHeader2bMoved.jpg
    No description
    141.32 kB12:52, 30 May 2012CareenActions
     DeptHeaderMoved.jpg
    No description
    34.18 kB12:54, 30 May 2012CareenActions
     DeptHeadUnderLoop.jpg
    No description
    33.67 kB13:03, 30 May 2012CareenActions
    DeptLookup.jpg
    No description
    134.67 kB12:33, 5 Jun 2012CareenActions
     DeptLookupOption.jpg
    No description
    83.02 kB12:35, 5 Jun 2012CareenActions
     DeptTotal.jpg
    No description
    21.47 kB15:08, 31 May 2012CareenActions
     DeptTotalMoved.jpg
    No description
    51.26 kB15:10, 31 May 2012CareenActions
     DetailWithLocalFields.jpg
    No description
    13.81 kB10:16, 18 May 2012CareenActions
     EditInPlace.jpg
    No description
    61.47 kB10:16, 18 May 2012CareenActions
     EmpDetailsLocalField.jpg
    No description
    59.94 kB13:08, 30 May 2012CareenActions
     EmpDetailsUnderDates.jpg
    No description
    37.76 kB14:07, 30 May 2012CareenActions
     EmpDetailswithClockings.jpg
    No description
    13.69 kB14:07, 30 May 2012CareenActions
     FieldNameChange.jpg
    No description
    55.19 kB12:33, 5 Jun 2012CareenActions
     FormulaEditor.jpg
    No description
    125.4 kB10:16, 18 May 2012CareenActions
     GroupVar.jpg
    No description
    56.27 kB11:13, 18 May 2012CareenActions
     HeaderFunction.jpg
    No description
    43.28 kB13:03, 30 May 2012CareenActions
     HeaderProperties.jpg
    No description
    37.29 kB11:21, 16 May 2012CareenActions
     InGroup.jpg
    No description
    54.03 kB12:44, 30 May 2012CareenActions
     LookupPicture.jpg
    No description
    60.18 kB10:16, 18 May 2012CareenActions
     LoopThroughDates.jpg
    No description
    37.47 kB14:07, 30 May 2012CareenActions
     LoopThroughDatesMoved.jpg
    No description
    37.28 kB14:07, 30 May 2012CareenActions
     MovedEmpDetails.jpg
    No description
    27.92 kB12:38, 30 May 2012CareenActions
     MovedSecondary.jpg
    No description
    27.93 kB12:37, 30 May 2012CareenActions
     NewLocalVar.jpg
    No description
    42.75 kB10:16, 18 May 2012CareenActions
     PrimaryFileClassification.jpg
    No description
    106.57 kB11:06, 4 Jun 2012CareenActions
     PrimaryFileDept.jpg
    No description
    141.18 kB12:19, 30 May 2012CareenActions
     RepOptionsWithEmpSelection.jpg
    No description
    100.05 kB11:15, 18 May 2012CareenActions
     ReportEditorIcon.jpg
    No description
    5.99 kB10:15, 16 May 2012CareenActions
     ReportExample.jpg
    No description
    180.89 kB14:30, 30 May 2012CareenActions
     ReportExampleClue.jpg
    No description
    41.35 kB14:30, 30 May 2012CareenActions
     ReportHeader.jpg
    No description
    75.96 kB11:38, 16 May 2012CareenActions
     ReportOptionsWithDates.jpg
    No description
    141.62 kB14:13, 30 May 2012CareenActions
     REportOptionsWithLookup.jpg
    No description
    193.57 kB12:33, 5 Jun 2012CareenActions
     Rev2Output.jpg
    No description
    134.47 kB10:35, 18 May 2012CareenActions
     Rev4bOutput.jpg
    No description
    140.51 kB12:41, 30 May 2012CareenActions
    Rev4cOutput.jpg
    No description
    141.4 kB14:52, 31 May 2012CareenActions
    Rev4dOutput.jpg
    No description
    124.33 kB14:49, 31 May 2012CareenActions
     Rev4eOutput.jpg
    No description
    224.45 kB14:13, 30 May 2012CareenActions
     Rev4fOutput.jpg
    No description
    206.28 kB15:17, 31 May 2012CareenActions
     Rev4Output.jpg
    No description
    153.86 kB11:32, 18 May 2012CareenActions
     Rev5Output.jpg
    No description
    120.13 kB11:34, 4 Jun 2012CareenActions
     SelectLookupParent.jpg
    No description
    150.03 kB11:32, 18 May 2012CareenActions
     SelectLookupSource.jpg
    No description
    52.8 kB11:32, 18 May 2012CareenActions
     SelectSecSource.jpg
    No description
    144.62 kB12:28, 30 May 2012CareenActions
     SelectSecSourceName.jpg
    No description
    54.18 kB12:28, 30 May 2012CareenActions
     UpdateEmpClass.jpg
    No description
    107.79 kB11:32, 18 May 2012CareenActions
     UsingEmpDetails.jpg
    No description
    137.86 kB13:14, 30 May 2012CareenActions
    Powered by MindTouch Core