CS Time/TNA Documents

username password

Writing Text Reports - Revision 4

    The Report Writer

    In the 4th Revision we add the description of the department to the report.

    Report Specification Revision 4

    Print an employee list report, in name order of all the employees on CS Time’s database. All employee details must be in capitals and the user must be able to select an employee or employee group for which he wants this report to print and display the department description next to the employee surname.
     

    Part 1 - Showing the Department Description

    As seen in CS Time’s Employee Masterfile, employees are assigned to departments or classifications. The employee record will hold the applicable department or classification code and a lookup in the department record will then display the department name.
     
    WR-4-1.png
     
    A similar procedure must be followed to determine the Department Description in the report writer. To do this,  we have to first create a Lookup.
     
    1. In the Edit Report window, highlight the Primary file (this is the file on which you would like to do a lookup) and then select the Lookup button.
     
    WR-4-2.png
     
    1. Select the Properties tab to edit the Lookup options.
    2. Double-click on the Value of the Parent Source field to select the Parent File.  This should be set to the Employee file.
     
    WR-4-3.png
     
    1. Now double-click on the Value of the Source Name to select the file you want to lookup.  This should be set to Department.
     
    WR-4-4.png
     
    1. This Lookup must now be moved into position in the report tree using the green arrow buttons to end up as shown below.
     
    WR-4-5.png
     
    The Lookup thus occurs just after the Primary file’s record was found and before the print detail is printed. 
     
    1. Change the Print employee detail to include the Department Description as shown above. (Note that the fields for the Department file are now available in the Fields tab).
    2. Save the changes and run the report. The report should now look like this.
     
    WR-4-6.png
     
    This is all very nice, but as you will notice, the employee’s are not in Department order and it would be nice to setup Department Headers. To achieve this, we have to rearrange the report around a bit.
     

    Part 2 - Printing the Employees in Department Order

    1. The first step would be is to change your Primary file, which used to be Employees, to Department. This will allow us to loop through the Primary file (Department) in description order (so select the Order By (Key) option to DescKey) and then add the employee details below each department.
    2. Now the Primary file Properties should look like the ones in the picture below:
     
    WR-4-7.png
     
    1. Next, click the Ok button to save the structure change and then edit the report again. 
     
    It is obvious that the employee details are now going to produce no information because the report has no reference to the employee file at all. To gain a reference and use values in a file, a lookup can be used or a secondary file. A lookup will achieve a result, but will not list all the employees who belong to the department currently used in the primary file. For this we need to add a secondary file.
     
    1. This file must be added by first highlighting the Primary file and then clicking on the Add Secondary button.
     
    WR-4-8.png
     
     
    1. A secondary file will be added and indicated by a Loop through record.  Select this record and edit it's Properties. The Parent Source shoudl be set as Department.
     
    WR-4-9.png
     
    1. Select Employee as the Source Name.
     
    WR-4-10.png
     
    1. Select Ok to save the changes.
    2. Now move this secondary detail with the arrow keys to place it on the same report tree as the primary file.
    3. At the same time you will notice that a Lookup on Department and the Primary File Department exists. Any department file field names can be derived from the primary file, so the lookup on Department can now safely be removed with the remove button. The report detail should now look like this.
     
    WR-4-11.png
     
     
     
    There is one more minor change which is required. The report has all its details under the same report tree. This means that the first department will be picked, then the report will loop through all the employees who belong to that department, then the report will exit the loop and print the last employee’s details. Not exactly what we want.
     
    What we wanted is that the first department will be picked, then the report should loop through all the employees who belong to that department and print each employee’s details as the report loops through the employees belonging to that department.
     
    1. To achieve this, move the Print Employee details to under the Loop Though Employee tree as shown below.
     
    WR-4-12.png
     
    1. Save the report and then print it. The report should like the example below. Please note that the departments are in alphabetical order on the right.
     
    WR-4-13.png
     
    We previously added a function called group to the report fields. Note that this function only works on the primary file. If the Employee file is not the primary file (as in the report example now), then the Ingroup() function must be added to the Employee file filter detail. This will achieve the same result used previously for filtering an employee or group of employees for this report.
     
    1. Edit the Loop Through Employee Properties and add the InGroup() function in the field. The Employee Secondary file properties must look like this:
     
    WR-4-14.png
     
    1. Save these changes and let us move on.
     

    Part 3 - Adding a Department Header

    1. We would like to add a Department Header for each group of employees. We achieve this by adding a Print Detail called Department Header and populating it as follows:
     
    WR-4-15.png
     
    1. Save the detail and the report should now look like this.
     
    WR-4-16.png
     
    1. Move the Header to the indicated place on the report tree as shown below.
     
    WR-4-17.png
     
    1. Save and then run the report, and the result will be the following:
     
    WR-4-18.png
     
    1. By looking at the structure of the report, you will notice that the Department Header is printed before looping through the employees. At this point, the Department Header will print, but there is no guarantee that employees will be found in the loop. In short, you can get Department Headers, which contain no data. To avoid, this move the Department Header into the report tree as shown below.
     
    WR-4-19.png
     
    But wait, you say, will the Department Header now not print for every employee that the report finds in the loop. That is correct and in order to avoid this we have to place a condition in the Department Header detail which will only allow the detail to print every time the department changes.
     
    CS Time has a function called Header() and it is used in the Header Details as shown below:
     
     
     
    HeaderFunction.jpg
     
     
     
    In short, if there are no employees in the loop for a department, the Department Header detail will never be printed, and the condition avoids the header from being repeated if more than one employee is found in the loop for a department.
     
    Now that we have a concise report, you can clean up the Employee Details by removing the unwanted field <Dep:Description>. To gain more space on this line, let’s combine the employee Surname and Name into one field with a fixed length. Add a field to the report called EmpDetails, with a picture of @S20 and a value of clip(Emp:Surname) & ', ' & clip(Emp:FirstName) as shown below:
     
     
     
    EmpDetailsLocalField.jpg
     
     
     
    "Clip" removes any trailing space from fields and the "&" sign combine fields and strings together. A string is identified by starting with a single quotation mark and ending with a single quotation mark i.e. ‘test’.
     
    Delete the RepName and RepSurname local fields from the report and also from the Print Employee Detail. Add the EmpDetails field to the Print Employee Detail of the report as shown below:
     
     
     
    UsingEmpDetails.jpg
     
     
     
    The report, when printed, will now look like this:
     
     
     
    Rev4dOutput.jpg
     
     
     

    Part 4 - Showing Employee Clockings for a Date Range

     
    The next step in this example would be to print an employee’s clockings for a set period of time.  To achieve this you would have to create three more additional fields.
     
    The first two must be FromDate and ToDate and each are formatted as follows:
     
     
     
    DateFields.jpg
     
     
     
    Add a new field called Clockings and format this field as shown below:
     
     
     
    ClockingsField.jpg
     
     
     
    Getclockings is a function which gathers clockings into one line for a particular period. The period is defined by the fields called dat:date which will be explained in the sections below. Save the report.
     
    Highlight the Primary File and add a secondary report detail. Change the Parent Source file to None and select Dates as your Source Name. By changing the parent file to None, it prevents the file called Dates from being limited by values in the primary file i.e. it will loop through all available dates irrespective of the primary file. Set the Order to Key which will make the range options visible.  Set the From range to <FromDate> and the To range <ToDate> as shown below:
     
     
     
    DatesProperties.jpg
     
     
     
    Your report should now look like this:
     
     
     
    LoopThroughDates.jpg
     
     
     
    Move the Print Detail in the report tree as shown below:
     
     
     
    LoopThroughDatesMoved.jpg
     
     
     
    The result is that for every Employee in the Department, the report will loop though the Dates file from the date specified in the FromDate Field, up to the date specified in the ToDate Field.
     
    Move the Print Employee detail into the Loop Through Dates tree (as shown below), and the employee detail will be printed for every date in the dates loop.
     
     
     
    EmpDetailsUnderDates.jpg
     
     
     
    Edit the Print Employee Details and add the Date field and the Clockings field as shown below:
     
     
     
    EmpDetailswithClockings.jpg
     
     
     
    Save the changes and run the report.  You will notice the two fields called FromDate and ToDate are now available on the Report Options window. These fields are user fields which can be changed at report runtime. In this case they will determine the date range that this report will be printing Employee Clockings.
     
     
     
    ReportOptionsWithDates.jpg
     
     
     
    When printing the report the following information will be provided:
     
     
     
    Rev4eOutput.jpg
     
     
     
    The formatting of the report is a function of preference and requirements. It is possible to build more information into the Department Header, i.e. the Description fields and even a space between employees. The end result can look like this:
     
     
     
    ReportExample.jpg
     
     
     
    The details of the changes I will simply leave up to you, but the screen capture below should help.
     
     
     
    ReportExampleClue.jpg
     
     
     

    Part 5 - Calculating the Number of Employees per Department

     
    Calculations can be used to change values according to certain conditions depending on the report requirements or simply to count the total of employees by department.
     
    Add a calculation by clicking on the Add Calculation button as shown below:
     
     
     
    AddCalc.jpg
     
     
     
    Click on the Calculation New Calc entry and edit it's properties as shown below:
     
     
     
    CalcProperties.jpg
     
     
     
    The calculation will thus always (when 1=1, which is always true) increment the field counter by one when the report reaches this calculation.
     
    Move the Calculation into the report tree to under the Employee file loop. This will ensure that the calculation is always reached when the employee details are printed.
     
     
     
    CalcMoved.jpg
     
     
     
    Remember, we used a field called Counter in the calculation. You now need to add this field to the local report fields as shown below.
     
     
     
    CounterField.jpg
     
     
     
    Now we need to place the Counter field in the report where it is going to make sense. Generally it will be nice to see how many employees were found in each department. Therefore the employee total must be printed after the last employee was found for each department.
     
    Create a print detail which looks like this:
     
     
     
    DeptTotal.jpg
     
     
     
    Move the detail into the following position:
     
     
     
    DeptTotalMoved.jpg
     
     
     
    The Department Total is thus printed after the report exits the employee loop in the report tree. The only other consideration is that there is no condition which will reset the counter to zero when the department changes.  Currently, the Counter will increase irrespective if the department changes or not for each employee found. To prevent this, we have to add a reset counter calculation detail.  Add another calculation with the properties shown below and move the counter underneath Primary File Department. This will ensure that the counter is reset just before the report enters the Employee loop for a Department.
     
     
     
    ResetCounter.jpg
     
     
     
    The resulted report should look like this:
     
     
     
    Rev4fOutput.jpg
     
     
     

    Part 6 - Adding a Department Lookup in the Report Options

     
    Now we want to add an option to select to print the list for a specific department.  In TNA6 special classification report fields are available that makes this possible.
     
    Add a local user field called RR:DepartmentID as shown below:
     
     
     
    DeptLookupOption.jpg
     
     
     
    In the Report Options window custom field are displayed in description order which means that this local field will be displayed in between the From Date and To Date options.  To correct this, rename the FromDate and ToDate fields and add an 'a' on the left as shown above. 
     
    Then go to the Loop Through Dates file and change the fields names accordingly as shown below:
     
     
     
    FieldNameChange.jpg
     
     
     
    Click on Ok to save the changes and click on Print to view the report options.  You will now see an lookup on Department option which allows you to print the information for the employees in the selected department.
     
     
     
    REportOptionsWithLookup.jpg
     
     
     
    This works great, but you will notice that you are now unable to print the report for all of the departments.  To rectify this you need to add a filter on your primary file.
     
    Change the report and view the properties for Primary File Department.  Double-click on the value of the Report Filter field and add in the following:
     
    (<RR:DepartmentID> > 0 AND <DEP:DEPARTMENT> = <RR:DepartmentID>) OR (<RR:DepartmentID> = 0 AND  <DEP:DEPARTMENT> > 0)
     
    Click on OK and Ok again to save the changes.  The statement above consists of two parts and the report engine will loop through the primary file if either one of the parts are true:
     
    1) if the value of RR:DepartmentID is greater than zero and the current department code in the primary file is equal the the value of RR:DepartmentID i.e. it will only print for the selected department; OR,
     
    2)  the value of RR:DepartmentID is zero and the value of the current department code in the primary file is greater than zero i.e. it will print all the departments if the value of RR:DepartmentID is zero.
     
    Click on Ok to save the changes and test your report again for both scenarios.
     
    A list of all the classification lookups can be found in the Special Report Fields document.
     

     

    Permalink:  http://tinyurl.com/k7p4uks

    Page last modified 15:56, 28 Jan 2015 by cay-lynne CS Time/TNA Documents > Reports > Writing Reports > Writing Text Reports - Revision 4

    Comments0

    You must login to post a comment.
    Attach file

    Files23

    FileSizeDateAttached by 
     WR-4-1.png
    No description
    41.93 kB11:14, 28 Jan 2015cay-lynneActions
     WR-4-10.png
    No description
    8.43 kB11:18, 28 Jan 2015cay-lynneActions
     WR-4-11.png
    No description
    4.64 kB11:18, 28 Jan 2015cay-lynneActions
     WR-4-12.png
    No description
    4.6 kB11:18, 28 Jan 2015cay-lynneActions
     WR-4-13.png
    No description
    38.79 kB11:18, 28 Jan 2015cay-lynneActions
     WR-4-14.png
    No description
    8.42 kB12:33, 28 Jan 2015cay-lynneActions
     WR-4-15.png
    No description
    1718 bytes13:02, 28 Jan 2015cay-lynneActions
     WR-4-16.png
    No description
    45.6 kB13:02, 28 Jan 2015cay-lynneActions
     WR-4-17.png
    No description
    5.26 kB13:02, 28 Jan 2015cay-lynneActions
     WR-4-18.png
    No description
    35.77 kB13:03, 28 Jan 2015cay-lynneActions
     WR-4-19.png
    No description
    5.28 kB15:06, 28 Jan 2015cay-lynneActions
     WR-4-2.png
    No description
    4.53 kB11:14, 28 Jan 2015cay-lynneActions
     WR-4-20.png
    No description
    8.67 kB14:58, 28 Jan 2015cay-lynneActions
     WR-4-21.png
    No description
    11.1 kB14:58, 28 Jan 2015cay-lynneActions
     WR-4-22.png
    No description
    47.68 kB14:59, 28 Jan 2015cay-lynneActions
     WR-4-23.png
    No description
    32.45 kB14:59, 28 Jan 2015cay-lynneActions
     WR-4-3.png
    No description
    45.28 kB11:14, 28 Jan 2015cay-lynneActions
     WR-4-4.png
    No description
    7.49 kB11:15, 28 Jan 2015cay-lynneActions
     WR-4-5.png
    No description
    40.67 kB11:15, 28 Jan 2015cay-lynneActions
     WR-4-6.png
    No description
    37.07 kB11:15, 28 Jan 2015cay-lynneActions
     WR-4-7.png
    No description
    43.19 kB11:17, 28 Jan 2015cay-lynneActions
     WR-4-8.png
    No description
    5.01 kB11:17, 28 Jan 2015cay-lynneActions
     WR-4-9.png
    No description
    47.82 kB11:17, 28 Jan 2015cay-lynneActions
    Powered by MindTouch Core