Advanced Report Design for Sage Timberline Office

1
Advanced Report Design for Sage Timberline Office

• By Bill Kormoski - Construction Accounting
  Consultant

2
Topics

• Data Dictionary Terms / Structure
• Functions
• IF JOIN Functions
• PART DAY Function
• DAYS Function
• A peek at ODBC in Excel
• Question

3
Data Dictionary / Structure

• Definition of Terms
• File
• A file is a collection of related records that a
  computer treats as a basic unit of
• storage. When you use the software to create a
  new company, you specify a folder
• in which you want to store your information. The
  software stores all of your
• companys data in files in that folder. For
  example, all of the accounts payable
• invoice records and vendor records are stored in
  an accounts payable file.
• Record
• A record is a collection of related items of data
  treated as a unit. The software has
• many types of records stored in a file. For
  example, all of the information about a
• vendor is contained in a vendor record.
• Field
• A field is a single piece of information, the
  smallest unit normally manipulated by
• the software. A record is made up of one or more
  fields. For example, a vendors
• name is a field.

4
Definition of Terms

• Primary key
• A primary key is a unique field or a unique
  combination of fields that identify a record. The
  software uses a records primary key to sort
  records for processing or to locate a particular
  record within a file.
• Alternate key
• An alternate key is an optional method that the
  software can use to sort records when it
  processes a report. Alternate keys also create a
  relationship from one record to another.
• Function
• A function is a named procedure that performs a
  distinct service. For example, the JOIN function
  connects multiple alpha fields into one string of
  characters.
• You can use the JOIN function to format an
  address
• JOIN(City, ", ", State, " ", Zip)

5
Definition of Terms Cont

• Formula
• A formula is a concise statement that expresses
  the symbolic relationship between
• two or more values.
• Formulas allow you to create mathematical
  expressions that can be evaluated in
• numerous applications. Formulas can contain
  combinations of fields, numeric
• operators, functions, constants, and other
  formulas. These elements combine to
• create a formula which can then be used in
  different areas of the software to perform
• calculations.
• An example of a formula called Variance-JC for
  Job Cost might be
• Total Estimate - JTD Cost

6
Data Dictionary

• Print a Data Dictionary
• Relationships Between Record Types
• About One-to-Many Relationships
• About One-to-One Relationships
• Some Records Do Not Have a Relationship

7
Functions

• Review Functions in RD Help

8
IF Function

• IF Function
• Use the IF function to return a value based on a
  conditional statement. This function returns the
  first value if a statement is true or the second
  value if a statement is false.
• Syntax
• IF (condition, value if true, value if false)
• Parameters
• Condition An expression or a field in which the
  value is interpreted as true or false, yes or no,
  or marked or unmarked.
• Value if true The value returned if a condition
  is true, yes, or marked.
• , Value if false Optional. The value returned
  if a condition is false, no, or
• unmarked. If a value is not used or the value is
  false, no, or unmarked, the function
• returns blanks or null values.
• Return If the true value and the false value are
  of the same field type, the return is that type.
  Otherwise, the return is an alphanumeric value.

9
Examples IF

• The following example prints an amount for
  records that have invoice dates greater than 60
  days and less than or equal to 90 days.
• IF (DAYS(Invoice Date, System Date) gt 60 AND DAYS
  (Invoice Date, System Date) lt 90, Amount)

10
More IF (Property Management)

• The following example prints the charge amount if
  the transaction type is Charge. Otherwise, it
  prints the credit amount.
• IF (Transaction Type PM - Transaction
  Charge, Charge Amount PM - Transaction,
  Credit Amount PM - Transaction)

11
More IF (Job Cost)

• The following example prints the job-to-date cost
  for records that have categories of OH, LB, or O.
• IF (Category JC - Category OH OR Category
  JC - Category LB OR Category JC -
  Category O, JTD Cost JC - Category)

12
JOIN Function

• JOIN Function
• Use the JOIN function to connect multiple
  alphanumeric fields into one string of characters
  that prints in the order they were connected. Use
  quotation marks (" ") to combine blank fields
  with alphanumeric fields.
• Syntax
• JOIN (value1, value2, ...)
• Parameters
• Value1 The first value in a list of values to be
  joined. Value2 The second value in a list of
  values to be joined.
• , ... Optional. Additional values in a list.
• Return Alphanumeric value.

13
JOIN Examples

• To properly format an address without extra blank
  spaces, use the following formula
• JOIN (City, ", ", State, " ", Zip)

14
MORE JOIN

• Build a Label That Contains a Field
• Use JOIN to build a label that combines
  descriptive text and a fields contents for a
  field that is totaled. For example, use the
  following formula for a label
• JOIN (Job JC - Job, " Total")

15
PART Function

• PART Function
• Use the PART function to extract a portion of any
  field.
• Syntax
• PART (value, starting position, length)

16
PART Examples

• Alphanumeric Johnson Building Supply
• PART (Employee Name,1,5) Returns Johns
• Numeric 5,123,554.66
• PART (Amount, 3, 6) Returns
  123554
• Date 05/20/2007
• PART (Date, 1, 4)
  Returns 2007

17
DAY Function

• DAY Function
• Use the DAY function to return the day (131)
  from a date field.
• Syntax
• DAY (date)
• Parameters
• Date The date field that contains the day to
  return.
• Return Numeric value.
• Notes
• This function is equal to
• PART (date, 5, 2)

18
DAYS Function

• DAYS Function
• Use the DAYS function to calculate the number of
  days between two dates. The number of days
  returned is always an absolute value.
• Syntax
• DAYS (date1, date2)
• Parameters
• Date1 One of the dates for comparison
• Date2 The other date for comparison
• Returns Numeric value

19
DAYS Examples

• Current
• IF (DAYS (Invoice Date, System Date) lt 30,
  Amount)
• Over 30
• IF (DAYS (Invoice Date, System Date) gt 30 AND
  DAYS (Invoice Date, System Date) lt 60, Amount)

20
A PEEK _at_ ODBC
21
Questions