reports:egreports:data_types
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
reports:egreports:data_types [2022/04/26 17:31] – [Aggregate and Non-Aggregate Transforms] smorrison | reports:egreports:data_types [2024/03/21 16:02] (current) – [Table] smorrison | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Data Types, Transforms, and Operators ====== | + | ====== |
===== Data Types ===== | ===== Data Types ===== | ||
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 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. | ||
- | For example, when Evergreen needs to store a time, such as a checkout time for a circulation, | + | The following |
- | **bool:** Bool is short for “boolean”; | + | {{tablelayout? |
+ | ^ Data Type ^ Description | ||
+ | | bool | Bool is short for “boolean”; | ||
+ | | int | Short for “integer, | ||
+ | | interval | ||
+ | | 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 | ||
+ | | 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 | ||
- | **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 | + | ==== Data Type Key ==== |
+ | Within the template editor, data fields are associated with the following icons, which notate what type of data the field contains: | ||
- | **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. | + | {{:reports: |
- | + | {{: | |
- | **id:** id fields are the unique identifiers that the software uses to distinguish database tables from one other; for example, each circulation that is recorded has an id that no other circulation record has. Ids look like numbers, but the id data type is treated specially by the software for determining how tables are linked. | + | {{:reports: |
- | + | {{:reports: | |
- | **link:** The link data type is similar | + | {{:reports:egreports: |
- | + | {{: | |
- | **money:** Fields with the money data type contain monetary amounts, such as the amount | + | {{:reports:egreports: |
- | + | {{:reports:egreports: | |
- | **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' | + | {{:reports:egreports: |
- | + | ||
- | **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. | + | |
===== Transforms ===== | ===== 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. | + | 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. | This section discusses the difference between aggregate and non-aggregate transforms and contains a listing of the transforms available in Evergreen. | ||
Line 33: | Line 38: | ||
=== Aggregate and Non-Aggregate Transforms === | === Aggregate and Non-Aggregate Transforms === | ||
- | Most transforms are non-aggregate; | + | Most transforms are non-aggregate; |
{{tablelayout? | {{tablelayout? | ||
Line 41: | Line 46: | ||
| Smith | 5 | 43547 | | | Smith | 5 | 43547 | | ||
- | If you don't care about anything other than the total claims returned count, you can apply the aggregate transform | + | If you don't care about anything other than the total claims returned count, you can apply the aggregate transform |
{{tablelayout? | {{tablelayout? | ||
- | | Claims Returned Count | | + | | Claims Returned Count | |
- | | 9 | | + | | 9 |
+ | The following table covers the transforms used by Evergreen. | ||
- | On the other hand, if you apply a non-aggregate transform, such as raw data, to the Claims Returned Count, each entry will appear | + | {{tablelayout? |
+ | ^ Transform | ||
+ | | Age | Age presents a timestamp as the interval between it and the current date. For example, if today' | ||
+ | | Average | ||
+ | | 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 | ||
+ | | 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 Five Characters | ||
+ | | 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 | ||
+ | | 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, | ||
+ | | 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 | ||
+ | | 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. | ||
- | {{tablelayout? | + | ===== Operators ===== |
- | | Claims Returned Count | | + | |
- | | 3 | | + | |
- | | 1 | | + | |
- | | 5 | | + | |
+ | 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. | ||
- | **Average: | + | 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. |
- | **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 // | + | 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. |
- | **Count Distinct:** Count Distinct is an aggregate transform; it gives a count of all the unique fields found; | + | The terms in this list are the operators that are available |
- | **Raw Data:** The raw data transform presents | + | Note that transforms are applied to fields before |
- | **First Five Characters: | + | The following table shows the Operators available |
- | **First Continuous Non-space String:** | + | {{tablelayout? |
- | First Continuous Non-space String is a non-aggregate transform. As the name indicates, it returns the first word (or string | + | ^ Operator |
+ | | Equals | ||
+ | | Contains Matching Substring | ||
+ | | Contains Matching Substring (Ignore Case) | This operator is identical to Contains Matching Substring, except | ||
+ | | 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”); | ||
+ | | Greater Than or Equal To | Greater Than or Equal To is identical to Greater Than, except it also returns | ||
+ | | Less Than | This operator returns TRUE if a field is less than, lower than, earlier than, ascibetically 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 | ||
+ | | Not Between | ||
+ | | 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 ' | ||
+ | | Is Not NULL or Blank | This operator returns TRUE for fields that hold some non-trivial | ||
- | **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. | + | //Source: {{ : |
- | {{tablelayout? | ||
- | | Last Name | Claims Returned Count | Patron ID | | ||
- | | Levine | ||
- | | Maddox | ||
- | | Smith | 5 | 43547 | | ||
- | |||
- | 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 // | ||
- | |||
- | **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. Last Value is available for the //text//, //int//, //money//, and // | ||
- | |||
- | **Lower Case:** Lower Case presents the text stored in a field as all lower case. So, if a field contains the text “Smith, | ||
- | |||
- | **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 // | ||
- | |||
- | **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 // | ||
- | |||
- | **Substring: | ||
- | |||
- | **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. | ||
- | |||
- | **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' | ||
- | |||
- | **Date:** This transform presents a timestamp as a human-readable date. Date is available for // | ||
- | |||
- | **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 // | ||
- | |||
- | **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. Day of Month 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. Day of Week is available for // | ||
- | |||
- | **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 // | ||
- | |||
- | **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.” Hour is available for // | ||
- | |||
- | **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.” Month Name is available for // | ||
- | |||
- | **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. Month of Year is available for // | ||
- | |||
- | **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 // | ||
- | |||
- | **Quarter of Year:** Quarter of Year displays the numerical quarter – for example, a timestamp for December 12, 2008 would appear as “4.” Quarter of Year is available for // | ||
- | |||
- | **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. Quarters Ago is available for // | ||
- | |||
- | **Week of Year:** | ||
- | Week of Year displays the numerical week of a year (1–52) for which a timestamp is set. Week of Year is available for // | ||
- | |||
- | **Year:** The Year transform 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 ===== |
reports/egreports/data_types.1650994299.txt.gz · Last modified: 2022/04/26 17:31 by smorrison