User Tools

Site Tools


reports:egreports:data_types

List of Data Types, Transforms, and Operators

Data Types

The term “data type” means exactly what it appears to mean: when a piece of data is stored, its data type indicates what kind of information it is. The data type tells the system how to treat the piece of information and determines how that information can be used or viewed.

The following listing covers the data types used by Evergreen.

bool: Bool is short for “boolean”; this data type denotes that the field contains either “true” or “false.”

int: Short for “integer,” this data type denotes that the field contains a number. Occasionally, you will see fields that contain monetary amounts with this data type, however, usually int indicates that the field contains a number such as a patron's Claims Returned Count.

interval: Interval fields contain time intervals, such as “2 weeks” or “6 months.” The recurrence interval for fines and the time limit for age hold protection are stored as intervals.

id: id fields are the unique identifiers that the software uses to distinguish database tables from one another; for example, each circulation that is recorded has an id that no other circulation record has. Ids look like numbers, but the id data type is treated specially by the software for determining how tables are linked.

link: The link data type is similar to the id data type. Links look like numbers if you use them in a report's output and are used by the software to link tables together.

money: Fields with the money data type contain monetary amounts, such as the amount billed to a patron on a billing line item.

org_unit: Short for “organizational unit”, org_unit is a special data type. It can act like a link (for example, the Home Library field for a patron's record links that patron with his or her library branch), but it also lets you choose branches and systems from a list when you use a field of this type as a filter.

text: Fields with the text data type contain plain text; patron names, call number labels, and other strings of text are stored with this data type.

timestamp: Timestamp is a very important data type for reporting; it contains times in a very specific manner that the software can understand. Unless a transform is applied, timestamps aren't particularly friendly to look at; however, many transforms are available to make this data type easier to use for displays and filters.

Transforms

Transforms determine how data is interpreted when it is retrieved from the database. Each data type has a specific set of transforms available; some are more useful to computer systems, some are more useful for humans. The 'Raw Data' transform is available to every data type; it indicates that the data should be treated exactly as it is stored.

This section discusses the difference between aggregate and non-aggregate transforms and contains a listing of the transforms available in Evergreen.

Aggregate and Non-Aggregate Transforms

Most transforms are non-aggregate; non-aggregate transforms only present the data from a single row. On the other hand, aggregate transforms bundle data from multiple rows together. For example, if you apply a non-aggregate transform, such as Raw Data, to the Claims Returned Count for the information below, each entry will appear on a different row in the output:

Last Name Claims Returned Count Patron ID
Levine 3 23923
Maddox 1 43613
Smith 5 43547

If you don't care about anything other than the total claims returned count, you can apply the aggregate transform Sum to the Claims Returned Count. Since Sum is an aggregate transform, the Claims Returned Count from all three rows will be presented in a single row in the output:

Claims Returned Count
9

Average: The Average transform is an aggregate transform. When you apply this transform to a field, the output will contain the average value of the field from every database row that meets the filter criteria. This transform is available for int and money data types.

Count: Count is an aggregate transform; it gives a count of all the fields found. In general, you should use Count Distinct rather than count to avoid unexpected results. Count is available for text, int, id, money, and timestamp data types.

Count Distinct: Count Distinct is an aggregate transform; it gives a count of all the unique fields found. In general, you should use this transform rather than Count to avoid unexpected results. Count Distinct is available for text, int, id, money, and timestamp data types.

Raw Data: The Raw Data transform presents the data exactly as it is stored in the database. Raw Data is available for all data types.

First Five Characters: This transform is non-aggregate and returns the first five characters of the text stored in a field. This transform is particularly useful for filtering or sorting ZIP Code fields. First Five Characters is available for the text data type.

First Continuous Non-space String: First Continuous Non-space String is a non-aggregate transform. As the name indicates, it returns the first word (or string of numbers or characters) in a field. This transform is available for the text data type.

First Value: While not technically an aggregate transform, First Value presents one of many fields. It presents the value that was added to the database first. So, say you have three patrons that were entered in the following order.

Last Name Claims Returned Count Patron ID
Levine 3 23923
Maddox 1 43613
Smith 5 43547

If you apply First Value to the Last Name field, it will return “Levine” since that is the oldest value for that field. First Value is available for the text, int, money, and timestamp data types.

Last Value: Last Value is identical to First Value except that it returns the most recently added value rather than the one that has been in the database for the longest. This transform is available for the text, int, money, and timestamp data types.

Lower Case: Lower Case presents the text stored in a field as all lower case. So, if a field contains the text “Smith,” this transform would present that field as “smith.” Lower Case is available for the text data type.

Max: Max is another technically non-aggregate transform – it returns the highest value available, rather than the oldest or newest values that first value or last value return. Max is available for text, int, money, and timestamp.

Min: Min is identical to Max except that it returns the lowest value available from the database rather than the largest or highest. Min is available for text, int, money, and timestamp.

Substring: Substring is an unusual transform; it only applies for filters, not for display fields. Say you want to get a list of every patron that has the letters “ith” in their last name; you could set up a filter on the Last Name field with substring as the transform and “Equal to” as the operator. Then, you could specify “ith” when you run the report and patrons with names like “Smith” or “Witherson” will be included in the output. Substring is only available for text.

Upper Case: This transform presents the text stored in a field as all upper case; it is good for matching text fields if you don't know which case is used in the database. Upper Case is only available for the text datatype.

Round: Round presents numerical data as the closest integer. It is available for int and money.

Sum: Sum is an aggregate transform that adds up the values of all applicable fields. Be careful not to confuse Sum with Count or Count distinct. Sum is available for int and money.

Age: Age presents a timestamp as the interval between it and the current date. For example, if today's date is November 10, 2008, and a field contains a timestamp that was created on November 3, 2008, Age would present that field as “0 mons 7 days 07:02:08.00186.” Age is only available for timestamp data type.

Date: This transform presents a timestamp as a human-readable date. Date is available for timestamp fields.

Day Name: This transform presents a timestamp as the day of the week by its name, such as Monday, Tuesday, Wednesday, etc. Day Name is only available for timestamp fields.

Day of Month: Day of Month presents a timestamp as the numerical day of the month; for example, a timestamp generated for January 19, 2007, would appear as “19” if Day of Month is applied to it. This transform is available for timestamp fields.

Day of Week: Day of Week presents a timestamp as the numerical day of the month. A timestamp generated for a Monday would appear as “1” if Day of Week is applied to it. This is available for timestamp fields.

Day of Year: Day of Year presents a timestamp as the numerical day of the year. A timestamp generated for February 2 would appear as “32” if Day of Year is applied to it. Day of Year is available for timestamp fields.

Hour: Hour presents a timestamp as the hour for which it was generated, for example, a timestamp generated at 2:17 PM would appear as “14.” This transform is available for timestamp fields.

Hour of Day: Hour of Day is identical to Hour.

Month Name: Month Name presents a timestamp as the name of the month, such as “January.” This is available for timestamp fields.

Month of Year: Month of Year displays the numerical month; for example, a timestamp for February 15, 2008, appears as “2” when Month of Year has been applied to it. This is available for timestamp fields.

Months Ago: Months Ago presents the number of months that have passed between the time listed in the field and the time the report is run. Months Ago is available for timestamp fields.

Quarter of Year: Quarter of Year displays the numerical quarter – for example, a timestamp for December 12, 2008, would appear as “4.” This is available for timestamp fields.

Quarters Ago: Like Months Ago, Quarters Ago presents the number of quarters that have passed between the time listed in a field and the time the report is run. This is available for timestamp fields.

Week of Year: Week of Year displays the numerical week of a year (1–52) for which a timestamp is set. This is available for timestamp fields.

Year: Year displays only the year portion of a timestamp.

Year + Month: Year + Month presents a timestamp as the year and month, for example, July 31, 2005, appears as “2005-7” when this timestamp is applied. Year is available for timestamp fields.

Operators

Operators describe the ways two pieces of data can be compared to one another and are used when creating filters to determine which database records should be included in a report.

Operators return TRUE or FALSE depending upon the result of the comparison; when an operator returns FALSE for a record, that record is not included in the output.

For example, when comparing two numbers, they can be equal to one another, the first can be greater than the second, the first can be less than the second, or they can be not equal to one another.

The terms in this list are the operators that are available when performing comparison operations to determine which data should be returned by a report.

Note that transforms are applied to fields before the operator is used to compare your parameter with each field. So, if you have a filter on a patron's first name set up with the Upper Case transform and Equals as the operator and you specify “MATTHEW” as your filter parameter, then a field containing “Matthew” will match.

Equals: Equals compares two operands and returns true if they are exactly the same.

Contains Matching Substring: This operator checks if a field contains a specific, case-sensitive substring. Returns TRUE if the field contains the exact specified substring.

Contains Matching Substring (Ignore Case): This operator is identical to Contains Matching Substring, except it is not case-sensitive.

Greater Than: This operator returns TRUE if a field is greater than your parameter. For text fields, this means ascibetically higher strings (for example, “oranges” is Greater Than “bananas”); for timestamps, Greater Than can be thought of as “later than.”

Greater Than or Equal To: Greater Than or Equal To is identical to Greater Than, except it also returns TRUE when the field is equal to your parameter.

Less Than: This operator returns TRUE if a field is less than, lower than, earlier than, ascibetically before, or smaller than your parameter.

Less Than or Equal To: This operator is identical to Less Than except it returns TRUE when the field is equal to your parameter.

In List: In List is similar to Equals, except it allows you to specify multiple parameters and returns TRUE if the field is equal to any one of your choices.

Not In List: Not In List is the opposite of In List; it allows you to specify multiple parameters and returns TRUE only for fields that are not equal to any of your choices.

Between: Between requires you to specify two parameters and returns TRUE for fields that are between them (inclusive). Between is relative to datatype.

Not Between: Like Between, Not Between requires you to specify two parameters; it returns TRUE for fields that are not between your parameters.

Is NULL: Is NULL returns TRUE for fields that hold no data. It does not take any parameters. You should try to use Is NULL or Blank instead of Is NULL. There are some circumstances where fields may contain blank text strings, but for all intents and purposes hold no data. Is NULL or Blank behaves consistently for both situations.

Is Not NULL: This returns TRUE for fields that hold some data; like Is NULL, Is Not NULL does not take any parameters. However, you should use Is Not NULL or Blank rather than Is Not NULL for consistent behaviors when dealing with strings.

Is NULL or Blank: Is NULL or Blank returns TRUE for fields that either hold no data or do hold 'empty' data.

Is Not NULL or Blank: This operator returns TRUE for fields that hold some non-trivial data.

Source: Advanced Reporting Guide

reports/egreports/data_types.txt · Last modified: 2022/09/16 11:55 by smorrison