Template Terminology

Data Types

The information in Evergreen’s database can be classified in nine data types, formats that describe the type of data and/or its use. These were represented by text-only labels in prior versions of Evergreen. Evergreen 3.0 has replaced the text labels with icons. When building templates in Reports, you will find these icons in the Field Name Pane of the template creation interface.

timestamp

datatypes timestamp

An exact date and time (year, month, day, hour, minutes, and seconds). Remember to select the appropriate date/time transform. Raw Data includes second and timezone information, which is usually more than is required for a report.

datatypes link

A link to another database table. Link outputs a number that is a meaningful reference for the database but not of much use to a human user. You will usually want to drill further down the tree in the Sources pane and select fields from the linked table. However, in some instances you might want to use a link field. For example, to count the number of patrons who borrowed items you could do a count on the Patron link data.

text

datatypes text

A field of text. You will usually want to use the Raw Data transform.

bool

datatypes bool

True or False. Commonly used to filter out deleted item or patron records.

org_unit

datatypes orgunit

Organizational Unit - a number representing a library, library system, or federation. When you want to filter on a library, make sure that the field name is on an org_unit or id data type.

id

datatypes id

A unique number assigned by the database to identify each record. These numbers are meaningful references for the database but not of much use to a human user. Use in displayed fields when counting records or in filters.

money

datatypes money

A monetary amount.

int

datatypes int

Integer (a number)

interval

datatypes interval

A period of time.

Field Transforms

A Field Transform tells the reporter how to process a field for output. Different data types have different transform options.

Raw Data. To display a field exactly as it appears in the database use the Raw Data transform, available for all data types.

Count and Count Distinct. These transforms apply to the id data type and are used to count database records (e.g. for circulation statistics). Use Count to tally the total number of records. Use Count Distinct to count the number of unique records, removing duplicates.

To demonstrate the difference between Count and Count Distinct, consider an example where you want to know the number of active patrons in a given month, where ``active" means they borrowed at least one item. Each circulation is linked to a Patron ID, a number identifying the patron who borrowed the item. If we use the Count Distinct transform for Patron IDs we will know the number of unique patrons who circulated at least one book (2 patrons in the table below). If instead, we use Count, we will know how many books were circulated, since every circulation is linked to a patron ID and duplicate values are also counted. To identify the number of active patrons in this example the Count Distinct transform should be used.

Title Patron ID Patron Name

Harry Potter and the Chamber of Secrets

001

John Doe

Northern Lights

001

John Doe

Harry Potter and the Philosopher’s Stone

222

Jane Doe

Output Type. Note that each transform has either an Aggregate or Non-Aggregate output type.

Selecting a Non-Aggregate output type will return one row of output in your report for each row in the database. Selecting an Aggregate output type will group together several rows of the database and return just one row of output with, say, the average value or the total count for that group. Other common aggregate types include minimum, maximum, and sum.

When used as filters, non-aggregate and aggregate types correspond to Base and Aggregate filters respectively. To see the difference between a base filter and an aggregate filter, imagine that you are creating a report to count the number of circulations in January. This would require a base filter to specify the month of interest because the month is a non-aggregate output type. Now imagine that you wish to list all items with more than 25 holds. This would require an aggregate filter on the number of holds per item because you must use an aggregate output type to count the holds.