HomeReports in EvergreenAppendicesAppendix B: Field Transforms

2. Appendix B: Field Transforms

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 Philosopher’s Stone

222

Jane Doe

Harry Potter and the Chamber of Secrets

001

John Doe

Northern Lights

001

John 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.

 

Non-Aggregate Types

Most transforms are more likely to be used as non-aggregate; non-aggregate output types display each field as a single row of information.

Patron Name

Email address

Phone number

Age

Patron A

patrona@gmail.com

xxx-xxx-xxxx

10

Patron A

patrona@gmail.com

xxx-xxx-xxxx

60

 

Transform type

Description

Raw data

Displays a field exactly as it is stored in the database. Raw data is available for all data types.

First Five Characters

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 Continuous Non-space string

Returns the first word (or string of numbers or characters) in a field. 

First Value

Displays the first value that was added to a field.

Last Value

Last Value is identical to First Value except that it returns the most recently added value rather than the last.

Lower Case

Lower Case presents the text stored in a field as all lower case. 

Max

Returns the highest value available for a field rather than the oldest or newest values that first value or last value return.

Min

Returns the lowest value available for a field rather than the largest or highest.

Substring

Applies only to filters, not for display fields.  For example, if you want all patrons who Say you want to get a list of all titles that have the word ‘olympics’ within their title.   Use the ‘title’ field with substring  as the transform and “Equal to” as the operator.  Then, you could specify “olympic” when you run the report.

Upper Case

Displays field in all upper case

Round

Round presents numerical data as the closest integer.  It 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 10th, 2008 and a field contains a timestamp that was created on November 3rd, 2008, age would present that field as “0 mons 7 days 07:02:08.00186.” Age is only available for timestamps.

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 it's 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 19th, 2007 would appear as “19” if day of month is applied to it.  Day of Month is available for timestamp fields.

Age

Age displays a timestamp as the interval between one point of time and a second point in time.  For example, if today's date is November 10th, 2008 and a field contains a timestamp that was created on November 3rd, 2008, age would present that field as “0 mons 7 days 07:02:08.00186.”

Date

Displays a timestamp

Day Name

Returns the day of the week for a date, for example,  Monday, Tuesday, Wednesday, etc... 

Day of Month

Returns the day of the month for a date, for example,  October 12, 2012 would return 12.

Day of Week

Returns the day of the week for a date, for example, a timestamp generated for a Monday would appear as “1”.

Day of Year

Returns the day of the year for a date

Hour

Returns the hour of the day for a date

Hour of Day

Hour of day is identical to Hour.

Month Name

Returns the name of the month for a date

Month of Year

Returns the number of the month for a date, for example October is month “10”.

Months Ago

Returns the number of months that have passed between the time listed in the field and the time the report is run

Quarter of Year

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

Quarters Ago

Quarters Ago displays the number of quarters that have passed between the time listed in a field and the time the report is run.

Week of Year

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

Year

The Year transform displays the year portion of a timestamp.

Year + Month

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

 

Aggregate Types

Transform type

Description

Average

 

Returns the average value of a field

Count

 

Returns a count of all rows found

Count Distinct

 

Returns a count of all unique occurrences of a field.

 

 

When to use what?


Knowledge Tags

This page was: Helpful | Not Helpful