[OPEN-ILS-DEV] Report Templates

[Jason Etheridge]

On Fri, Nov 13, 2009 at 3:12 PM, Anne Andres <Anne.Andres at columbiabc.edu> wrote:
> Sorry to write the discussion group on this.....but are any of you willing to share a few major report templates?

Anne, I'm not a reporting guru, but I can give it a shot.  I'm going
by the development branch, so there may be some differences with your
particular version of EG.

Common steps to start folks off:

Staff Client -> Local Administration -> Reports
Templates -> Test (or some other folder you created.  it's useful to
create the same folder in Reports and Output)-> Create a new Template
for this folder

> The major template I need is:
> 1) Item count by Circ Modifier and Shelving Location.

Database Source Browser -> Sources -> Item
Select Item again (this time in the list with the white background,
not the menu, and not the expand-tree icon)
In pane 2, select Copy ID (to get a count, you have to count something
unique, such as the internal ID for an entity)
In pane 3, select Count Distinct
Add Selected Fields (Copy ID should show up in pane 4 in the lower
left, in the Displayed Fields tab)

Reporting Tip: The default state of a reporting query is to produce
matching rows from a table or combination of tables.  The Count and
Count Distinct transforms will summarize that data by counting
occurrences of unique values in the fields you specify.  If you
display just Copy ID (Raw Data) and you have 1000 items, then your
report will have 1000 rows of data.  But if you display Copy ID
(Count), then you'll just get one row of data, showing a count of
1000.  Other fields specified as Display Fields will break-down or
subdivide that total.  So if you display Copy ID (Count) and Is
Reference (Raw Data), then you'll get up to two rows of data (because
Is Reference may be either True or False), each with a count, where
the two counts will add up to 1000.   Count Distinct is safer than
Count unless you know what you're doing, because otherwise certain
types of queries involving multiple tables may count the same rows
multiple times.  Count Distinct helps prevent that.

Reporting Tip: Too many display fields in a "Count" query may produce
unwieldy results.  What you end up counting is unique combinations of
values across all the display fields.  So for example, a count of Copy
ID and Is Reference may produce two rows, while Copy ID, Is Reference,
and OPAC Visible may produce 4 rows, and Copy ID, Is Reference, OPAC
Visible, and Holdable may produce 8 rows, and so on (multiple the
number of possible values for each display field).

In pane 1, expand the Items entry by clicking the expand-tree icon
(should look like a plus sign in a box)
Select Circulation Modifier
In pane 2, select Name
In pane 3, select Raw Data
Add Selected Fields (Name should show up in pane 4 below Copy ID)
In pane 4, select Name
Alter Display Header
Enter "Circ Modifier" as the column header

Reporting Tip: Generally, whenever you're interested in displaying
something  you see in Pane 2 and the datatype is "link", you should go
back to pane 1 and find the field there in the source browser and use
that instead.  In this case, Circulation Modifier from pane 2 would
have worked, but only because it uses the display value as a "natural
key" to link to a separate circulation modifier table.

In pane 1, select select Shelving Location
In pane 2, select Name
In pane 3, select Raw Data
Add Selected Fields (Name should show up in pane 4 below Copy ID)
In pane 4, select Name
Alter Display Header
Enter "Shelving Location" as the column header

Reporting Tip: By renaming the column headers, you can make your
output less ambiguous, since by default the column headers match the
field names, which may be duplicated across different sources and
tables.

In pane 1, re-select Item at the top of the list (this should reset
the fields listed in pane 2 to the those directly on the Item table)
In pane 2, select Is Deleted
In pane 3, select Raw Data
In pane 4, select the Base Filters tab
In pane 3, Add Selected Fields (Is Deleted should show up in pane 4)
In pane 4, select Is Deleted
Change value
Select Cancel from the pop-up dialog

Reporting Tip: Fields you enter into the Base Filters tab may be used
to reduce the rows you're listing or counting.  You get to set
specific values when you later schedule a report based on templates,
but may also pre-select certain values for filters within the
templates themselves.

Reporting Tip: Evergreen does not truly delete data by default, and
often simply flags the data as deleted.  So be aware of such fields as
Is Deleted and Active, and filter on them accordingly.

In pane 4, enter a Name and a Description for the template
Save

> 2) and Counts by title, and counts by volumes or units for each Circ. Modifier

If someone else doesn't volunteer for this one, I'll come back to it
later.  Your general strategy here is probably to count Items again.

Hope this helps!

-- 
Jason Etheridge
 | VP, Tactical Development
 | Equinox Software, Inc. / The Evergreen Experts
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  jason at esilibrary.com
 | web:  http://www.esilibrary.com

Please join us for the Evergreen 2010 International Conference, April 20-23,
2010 at the Amway Grand Hotel and Convention Center, Grand Rapids, Michigan.
http://www.evergreen2010.org/