1.1. Reports Overview Webinar Video

1.2. Reports Permission Group

2.1. What Are Report Folders?

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.

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:

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

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. 

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

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.

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

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.

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.

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

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.

   

2. Click on Create a new Template for this folder.

   

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

   

   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.

   

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.

   

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.

   

   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.

   

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.

   

   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.

   

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.

    

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

    

    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.

    

    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.

    

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

    

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

    

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.

    

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.

    

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

    

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.

    

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

    
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.

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.

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. 

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.

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.

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.

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.

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

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

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 

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.

Each data type has its own characteristics and uses:

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

A Field Transform tells the reporter how to process a field for output. Different data types have different transform options.

Raw Data. To display a field exactly as it appears in the database use the Raw Data transform, available for all data types.

Count and Count Distinct. These transforms apply to the id data type and are used to count database records (e.g. for circulation statistics). Use Count to tally the total number of records. Use Count Distinct to count the number of unique records, removing duplicates.

To demonstrate the difference between Count and Count Distinct, consider an example where you want to know the number of active patrons in a given month, where ``active" means they borrowed at least one item. Each circulation is linked to a Patron ID, a number identifying the patron who borrowed the item. If we use the Count Distinct transform for Patron IDs we will know the number of unique patrons who circulated at least one book (2 patrons in the table below). If instead, we use Count, we will know how many books were circulated, since every circulation is linked to a patron ID and duplicate values are also counted. To identify the number of active patrons in this example the Count Distinct transform should be used.

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.

Aggregate Types

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.

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.