Creating Report Templates
Introduction
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.
Creating a Template
Before creating a completely new template, it is recommended that you check your organization’s shared templates to see if there is a template that can be modified to give you the information you need. The template search can be used to find and identify potential templates. Please note that the search does not provide the folder name where the template is stored. Be sure to leave out any special characters or punctuation in your search.
See Cloning Report Templates for more information.
Creating a template is complex and requires some understanding of the Evergreen database and how Evergreen handles various records when performing tasks on the staff client. It can be useful to plan out your template before getting started with the reporter. Think about what type of records you will be reporting on (circulation, item, bibliographic, patron, etc.) and what information you want to see displayed in your report output. You also want to think about what filters should be used so the report output contains only the records you’re interested in.
-
Navigate to Administration → Reports.
-
In the My Folders section, select the arrow beside the Templates folder to see your template folders. Use the arrows to expand the folder tree until you find the folder where you’d like to create the new template.
-
Select the folder name.
-
In the folder, select Create a new Template for this folder.
-
The Report Template Editor will open. See Report Template Edotpr for information on using the editor.
-
Choose a Core Source. See Core Source.
-
Add Display Fields. See Adding a Display Field.
-
Add Filters. See Adding a Filter.
All report templates MUST have at least one filter to choose a specific library. This filter is important as it allows staff to restrict the data in the report output to only data relevant to their library.
-
Add hardcoded filter values for any filters that should always use the same value. See Reporter Basics for a list of filters that are commonly hardcoded.
-
Select Save Template.
-
Your new template can now be found in the folder you saved it in.
-
Run a report from the new template to see if the desired output is generated.
If the path for one of your display fields or filters is incorrect, you will get an error instead of the expected report output. Sometimes the error text will give you a clue about what isn’t working.
You can modify your template and try again. It may be helpful to review Troubleshooting Report Templates.
-
Repeat the above steps until your template works as desired.
|
It is not unusual to create multiple versions of a template when working to modify a template. Once you have a template that works as desired, it is recommended that you delete the interim versions of the template. See Deleting Report Templates. |
Report Template Editor
The report template editor is made up of three sections:
-
Basic Information
-
Display Field and Filter Selector
-
Display Field and Filter Tabs
Basic Information Section
The Basic Information section of the report template editor is where you can add or update the template name, description, and documentation URL.
|
The names of templates in a folder must be unique. Evergreen will not allow a template using an identical name to be saved into the same folder. |
If you have local documentation online that pertains to a report, you can add it in the Documentation URL field and it will be included as a link in the report output.
Display Field and Filter Selector
The Display Field and Filter Selector is where you build your display fields and filters for your template.
Core Source
Every report template starts with a core source. This is the database table from which your template is built. The top of the left-hand pane of the Display Field and Filter Selector section of the Report Template Editor contains the Core Sources dropdown menu.
When modifying a template, NEVER change the core source. This will scrap your existing template and you will have to start again from scratch.
When creating a new template, the first thing you need to do is figure out which core source makes the most sense for the data you want to see in your report output. If you need information from other tables, you’ll follow the links to those tables when adding display fields and filters.
The three most commonly used core sources are Circulation, Item, and ILS User. Templates for monthly circulation statistics and templates for tracking overdue items use the core source Circulation. Templates about the state of your collection use the core source Item. Templates containing counts or lists of your patrons use the core source ILS User. More information can be found in Commonly Used Tables.
You can link to other database tables to include information contained in those tables but not every table links directly. For example, patrons and items aren’t directly linked to each other. Information about items out to a patron is held in the circulation table while information about items on hold is held in the hold request table. More information can be found in documentation about the Evergreen Database.
Nullability
The Nullability checkbox is beside the core source list. In general, this checkbox should be left un-checked.
You may wish to use nullability if your report output includes null values that you don’t want included or if your report output doesn’t include null values that you do want to see. Nullability allows you to have more control over whether or not null values in fields are included in your report output. It does this by allowing you to manually select the way in which Evergreen is joining the database tables when running a report from the template. The type of join used will determine what kind of data can display a null value in your report output.
When Nullability is checked, the following options show for joins between tables:
-
Default = defined within Evergreen. See Evergreen Database for more information.
-
Child Nullable = depends on the default join (usually left). Evergreen will include all records in the linked child table.
-
Parent Nullable = depends on the default join (usually right). Evergreen will include all records in the linked parent table.
-
None Nullable = equivalent to inner join. Evergreen will only include data that is in both tables.
|
You must use the same nullability selection on all display fields and filters that you add from the same table. |
To learn more about nullability, see the Evergreen conference presentation Thanks for Nothing: Nullability Selection in Evergreen Reports.
Display Fields
Display fields are the columns of information that will display on your report output. The Display Fields tab is the active tab by default in the Report Template Editor.
|
The action Change Column Documentation on the Display Fields tab can generally be ignored. The column documentation will appear next to the column name in the Report Columns section of the template, but it does not appear next to the column name in the report output. |
Adding a Display Field
Display fields are the columns which will display in your report output.
-
Ensure the Display Fields tab is the active tab in the Display Field and Filter Tabs section of the Report Template Editor.
-
Select the top of the tree in the Core Source pane. A list of available fields will display in the Source Path pane.
-
The icon beside the field name indicates the data type. If the field you want to use is a Link, you need to expand the tree in the Core Source pane to find it and then select it.
-
Select the field in the Source Path pane you would like to add as a display field.
For display fields, you generally want to pick a Text field over an ID or Org_unit field. For example, to add shelving location as a display field, you will select Name rather than Location ID.
-
In the Transform pane, select the transform you would like to use. The transforms available in the list will vary depending on the data type of the selected field.
The transform will determine how Evergreen processes the data when generating the report. Raw data is the most commonly used transform for display fields except when it’s a timestamp, in which case Date is more commonly used. More information on the available transforms can be found in Field Transforms.
-
Your selected options will display in bold in the three panes. In the Display Fields tab, select Add Fields.
-
Your display field will now display in the grid.
Reordering Display Fields
-
On the Display Fields tab, select the field you would like to re-order.
-
From the Actions menu, choose Move Field Up or Move Field Down. You can also right-click on the field to open the Actions menu.
-
Repeat until your fields are in the order you desire.
Renaming a Display Field
Each display field in a template has a column label which displays as the column header in the report output.
-
On the Display Fields tab, select the field you would like to rename.
-
From the Actions menu, choose Change Column Label. You can also right-click on the field to open the Actions menu.
-
In the pop-up that appears, enter the new name for the field and select OK/Continue.
-
The new field name displays in the Column Label column and will display as the column header on your report output.
|
It is best practice to include qualifiers in the field names for fields like barcode and library so that staff know what data they are looking at in the report output. For example, for multi-branch libraries and libraries participating in reciprocal borrowing, it is not uncommon for the Circulating Library and Owning Library of an item to be different. In this case, it is important to specify in the field name which type of library is being presented in the report output data. |
Changing a Transform
For information on specific transforms, see Field Transforms.
-
On the Display Fields tab, select the field you would like to change the transform for.
-
From the Actions menu, choose Change Transform. You can also right-click on the field to open the Actions menu.
-
In the pop-up that appears, select the new transform from the list and select OK/Continue.
-
The new transform will display in the Field Transform column in the grid.
Removing a Display Field
|
Removing display fields usually does not affect the result set for the report output, but it can, especially when the report output displays a count of records (whether item, patron, circulation, hold, or bibliographic). When removing fields, be mindful of whether or not it will still be clear in the report output what your results mean. |
-
On the Display Fields tab, select the field you would like to rename.
-
From the Actions menu, choose Remove Field. You can also right-click on the field to open the Actions menu.
-
The selected field is removed and no longer shows as a display field.
Filters
Adding a Filter
Filters allow you to set up the criteria so that Evergreen returns the information you are looking for. In most cases, the value for the filter will be entered when the report is run, rather than included in the template.
-
Ensure the Filter tab is the active tab in the Display Field and Filter Tabs section of the Report Template Editor.
-
Select the top of the tree in the Core Source pane. A list of available fields will display in the Source Path pane.
-
The icon beside the field name indicates the data type. If the field you want to use is a Link you need to expand the tree in the Core Source pane to find it and then select it.
-
Select the field in the Source Path pane you would like to add as a filter.
For filters you generally want to pick an ID or Org_unit field over a Text field. For example, to add circulating library as a filter you will select Circulating Library which has the tree icon indicating it’s an Org_unit.
-
In the Transform pane select the transform you would like to use. The transforms available in the list will vary depending on the data type of the selected field.
The transform will determine how Evergreen processes the data when generating the report. Raw data is the most commonly used transform for filters except when it’s a timestamp in which case Date or Month + Year is more commonly used. More information on the available transforms can be found in Field Transforms.
-
Your selected options will display in bold in the three panes. In the Filter tab, select Add Fields.
-
Your filter will now display in the grid.
-
If desired, you can hardcode the value of the filter into the template. See Harcoded Filters for more information.
Changing the Column Documentation
When running a report, the filter can include text which gives information on how to use the particular filter and, in some cases, how to enter the filter value. In the Report Template Editor, this is referred to as column documentation or a field hint.
-
On the Filter tab, select the field you would like to add column documentation to.
-
From the Actions menu, choose Change Column Documentation. You can also right-click on the field to open the Actions menu.
-
In the pop-up that appears, add or update the text and select OK/Continue.
-
When running a report, the column documentation will display under the filter path.
Changing an Operator
For information on specific operators, see Report Operators.
-
On the Filter tab, select the field you would like to change the operator of.
-
From the Actions menu, choose Change Operator. You can also right-click on the field to open the Actions menu.
-
In the pop-up that appears, select the new operator from the list and select OK/Continue.
-
The operator will be updated in the grid.
Changing a Transform
For information on specific transforms, see Field Transforms.
-
On the Filters tab, select the field you would like to change the transform for.
-
From the Actions menu, choose Change Transform. You can also right-click on the field to open the Actions menu.
-
In the pop-up that appears, select the new transform from the list and select OK/Continue.
-
The new transform will display in the Field Transform column in the grid.
Updating a Filter Value
Filter values can be hardcoded into a template or left blank to be filled in when the report is run. Information on commonly hardcoded filters can be found in Reporter Basics.
Hardcoding a filter is recommended when the value of the filter will not change. For example, hardcoded filters are often used when filtering out deleted items.
Filter values can also be hardcoded into templates to make it easier for staff with less reporter experience to run certain reports. For example, you can set up a report with an Item Status filter with a hardcoded value of Missing so that staff can run a report to get a list of all missing items without having to enter filter information.
It is recommended that you balance the reusability of templates that comes when staff enter filter values at the time of running a report versus the ease of use for staff with less experience with the reporter. For example, when no value is entered in the template for a filter on Item Status, staff can pick the relevant status from the list of existing statuses. When a value is hardcoded for Item Status, a new template must be created every time you wish to filter on a different item status.
-
On the Filter tab, select the field you would like to add, update, or remove the filter value for.
-
From the Actions menu, choose Change Filter Value to add or update the value or choose Remove Filter Value to clear the value from the filter. You can also right-click on the field to open the Actions menu.
-
When changing a filter value, a pop-up will appear where you can add the relevant value. See Filter Values for details on exactly how different values must be entered.
-
Select OK/Continue.
-
The filter value will display in the grid.
Removing a Filter
|
Removing a filter WILL affect what results are included in your report output. Ensure you don’t need to filter on a particular field before removing it. It is recommended that reports have at least one filter to choose a specific library. This filter is important as it allows staff to restrict the data in the report output to only data relevant to their library. |
-
On the Filter tab, select the filter field you would like to remove.
-
From the Actions menu, choose Remove Field. You can also right-click on the field to open the Actions menu.
-
The selected field is removed and no longer shows as a filter field.
Troubleshooting Report Templates
If you run into difficulties creating or modifying a template, you may need to contact your system administrator for assistance.
Before contacting your system administrator, please ensure the applicable template folder is shared with your library so your system administrator is able to view the template in question.
When contacting your system administrator, please include the following information:
-
Username of the staff account where the template has been created
-
Folder where the template has been created
-
Name of the template in question
-
Desired end result for the template (what do you want it to report on?)
-
Specific display fields or filters you are having difficulties modifying or adding
-
Links to your current report output from the template if relevant