HomeReports in EvergreenPrinter Friendly Version

Reports in Evergreen

Documentation of the reporting module in Evergreen and NC Cardinal reporting procedures.

Incorporates elements of Evergreen Documentation under Creative Commons Attribution Share-Alike 3.0 License (https://creativecommons.org/licenses/by-sa/3.0/legalcode) and available to share under the same license.

1. Introduction to Reports

1.1. Reports Overview Webinar Video


1.2. Reports Permission Group

Evergreen permits access to report folders based on login account. All permission groups in NC Cardinal have reporting permissions, so any staff member can create, clone, and run reports using their individual login or a shared generic circulation login account. By default, report folders are not shared, so staff with individual login access accounts would be the only ones to see the reports they created or cloned into their reports folders (My Folders). 

Because some library systems assign individual accounts to all staff members and do not use generic circulation accounts, NC Cardinal has a Reports permission group available to systems who wish to consolidate some reporting into one generic login access for multiple staff members. Please consult with your System Login Access Manager or director to find out how your library system shares reports.


2. Folders

2.1. What Are Report Folders?

There are three main components to reports: Templates, Reports, and Output.

Templates = the report structure
Reports = the run instance
Output = the data extracted

Each of these report components must be stored in a folder. Folders can be private (accessible to your login only) or shared with other staff at your library, other libraries in your system or with the consortium. It is possible to selectively share only certain folders and/or subfolders.

There are two parts to the folders pane. The My Folders section contains folders created with your Evergreen login access account. Folders that other users have shared with you appear in the Shared Folders section under the username of the sharing account.


2.2. Creating Folders

Whether you are creating a report from scratch or working from a shared template you must first create at least one folder in each of the three functional components in My Folders.

The steps for creating folders are the same within each component. It is easier to create folders with the same name for Templates, Reports, and Output at one time, though it is possible to do so at any time. This example demonstrates creating a template folder.

  1. Click on the word Templates in the My Folders section. A box will pop up to the side allowing you to create a new sub-folder.
  2. Name the folder  for example Circulation.* Select Share or Do not share from the Share this folder dropdown menu.


    3. If you want to share your folder, select who you want to share this folder with from the dropdown menu.
    4. Click Create Sub Folder.
    5. Click OK.
    6. Next, click on the word Reports to create a folder for the report instance to be saved in.
    7. Repeat steps 2-5 to create a Reports folder also named Circulation.*
    8. Finally, click on the word Output to create a folder for the report data output to be saved in.  
    9. Repeat steps 2-5 to create an Output folder named Circulation.*

The folders you just created will now be visible by clicking the arrows in My Folders. Bracketed after the folder name is the org unit the folder is shared with. For example, if you share with your library branch or system, the short code name for the branch or system will appear in parentheses after the folder name. If you share with the consortium, (CARDINAL) will appear after the folder name. If it is not a shared folder, there will be nothing after the folder name. You may create as many folders and sub-folders as you like.


*Note: Using a parallel naming scheme for folders in Templates, Reports, and Output helps keep your reports organized and easier to find

2.3. Managing Folders

Once a folder has been created you can change the name, delete it, create a new sub-folder, or change the sharing settings. This example demonstrates changing a folder name:


  1. Click on the folder that you wish to rename.
  2. Click Manage Folder.
  3. Select Change folder name from the dropdown menu and click Go.
  4. Enter the new name and click Submit.
  5. Click OK.
  6. You will get a confirmation box saying Action Succeeded. Click OK.

 If you choose one of the other options, you will follow similar steps:

Selecting Delete this folder will delete anything contained in the folder, such as templates, report instances, or output data, so be sure that is what you intend to do.

Select Create a new sub-folder to create a sub-folder that is tiered within the folder you choose. You can create as many sub-folders within sub-folders as you find useful.


Select Share this folder to share a folder that is not currently shared or Hide (unshare) this folder to hide a shared folder or share it with a different organizational unit (e.g. from branch to system).

3. Pre-existing Templates

3.1. Find Existing Templates

The NC Cardinal team has developed an extensive array of report templates that provide the data that most libraries need. These reports are organized in the Centralized Report Templates under Shared Folders for your use, so please look here first for a suitable existing template. Read the descriptions carefully, as some reports are similar but use different filtering options. 


When exploring template folders, be sure to select All instead of the default of 10 to see every option available in each folder.


If you are unable to find what you are looking for, please submit a help ticket and the NC Cardinal support team will find or create a suitable template for you. 

3.2. Cloning Templates

The steps below assume you have already created at least one Templates folder. If you haven’t done this, please see Creating Folders

  1. Access the reports interface from the Admin menu under Local Administration>Reports
  2. Under Shared Folders expand the Templates folder and the subfolder of the report you wish to clone. To expand the folders click on the grey arrow or folder icon. Do not click on the blue underlined hyperlink.
  3. Click on the subfolder.
  4. Select the template you wish to clone. From the dropdown menu choose Clone selected templates, then click Submit. [Note: by default Evergreen only displays the first 10 items in any folder. To view all content, change the Limit output setting from 10 to All.] 
  5. Choose the folder where you want to save the cloned template, then click Select Folder. Only template folders created with your account will be visible. If there are no folders to choose from please see Creating Folders.
  6. The cloned template opens in the template editor. From here you may modify the template by adding, removing, or editing fields and filters as described in Creating Templates. Template Name and Description can also be edited. When satisfied with your changes click Save.
  7. Click OK in the resulting confirmation window.

Once saved it is not possible to edit a template. To make changes, clone a template and change the clone.

Note — Be sure to clone a template from any shared folder into your own template folder. DO NOT run it from the shared folder as that can interfere with the periodic deletion and update of shared templates.

Report templates used in old login accounts can be cloned for use in other login accounts by first logging into Evergreen with the old account and sharing the template folders to the system level. To share a report folder, just go into that report folder, select Manage Folder, select Share this folder from the dropdown menu, and select your system.

Then, the folder is shared with anyone logging in from any workstation in your system (rather than with any particular login account). Log into the new login access account, find the old account name under Shared Folders, and open. Check the box beside the report you want and choose Clone selected template from the dropdown menu. Then, select the new folder to clone the template into [Note: you must create folders in the new login account first].

Only Templates folders are useful to share. Reports folders contain past report run instances or recurring reports and there is no mechanism to transfer data from one Reports folder to another. The run instance of recurring reports will not transfer from one login account to another, however you can clone the template into the new login account templates folder and create a new recurring report instance. Output folders do not share well with other login accounts. Instead, save any relevant Excel spreadsheets from the old login access account directly onto your hard drive.

3.3. Transferring Report Templates

Report templates used by staff members who may have left the library or changed work responsibilities can be cloned for use by different staff members by first logging into the old accounts and sharing the template folders to the system level. The System Login Access Manager for your library system should be able to perform this action for you. To share a report folder, just go into that report folder, select Manage Folder, select Share this folder from the dropdown menu, and select your system.


Then, the folder is shared with anyone logging in from any workstation in your system (rather than with any particular login account). Log into the new login access account, find the old account name under Shared Folders, and open. Check the box beside the report you want and choose Clone selected template from the dropdown menu. Then, select the new folder to clone the template into (note you have to create folders in your new login account first).

One of the things that does not share well are Output folders. Instead, save any relevant Excel spreadsheets from the old login access account directly onto your hard drive. 


4. Editing and Creating Templates

4.1. The Template Editor

All reports within Evergreen begin with creating a template.  Templates identify a report’s:

  • data source
  • fields selected from the data source
  • how information appears, for example date formats
  • criteria used to run the report


Template Editor Window  

The Template Editor window is divided into two sections; the Database Source Browser (upper pane) and the Template Configuration section (lower pane). Within the two panes, there are several smaller sub-panes.


 

 

 

 

 

 

 

 



 

Each sub-pane has a specific purpose as noted in the graphic below:


The Database Source Browser

The Database Source Browser displays a list of data sources and allows you to select fields which will display as columns within a report.   It also enables you to select fields for filtering and selection criteria.

 

The Database Source Browser is made up of several parts:

  1. Sources pane – ‘where to find your data’
  2. Field Name pane – ‘what fields to include in the report’
  3. Field transform pane – ‘how you want the field to be viewed’

Sources Pane

This contains a tree view of the source database table and its links to other tables.  If the linked database table contains references to other tables, then those can also be expanded in the tree view.








When a table name is highlighted in the Source Name textbox, the fields of that table are displayed in the Field Name pane.

 

Field Name Pane

Once a table is selected in the Source Name textbox, the table’s fields are listed in the Field Name pane.  The Field Name pane displays the field name and data type of each field.  Any of these fields can be selected for display or filter. 








When a field is highlighted, the Field Transform textbox displays transforms that can be applied to the data.


Field Transform Pane

This pane contains a list of available field transforms for the field that is highlighted in the Field Name pane.   For example, when ‘Daytime Phone” is highlighted, the Field Transform Pane displays the available, valid choices for displaying the field in a report.  These choices are – count (phone numbers can be counted), First 5 characters (the first five characters can be displayed), First Value (the first digit of a phone can be displayed), etc. 

For all field transforms, the “raw data’ output type generally displays the field “as it was typed into the database.”  Appendix B lists all transform definitions within Evergreen.

 

 





The Template Configuration Pane

The Template Configuration pane (bottom half of the Template Editor Window) lists the title of the report template, a report description, the fields that have been selected for a report, as well as any filters/criteria for running a report.  


The Template Configuration Pane is made up of several parts: 

  1. Name and Description Fields
  2. Documentation URL field
  3. Displayed Fields tab 
  4. Base filters tab
  5. Aggregate filters tab 
  6. Source specifier pane 

Name And Description Text Fields









The name and description of the new template should be entered into this pane.  The ‘Description’ field is optional, but it is a good practice to describe the display fields, filters, transforms, and operators used in the template for ease of reference. 

 

 The Name field must contain a name that is unique to the template folder in order to save the template.

Documentation URL

You can add a link to local documentation that can help staff create a report template. To add documentation to a report template, click Admin → Local Administration → Reports, and create a new report template. A new field, Documentation URL, appears in the Template Configuration panel. Enter a URL that points to relevant documentation.

Reports1

The link to this documentation will also appear in your list of report templates.

Reports2a


Displayed Fields Tab

 This tab lists the fields that will be displayed in the report’s output.  The pane shows the column header that will appear in the output which can be changed by highlighting the field and clicking “Alter Display Header”.







 


The “Field Transform” column shows how the data will be displayed.  To change the transform for a field, highlight the field and click “Change Transform”.  The column picker (to the far right of the box) allows you to display “Field Name” and “Field Transform Type” columns.  The “Field Name” column is especially useful in identifying fields after the Display Name has been altered.

 

The “Move Up” and “Move Down” buttons determine the order of the columns in the final output; the field that is listed first will display in the first column of the output.

Base and Aggregate Filters Tabs

Within Evergreen, the filter tabs determine

 

  1. What information will be needed/asked when a report is run; for example, a date range
  2. What values will be used as defaults when running a report; for example, if the report listed all active patrons, the report’s base filter would set the Active? Field to true.


The Base Filter Tab filters upon a single piece of information.  For example, a base filter is set up for a report that includes a date range, each row of information within the database will be compared to the date range and either included or excluded from the report.  In the example below, the report “Count Items by Circulation Modifier” has a base filter where the flag Is Deleted is set to FALSE.  When the report is run, each item’s deleted flag will be reviewed and excluded from the report.

 

 









The Filter Field column displays the friendly name of the field; this name cannot be changed, but is useful for verifying that the filters have been correctly chosen.

 

The Field Transform column displays the transform currently applied to the field; the transform determines how the field will ‘look’ when it is compared against the user-specified value. 

 

The Operator column displays the type of comparison that will be made.  The Change Operator button allows the user to choose different operators, such as “In List,” “Equal to,” “On or Before,” etc...

 

The Value column displays any parameters that have been set up for this filter.  Values can be set or removed using the “Change Value” and “Remove Value” buttons. 

 

The column picker ( to the far right of the box) also allows the user to display Field Name, Data Type,  and Field Transform Type columns.

 

Aggregate Filter Tab

The Aggregate Filters tab lists fields that have been selected to be filtered across numerous items/patron rows, etc.  For example, a report for all items that have more than 10 holds for the month is needed.  An aggregate filter would be needed to create this report, where the aggregate filter includes a “count of the number of holds over a given number”.  The reports program will sum all the rows of items and then apply that sum to display the rows of items that meet the criteria.

Source Specifier

This pane displays the data sources that are are used as Displayed Fields or Base or Aggregate Filters.  When a single source is highlighted with the Displayed Fields tab, only fields from that table are visible in the Displayed Fields, Base filters, and Aggregate Filters tabs.

 








**To select a source, click on it.  To ‘re-display’ all data sources, click CTRL-CLICK.

Field Hints

Descriptive information about fields or filters in a report template can be added to the Field Hints portion of the Template Configuration panel. For example, a circulation report template might include the field, Circ ID. You can add content to the Field hints to further define this field for staff and provide a reminder about the type of information that they should select for this field.

To view a field hint, click the Column Picker, and select Field Hint. The column will be added to the display.

Reports2

To add or edit a field hint, select a filter or field, and click Change Field Hint. Enter text, and click Ok.

Reports3


4.2. Creating Templates

Once you have created a folder, the next step in building a report is to create or clone a template. Templates allow you to run a report more than once without building it anew every time, by changing definitions to suit current requirements. For example, you can create a shared template that reports on circulation at a given library. Then, other libraries can use your template and simply select their own library when they run the report.

It may take several tries to refine a report to give the output that you want. It can be useful to plan out your report on paper before getting started with the reporting tool. Group together related fields and try to identify the key fields that will help you select the correct source.

It may be useful to create complex queries in several steps. For example, first add all fields from the table at the highest source level. Run a report and check to see that you get results that seem reasonable. Then clone the report, add any filters on fields at that level and run another report. Then drill down to the next table and add any required fields. Run another report. Add any filters at that level. Run another report. Continue until you’ve drilled down to all the fields you need and added all the filters. This might seem time consuming and you will end up cloning your initial report several times. However, it will help you to check the correctness of your results, and will help to debug if you run into problems because you will know exactly what changes caused the problem. Also consider adding extra fields in the intermediate steps to help you check your results for correctness.

This example illustrates creating a template for circulation statistics. This is an example of the most basic template that you can create. The steps required to create a template are the same every time, but the tables chosen, how the data is transformed and displayed, and the filters used will vary depending on your needs.

Choosing Report Fields

  1. Click on the My Folder template folder where you want the template to be saved.

    create-template-1
  2. Click on Create a new Template for this folder.

    create-template-2
  3. You can now see the template creating interface. The upper half of the screen is the Database Source Browser. The top left hand pane contains the database Sources drop-down list. This is the list of tables available as a starting point for your report. Commonly used sources are Circulation (for circ stats and overdue reports), ILS User (for patron reports), and Item (for reports on a library’s holdings).

    create-template-3

    The Enable source nullability checkbox below the sources list is for advanced reporting and should be left unchecked by default.

  4. Select Circulation in the Sources dropdown menu. Note that the Core Sources for reporting are listed first, however it is possible to access all available sources at the bottom of this dropdown menu. You may only specify one source per template.

    create-template-4
  5. Click on Circulation to retrieve all the field names in the Field Name pane. Note that the Source Specifier (above the middle and right panes) shows the path that you took to get to the specific field.

    create-template-5
  6. Select Circ ID in the middle Field Name pane, and Count Distinct from the right Field Transform pane. The Field Transform pane is where you choose how to manipulate the data from the selected fields. You are counting the number of circulations.

    create-template-6

    Field Transforms have either an Aggregate or Non-Aggregate output type. See the section called Field Transforms for more about Count, _Count Distinct, and other transform options.

  7. Click Add Selected Fields underneath the Field Transform pane to add this field to your report output. Note that Circ ID now shows up in the bottom left hand pane under the Displayed Fields tab.

    create-template-7
  8. Circ ID will be the column header in the report output. You can rename default display names to something more meaningful. To do so in this example, select the Circ ID row and click Alter Display Header.

    create-template-8

    Double-clicking on the displayed field name is a shortcut to altering the display header.

  9. Type in the new column header name, for example Circ count and click OK.

    create-template-9
  10. Add other data to your report by going back to the Sources pane and selecting the desired fields. In this example, we are going to add Circulating Item -→ Shelving Location to further refine the circulation report.

    In the top left hand Sources pane, expand Circulation. Depending on your computer you will either click on the + sign or on an arrow to expand the tree.

    create-template-10

    Click on the + or arrow to expand Circulating Item. Select Shelving Location.

    create-template-11

    When you are creating a template take the shortest path to the field you need in the left hand Sources pane. Sometimes it is possible to find the same field name further in the file structure, but the shortest path is the most efficient.

    In the Field Name pane select Name.

    create-template-12

    In the upper right Field Transform pane, select Raw Data and click Add Selected Fields. Use Raw Data when you do not wish to transform field data in any manner.

    create-template-13

    Name will appear in the bottom left pane. Select the Name row and click Alter Display Header.

    create-template-15
  11. Enter a new, more descriptive column header, for example, Shelving location. Click OK.

    create-template-16
  12. Note that the order of rows (top to bottom) will correspond to the order of columns (left to right) on the final report. Select Shelving location and click on Move Up to move Shelving location before Circ count.

    create-template-17
  13. Return to the Sources pane to add more fields to your template. Under Sources click Circulation, then select Check Out Date/Time from the middle Field Name pane.

    create-template-19
  14. Select Year + Month in the right hand Field Transform pane and click Add Selected Fields

    create-template-20
  15. Check Out Date/Time will appear in the Displayed Fields pane. In the report it will appear as a year and month (YYYY-MM) corresponding to the selected tranform.

    create-template-21
  16. Select the Check Out Date/Time row. Click Alter Display Header and change the column header to Check out month.

    create-template-22
  17. Move Check out month to the top of the list using the Move Up button, so that it will be the first column in an MS Excel spreadsheet or in a chart. Report output will sort by the first column.
create-template-23

Note the Change Transform button in the bottom left hand pane. It has the same function as the upper right Field Transform pane for fields that have already been added.

create-template-24

5. Generating reports

5.1. Run a Report Template

Now you are ready to run a report from your template.

Note — Be sure you select a template from one of your own template folders. DO NOT run reports from a shared folder. 

  1. In the My Folders section click the arrow next to Templates to expand this folder and select Circulation

  2. Select the box beside the template you wish to run in your templates folder. Select Create a new report from selected template from the dropdown menu. Click Submit. The Report definition interface will open.                                        

  3. Template NameTemplate Creator, and Template Description are for informational purposes only. They are hard coded when the template is created. At the report definition stage it is not possible to change them.

  4. Report Name is required. Reports stored in the same folder must have unique names.

  5. Report Description is optional but may help distinguish among similar reports.

  6. Report Columns lists the columns that will display in the output. This is derived from the template and cannot be changed during report definition.

  7. Pivot Label Column and Pivot Data Column are optional. Pivot tables are a different way to view data. If you currently use pivot tables in MS Excel it is better to select an Excel output and continue using pivot tables in Excel.

  8. You must choose a report folder to store this report definition. Only report folders under My Folders are available. Click on the desired folder to select it.

  9. The next section will vary for each report definition based upon the filters used in the report template.  In the example, select values for the Circulation > Check Out Date/Time. Use the calendar widget or manually enter the desired date range. (Under some circumstances, it may be possible to select relative dates for a report definition, depending upon the design of the template.)

  10. Select a value for the Circulation > Circulating Library.

  11. Next, you will select output options. Select one or more output formats. In this example the report output will be available as an Excel spreadsheet only. There are other options, such as a CSV file, an HTML table (for display in the staff client or browser), or a bar chart.                                                     

  12. Select Run as soon as possible for immediate output. It is also possible to set up reports that run automatically at future dates or intervals. If you want the report to be recurring, check the Recurring Report box and select the Recurrence Interval as described in Recurring Reports. In this example, as this is a report that will only be run once, the Recurring Report box is not checked.

  13. It is optional to fill out an email address where a completion notice can be sent. The email will contain a link to password-protected report output (staff login required). You can enter one email address or multiple addresses separated by commas.

  14. Select a folder for the report’s output.

  15. Click Save Report. You will get a confirmation dialogue box that the Action Succeeded. Click OK

  16. Once saved, the report will appear in the Reports and Output folders you selected and remain until you delete them or they are removed by a long-term data cleanup process.


5.2. Viewing and Editing Report Parameters

To view the parameters of a report, select the report that you want to view from the Reports folder, and click View. This will enable you to view the report, including links to external documentation and field hints. However, you cannot make any changes to the report.

Reports4

To edit the parameters of a report, select the report that you want to view from the Reports folder, and click Edit. After making changes, you can Save [the] Report or Save as New. If you Save the Report, any subsequent report outputs that are generated from this report will reflect the changes that you have made.

Reports6

In addition, whenever there is a pending (scheduled, but not yet started) report output, the interface will warn you that the pending output will be modified. At that point, you can either continue or choose the alternate Save as New option, leaving the report output untouched.

If, after making changes, you select, Save as New, then you have created a new report by cloning and amending a previously existing report. Note that if you create a new report, you will be prompted to rename the new report. Evergreen does not allow two reports with the same name to exist. To view or edit your new report, select the reports folder to which you saved it.

Reports5

5.3. Recurring Reports

Recurring reports are a useful way to save time by scheduling reports that you run on a regular basis, such as monthly circulation and monthly patron registration statistics. When you have set up a report to run on a monthly basis you’ll get an email informing you that the report has successfully run. You can click on a link in the email that will take you directly to the report output. You can also access the output through the reporter interface as described in Viewing Report Output.

To set up a monthly recurring report follow the procedure in Run a Report Template but make the changes described below.

  1. Select the Recurring Report check-box and set the recurrence interval to the desired amount of time (e.g. 1 month).
  2. Do not select Run ASAP. Instead schedule the report to run early on the first day of the next month. Enter the date in YYYY-MM-DD format.
  3. Ensure there is an email address to receive completion emails. You will receive an email completion notice each month when the output is ready.
  4. Select a folder for the report’s output.
  5. Click Save Report.
  6. You will get a confirmation dialogue box that the Action Succeeded. Click OK.


After following these instructions, you will get an email on the 1st of each month with a link to the report output. By clicking this link it will open the output in a web browser. It is still possible to login to the staff client and access the output in Output folder.

How to stop or make changes to an existing recurring report? Sometimes you may wish to stop or make changes to a recurring report, e.g. the recurrence interval, generation date, email address to receive completion email, output format/folder or even filter values (such as the number of days overdue). To make changes to an existing report, follow the instructions in Viewing and Editing Report Parameters. Or, if no longer needed, you delete the recurring report from the report folder. Please note that deleting a report also deletes all output associated with it.


6. Report Output

6.1. Viewing Report Output

Once a report definition has been saved, the output is stored in the specified Output folder and will remain there until manually deleted. If the report was set for a future date or as a recurring report, the report instance will show under Pending Items. Once the report has finish, it will show under Completed Items.


  1. To view report output in the staff client, open the reports interface and click on Output to expand the folder. Select the folder where you saved the report output.
  2. View report output is the default selection in the dropdown menu. Click the checkbox next to the report you wish to view and click Submit.

  3. A new tab will open for the report output. Select either Tabular Output or Excel Output. If Bar Charts was selected during report definition the chart will also appear.

  4. If you want to manipulate, filter or graph this data, Excel output is very useful and will generate a ".xlsx" file.

  5. If an email address was entered during the report definition stage, an email will be sent when the report completes that includes a link to the output file. Staff must be logged into an Evergreen account with access to the designated Output folder in order to view the report output. 

7. Resources and Example Reports

7.1. Troubleshooting Tips

  • Don’t go too far down the data source tree to select fields; If you have a choice in data sources, choose the one that has the LEAST number of tree branches.

 

For example, both the Item and Bibliographic Record sources contain title and author information.  Yet if the report to be created must include circulation modifier in addition to title, the Item data source is the better choice.  To obtain the circ modifier, one must go down six (6) branches within the Bibliographic Record data source.  For Item, only two (2) branches are necessary.

 

  • For count reports, run a report without the ‘count’ field – i.e. look at the ‘raw’ date that is being counted.
  • For circulation modifiers, use the ‘code’ data type for Base Filters; Use the ‘name’ field for field display.
  • For shelving locations, use Location IDs for Base Filters;  Location ID is an integer field.
  • If a report is blank, i.e. no rows display in a report, take out any Base Filters and re-run the report to see if your criteria is not what you were expecting.
  • Something looks funny?  Submit a help ticket to the State Library staff and ask for help.

 

Example:  Counting Discarded Items at various levels

 

Counting at Call Number/Volume level:

Owning Library

# of Items Deleted

Total Price

Library A

127

 

Library B

2

 

Library C

250

 

Library D

77

 

Library E

990

5246.75





Counting at Item level:



Library A

676

$5710.76

 

 

 







7.2. Hopeless Holds Video and Instructions



Here are instructions for using the Hopeless Holds reports created by Lise Keppler (Forsyth) who presented in the webinar.

This process generates a list of holds on bibliographic records that no longer have any viable copies, built using two Evergreen reports combined in MS Excel with the VLOOKUP function.

Multi-type holds are ignored since they may be filled by items in more than one bibliographic record.

Holds that can be transferred to other bibliographic records with viable copies or re-targeted by merging duplicate bibliographic records will be filled.

Patrons with holds that remain unfillable will be notified and the holds will be cancelled.



7.3. Other Consortia

Georgia Pines  keep in mind that Pines "Classic View" report templates do not work for us

Missouri Evergreen

Evergreen Community reports video playlist 

Appendices

1. Appendix A: Data Types

A Data type is the format that determines how a field is stored within Evergreen. For example, dates are stored in a timestamp format and the title of an item is stored as text. Both timestamp and text are specific data types which tell Evergreen what can be done with the field.

The central column of the Database Source Browser lists Field Name and Data Type for the selected database table.

view-output-2

Each data type has its own characteristics and uses:

Data Type Description Notes

id

Unique number assigned by the database to identify a record

A number that is a meaningful reference for the database but not of much use to a human user. Use in displayed fields when counting records or in filters.

int Integer The field contains numbers only
interval The field contains 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
money Number in dollars

Fields with the money data type contain monetary amounts, such as the amount billed to a patron on a billing line item.

text

Text field

Usually uses the Raw Data transform.

timestamp

Exact date and time

Select appropriate date/time transform. Raw Data includes second and timezone information, usually more than is required for a report.

bool

True or False

Commonly used to filter out deleted item or patron records.

org_unit

A number representing a library, library system, or federation

Abbreviation of "organizational unit". When you want to filter on a library, make sure that the field name is on an org_unit or id data type.

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.


When to use what?

 

  • Select the  org_units  data type when working with Base Filters;
  • Select text, money  when working with selecting columns for a report;
  • Select id  when working with counts and aggregate filters; Also use id when working with circulation modifier selection within the Base Filters pane.
  • Select bool  when working with true/false fields for example Active? Or Deleted?
  • Links are not selectable for display or report inclusion; A link field means you MUST find your data field in another part of the data source tree.


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?

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


3. Appendix C: Operators

Operators describe the ways two pieces of data can be compared to one another.  Operators are used when creating filters to determine which database records should be included in a report.  Different data types have different transform options.

 

Operator

Description


Equals

compares a field’s value and returns any data that matches EXACTLY


Contains Matching Substring

This operator checks if a field contains a specific, case-sensitive substring 

 

Contains Matching Substring (Ignore Case)

This operator identical to Contains Matching Substring, except it is not case-sensitive.

Greater Than

Returns information where the field value is ‘greater than’ the value specified.  For dates, beyond a given date or later than a given date;  For text fields, this means 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 data that is equal to your parameter.

Less Than

Returns information where the field value is ‘less than’ the value specified.  For dates, before a given date ;  For text fields, this means lower strings (for example “bananas” is less than “oranges”);

Less Than Or Equal To

This operator is identical to Less Than except it returns information that is equal and less than.

In List

Enables you to pick from a list of choices;

Not In List

Not In List is the opposite of In List – Enables you to exclude from a list of choices;

Between

Between requires you to specify two parameters ; mostly used for date ranges

Not Between

Like Between, Not Between requires you to specify two parameters; it returns TRUE for fields that are not between your parameters.

Is NULL

Is NULL returns values that hold no data.    It is recommended to use Is NULL or Blank instead of Is NULL

Is Not NULL

Returns data that hold some data;

Is NULL or Blank

Is NULL or Blank returns TRUE for fields that either hold no data or do hold 'empty' data.

Is Not NULL or Blank

This operator returns TRUE for fields that hold some non-trivial data.

 

 

When to use what?

 

  • Use In List to select from a list of branch libraries or a list of circulation modifiers
  • Between is date-inclusive.  For example, the date range Jan 1, 2012 – Jan 30,2012 includes the dates Jan 1 and Jan 30.
  • Use Contains Matching Substring (Ignore Case) when possible.


4. Appendix D: Copy Status

Here are the current copy statuses in NC Cardinal. Some reports give the ID vs the name.

ID Name Holdable OPAC visible Sets Copy Active Is Available Restrict Copy Delete
0 Available TRUE TRUE TRUE TRUE FALSE
1 Checked out TRUE TRUE TRUE FALSE TRUE
2 Bindery FALSE FALSE FALSE FALSE FALSE
3 Lost FALSE FALSE FALSE FALSE TRUE
4 Missing FALSE FALSE FALSE FALSE FALSE
5 In process TRUE TRUE FALSE FALSE FALSE
6 In transit TRUE TRUE FALSE FALSE TRUE
7 Reshelving TRUE TRUE TRUE TRUE FALSE
8 On holds shelf TRUE TRUE TRUE FALSE TRUE
9 On order TRUE TRUE FALSE FALSE FALSE
10 ILL FALSE FALSE TRUE FALSE FALSE
11 Cataloging FALSE FALSE FALSE FALSE FALSE
12 Reserves FALSE FALSE TRUE FALSE FALSE
13 Discard/Weed FALSE FALSE FALSE FALSE FALSE
14 Damaged FALSE FALSE FALSE FALSE FALSE
15 On reservation shelf FALSE FALSE TRUE FALSE FALSE
16 Long Overdue FALSE FALSE FALSE FALSE TRUE
17 Lost and Paid FALSE FALSE FALSE FALSE TRUE
18 Canceled Transit TRUE TRUE FALSE FALSE FALSE
101 Never Returned FALSE FALSE FALSE FALSE FALSE
102 Claimed Lost FALSE FALSE FALSE FALSE FALSE
103 Storage TRUE TRUE FALSE FALSE FALSE
104 On Display TRUE TRUE TRUE FALSE FALSE
105 In Transit TRUE TRUE FALSE FALSE FALSE
106 Repair TRUE TRUE FALSE FALSE FALSE
107 At Children's Desk TRUE TRUE TRUE FALSE FALSE
108 At Circulation Desk TRUE TRUE FALSE FALSE FALSE
109 In Use for Programs FALSE TRUE FALSE FALSE FALSE
110 Noncirculating FALSE TRUE FALSE FALSE ?
134 Digitization in Process FALSE FALSE FALSE FALSE ?