Differences

This shows you the differences between two versions of the page.

--- reports:egreports:data_types [2024/03/21 15:48] – [Operators] smorrison
+++ reports:egreports:data_types [2024/03/21 15:53] – smorrison
@@ Line 5: / Line 5: @@
 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.
+The following table covers the data types used by Evergreen.
 {{tablelayout?rowsHeaderSource=Auto&colwidth="90px"}}
@@ Line 52: / Line 52: @@
 | 9                     |
+The following table covers the transforms used by Evergreen.
-**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.
+{{tablelayout?rowsHeaderSource=Auto}}
+^ Transform                          ^ Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ^
-**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.
+| 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 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.
+| 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.
+| 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.                                                                                                                                                                                                                                                                                                                  |
-**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.
+| 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.                                                                                                                                                                                                                                                                                |
-**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.
+| 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 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.
+| 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.                                                   |
-{{tablelayout?rowsHeaderSource=Auto&float=left}}
+| 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.                                                                                                                                                                                                                                                                                            |
-| Last Name  | Claims Returned Count  | Patron ID  |
+| Hour of Day                        | Hour of Day is identical to Hour.                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
-| Levine     | 3                      | 23923      |
+| 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.                                                                                                                                                                                                                                     |
-| Maddox     | 1                      | 43613      |
+| 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.                                                                                                                                                                                                                                                                    |
-| Smith      | 5                      | 43547      |
+| 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.                                                                                                                                                                                                                                                                                              |
-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.
+| 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.                                                                                                                                                                                                                                                                                      |
-**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.
+| 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.                                                                                                                                                                                                                                                                                                                      |
-**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.
+| 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.                                                                                                                                                                                                                                                                                                                                                        |
-**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//.
+| 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.  |
-**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//.
+| 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.                                                                                                                                                                                                                                                                |
-**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//.
+| 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.                                                                                                                                                                                                                                                                                                                                                                                                                                |
-**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.
+| 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.                                                                                                                                                                                                                                                                                                   |
-**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 =====