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 table covers the data types used by Evergreen.

Data Type Description
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.
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.

Data Type Key

Within the template editor, data fields are associated with the following icons, which notate what type of data the field contains:

20220520-144107.jpegtext field
20220520-144115.jpegboolean (results in a true (1) or false (0))
20220520-144204.jpeglinks to a different table of data
20220520-144210.jpegorganizational unit (tip: to show library names in Display Fields, choose Short (Policy) Name instead; this is better to use for filters)
20220520-144215.jpegmoney/dollar amount
20220520-144223.jpegtimestamp (ex: date)
20220520-144230.jpegID (ex: circulation ID, hold request ID)
20220520-144238.jpeginteger
20220520-144303.jpegtime interval (ex: circulation duration lengths)

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

The following table covers the transforms used by Evergreen.

Transform Description
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:2:08.00186.” Age is only available for timestamp data type.
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.
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.
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 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 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. 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.
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.
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.
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.
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.
Round Round presents numerical data as the closest integer. It is available for int and money.
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.
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.
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.
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.

The following table shows the Operators available in Evergreen:

Operator Description
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 (dates), 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. For timestamps (dates), Greater Than or Equal To can be thought of as the specified date and after.
Less Than This operator returns TRUE if a field is less than, lower than, earlier than, ascibetically before, or smaller than your parameter. For timestamps (dates), Less Than can be thought of as “before.”
Less Than or Equal To This operator is identical to Less Than except it returns TRUE when the field is equal to your parameter. For timestamps (dates), Less Than or Equal To can be thought of as the specified date or earlier.
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: 2024/03/21 16:02 by smorrison