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
Click on the My Folder template folder where you want
the template to be saved.
Screenshot of staff client report interface
Click on Create a new Template for this
folder.
Screenshot of staff client report interface
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).
Screenshot of staff client report interface
The Enable source nullability checkbox below the sources
list is for advanced reporting and should be left unchecked by default.
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.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
Field Transforms have either an
Aggregate or Non-Aggregate output
type. See for more about
Count, Count Distinct, and other transform
options.
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.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
Double-clicking on the displayed field name is a shortcut to altering the
display header.
Type in the new column header name, for example Circ
count and click OK.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
Click on the + or arrow to expand Circulating
Item. Select Shelving Location.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
Name will appear in the bottom left pane. Select the
Name row and click Alter Display
Header.
Screenshot of staff client report interface
Enter a new, more descriptive column header, for example,
Shelving location. Click OK.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
Select Year + Month in the right hand
Field Transform pane and click Add
Selected Fields
Screenshot of staff client report interface
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.
Select the Check Out Date/Time row. Click
Alter Display Header and change the column header
to Check out month.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
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.
Screenshot of staff client report interface
Applying Filters
Evergreen reports access the entire database, so to limit report output to a single
library or library system you need to apply filters.
After following the steps in the previous section you will see
three fields in the bottom left hand Template Configuration pane.
There are three tabs in this pane: Displayed Fields (covered in the
previous section), Base Filters and Aggregate
Filters. A filter allows you to return only the results that meet the
criteria you set.
Base Filters apply to non-aggregate output types, while
Aggregate Filters are used for aggregate types. In most reports you will be using the
Base Filters tab. For more information on
aggregate and non-aggregate types see
.
There are many available operators when using filters. Some
examples are Equals, In list, is
NULL, Betwee, Greater than or equal
to, and so on. In list is the most flexible
operator, and in this case will allow you flexibility when running a report from this
template. For example, it would be possible to run a report on a list of timestamps (in
this case will be trimmed to year and month only), run a report on a single month, or
run a report comparing two months. It is also possible to set up recurring reports to
run at the end of each month.
In this example we are going to use a Base Filter to filter out
one library’s circulations for a specified time frame. The time frame in the template
will be configured so that you can change it each time you run the report.
Using Base Filters
Select the Base Filters tab in the bottom
Template Configuration pane.
For this circulation statistics example, select
Circulation
Check Out Date/Time
Year + Month
and click on Add Selected Fields. You
are going to filter on the time period.
Screenshot of staff client report interface
Select Check Out Date/Time. Click on
Change Operator and select In
list from the dropdown menu.
Screenshot of staff client report interface
To filter on the location of the circulation select
Circulation
Circulating library
Raw Data
and click on Add Selected Fields.
Screenshot of staff client report interface
Select Circulating Library and click on
Change Operator and select
Equals. Note that this is a template, so the
value for Equals will be filled out when you run the
report
Screenshot of staff client report interface
For multi-branch libraries, you would select Circulating
Library with In list as the operator, so you
could specify the branch(es) when you run the report. This leaves the template
configurable to current requirements. In comparison, sometimes you will want to
hardcode true/false values into a template. For example, deleted bibliographic
records remain in the database, so perhaps you want to hardcode deleted=false,
so that deleted records don’t show up in the results. You might want to use
deleted=true, for a template for a report on deleted items in the last month.
Once you have configured your template, you must name and save it. Name
this template Circulations by month for one library.
You can also add a description. In this example, the title is descriptive
enough, so a description is not necessary. Click
Save.
Screenshot of staff client report interface
Click OK.
Screenshot of staff client report interface
You will get a confirmation dialogue box that the template was
successfully saved. Click OK.
Screenshot of staff client report interface
After saving it is not possible to edit a template. To make changes you will need to
clone it and edit the clone
The bottom right hand pane is also a source specifier. By selecting one of
these rows you will limit the fields that are visible to the sources you have
specified. This may be helpful when reviewing templates with many fields. Use
Ctrl
Click
to select or deselect items.