2. Appendix B: Field Transforms

Last Updated 05/17/2023

What is a Field Transform?

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 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, 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 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 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?

Use ‘raw data’ for general display of field information.
Use ‘date’ for most date-based reports.
Be careful with the ‘age’ transform; it might not provide you with what you want.

Knowledge Tags

This page was: Helpful | Not Helpful