Transcript Voyager Access Reports

Voyager Access Reports
Tips, Tricks, and a Secret or Two
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
1
Voyager, Access, and SQL (oh my!)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
2
Topics








Getting started: setup and configuration
Navigation: the lay of the land
Running "canned" reports
Making changes: data and format
Finding what you want
Demo: creating a PO query
SQL Tips
Recipes, resources and requests
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
3
Getting Started

Setup: follow the directions



Voyager (6.5) Reporter User's Guide, "Setting Up
the Prepackaged Access Reports", starting on
page 1-17
Good documentation
Call Ex Libris support if problems
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
4
Setup: Tablespace

The "Build Database Links" examples show
Tablespace in lowercase (yourdb); use
uppercase (YOURDB)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
5
Setup: ALL_TAB_COLUMNS

Don't worry if you see this error - we'll fix it
later
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
6
Setup: Make a Copy

Make a backup copy of your configured
Reports.mdb


Preserve your work when upgrading Voyager
Accidents do happen!
Move this to "My Documents" or to a network
drive, and be sure it gets backed up!
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
7
Setup: Compact & Repair



Access databases can grow with every use, so
maintenance is important
If you do "Make table" queries, delete those tables when
no longer needed
Tools  Database Utilities  Compact and Repair
Database
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
8
Navigation: The Lay of the Land

Essential




Tables
Queries
Reports
Favorites

Optional




Forms
Pages
Macros
Modules
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
9
Navigation: Tables

Mostly links to Oracle tables, but some local
data too
Oracle (linked)
table
Access (local)
table
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
10
Navigation: Queries

This is where almost all work is done:
designing and running queries to get data
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
11
Navigation: Reports

Reports are the "fancy dress" versions of
queries - an extra step, not always needed
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
12
Navigation: Favorites

Favorites are shortcuts to tables, queries and
reports
Different names - but still just
shortcuts to the same query
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
13
Navigation: The Rest




Forms: for (re)linking tables
Pages: create web pages from data (but not
as simple as that sounds)
Macros: automate some tasks without
complex coding
Modules: VBA functions and programs which
support reports (like the UTF8to16 and Blob
functions)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
14
Queries vs. Reports



Do you want the raw data (a query), or
something nicely formatted (a report)?
Each report must have an underlying query,
but queries don't have to have reports
Each query can be used by many reports:
same data, different displays
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
15
Why Run a Query?




Copy & paste into Excel
Quickly sort and filter data to focus on a
subset
Use pivot tables to explore summary data
(counts, totals, averages and more)
Use "make-table" query to save results
locally for re-use
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
16
Query Results: Query Options
Right-click title
bar for options
Current
Total
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
17
Query Results: Column Options
Right-click
column header
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
Start & end date
columns are hidden
18
Why Run a Report?


Nicely formatted data, especially good for
printing - or impressing the boss
Reports can be shared electronically as
"snapshots", so even users without Access
can view them


http://office.microsoft.com/en-us/access/HP052612151033.aspx
Or install something like the free PDFCreator,
which lets you "print" to PDF

http://www.pdfforge.org/products/pdfcreator
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
19
Report Results
To make a snapshot, use the
Export function
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
20
Making Changes: Queries




Copy an existing query, then edit the copy
Make one change at a time, then test
Sanity-check your results - do the numbers
and the data look right?
Understand how parameters work, especially
dates
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
21
Making Changes: Parameters


Parameters add interactivity and allow
queries to be used more flexibly
Put prompting text in [square brackets]

ColumnName: [prompt for user info]
Asks user for
"start date"
Redundant, unless you
want to display it
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
22
Tip: Dates in SQL (BETWEEN)

Dates are really dates + times



2008-02-06 13:22:11.789
2/6/2008 = 2008-02-06 00:00:00.000
BETWEEN #2/6/2008# AND #2/7/2008#
means



Start: 2008-02-06 00:00
End: 2008-02-07 00:00
This is 24 hours, not 48 hours
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
23
Tip: Dates in SQL (>= AND <)


You can also use >= (greater than or equal)
and < (less than) - same result, but less
ambiguous than BETWEEN
>= #2/6/2008# AND < #2/7/2008# means



Start: 2008-02-06 00:00:00
End: 2008-02-06 23:59:59
This is 24 hours (minus one second)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
24
Tip: Dates in SQL (DateAdd)



If you don't want to think about adding a day
every time you run a query, you can let
Access do it for you automatically
The Access DateAdd function can add (or
subtract) any number of days/months/years
to any date
Either way, be consistent so you don't have
to remember to add a day to this report, but
not to that report....
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
25
Making Changes: Expression Builder

Right-click on criteria to access the
Expression Builder
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
26
Making Changes: Expression Builder

Highlight element to change, navigate to
desired function, and click Paste
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
27
Making Changes: Expression Builder

Fill in the parameters (click Help to learn
about the function) and click OK.
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
28
Making Changes: Reports




Copy an existing report, then edit the copy
Make one change at a time, then test
Understand your query data before putting it
into a report
Experiment! You can't break anything
(unless you start throwing things)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
29
Report: Design View
Label
Groups
Textbox with formula
Textbox with data
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
30
Finding What You Want



Data dictionary and class diagrams
Ask the database
Hack the data
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
31
Data Dictionary and Class Diagrams

On SupportWeb under Documentation



https://support.endinfosys.com/cust/voy/doc/er_diagrams/er-diagrams.html
https://support.endinfosys.com/cust/voy/downloads/pdf/2006.1/datadic.pdf
Highly recommended: CARLI’s V6 Data
Dictionary and Class Diagrams (via
KnowledgeBase)

Builds on the Voyager documentation - for
example, documents MARC data in bib_text table

http://support.endinfosys.com/cust/voy/resource/files/[email protected]/CARLI_F
eb_2007.zip
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
32
Data Dictionary: Sample

OPERATOR









OPERATOR_ID: VARCHAR2(10)
FIRST_NAME: VARCHAR2(25)
MIDDLE_INITIAL: VARCHAR2(1)
LAST_NAME: VARCHAR2(25)
PASSWORD: VARCHAR2(12)
CREATE_DATE: DATE
CREATE_OPID: VARCHAR2(10)
MODIFY_DATE: DATE
MODIFY_OPID: VARCHAR2(10)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
33
Class Diagram: Sample
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
34
Ask the Database


The Oracle database has its own data
dictionary, with full details about everything
within the database
Use the Access user_tab_columns query though we need to fix it first
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
35
Ask the Database: user_tab_columns

Change the criteria for OWNER to the name
of your database
Same as the
tablespace name
used for linking
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
36
Ask the Database: user_tab_columns

Information about all columns in all tables
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
37
Ask the Database: user_tab_columns

Example: find all the tables which (probably)
use BIB_ID
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
38
Hack the Data




"Hack" in its original (good) sense: in-depth
exploration
Create Voyager test records with every
possible field filled out
Use fake or easily identifiable data
View your test record in Access to
understand how fields in Voyager clients
correspond to columns in the database
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
39
Hack the Data: Sample PO in Voyager
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
40
Hack the Data: Sample PO in Access

Limit your query to just your test record and
retrieve all columns, then explore the data
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
41
Demo: PO Query


We want to know how many orders we
placed last year.
Desirable:



counts by vendor
counts by type (firm, continuation, etc.)
counts by month the PO was placed
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
42
Demo: PO Query - Guidelines





Start simply
Build on what you know works
Test and review the data
Use parameters for flexibility
Experiment - you can't hurt the data
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
43
Demo: PO Query - Purchase Order
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
44
Demo: PO Query - Add Vendor

Added VENDOR table, so we can get
VENDOR_NAME
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
45
Demo: PO Query - Joining Tables

Adding the VENDOR table automatically
created a JOIN to PURCHASE_ORDER on
VENDOR_ID
Double-click for details
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
46
Demo: PO Query - Add PO Type

Added PO_TYPE "lookup" table, so we can
get textual PO_TYPE_DESC
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
47
Demo: PO Query - Add Criteria

Start simply: one month instead of a whole year
(remember to change later, to parameters)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
48
Demo: PO Query - Review Data

We have the basic data we need; now we
can start refining our query to get exactly
what we want
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
49
Demo: PO Query - Select Columns

Remove unwanted columns
Removed
VENDOR_ID
and PO_TYPE
Kept PO_ID but
won't show in results
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
50
Demo: PO Query - Format Data

Add a new column for ORDER_MONTH;
update criteria for full year (or parameters)
Criteria are the
equivalent of
SQL's WHERE;
Fields are
SQL's SELECT
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
51
Demo: PO Query - Base is Done

We now have the base data needed: all
orders created in 2007. Use this for counts
by vendor etc.
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
52
Demo: PO Query - Counts by Vendor

Create a new query which uses the base
query just created
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
53
Demo: PO Query - Counts by Vendor

Turn on "Totals" for the query
Right-click on column, or
from View menu choose
Totals
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
54
Demo: PO Query - Counts by Vendor


Set "Group By" for desired columns
Add Total column with Count(*) as an
Expression, or use a specific column
Two options, same
results
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
55
Demo: PO Query - Counts by Vendor

All done: counts of orders placed by vendor,
for each month the vendor was used
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
56
Tip: Grouping & Counting

SQL counts only what exists: rows, or nonNULL columns



COUNT(*): count all rows
COUNT(col): count all rows where col is not NULL
Understand your data, think about what
you're counting, and check the results
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
57
Tip: Grouping & Counting
If I just count
ENCODING_LEVEL,
NULL values aren't
included
If I count rows (*),
NULL values are
included
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
58
Demo: PO Query - Behind the Scenes

What does all that pointing & clicking do? It
creates SQL:
SELECT
vendor_name
, order_month
, Count(*) AS Total
FROM [eSWUG PO demo]
GROUP BY vendor_name, order_month
ORDER BY vendor_name, order_month
;
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
59
SQL Tips: Anatomy of a Query
4) Format &
display it
1) Find it
2) Filter it
3) Group it
SELECT
vendor_name
, order_month
, Count(*) AS Total
FROM [eSWUG PO demo]
WHERE vendor_name like 'A%'
GROUP BY vendor_name, order_month
ORDER BY vendor_name, order_month
5) Sort it
;
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
60
SQL Tips: Access vs. Oracle

Access "SQL" is a mixture of Access / VBA /
SQL, and usually is not directly compatible
with Oracle via sqlplus or other tools



Access / VBA functions like Format
Access dates with ##
Linking process treats some numbers like strings
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
61
Recipes







Holdings with no items
Circulation Transactions
Blobs
Pivot tables
Make-table queries
Joining tables
Tables to avoid
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
62
Recipes: Holdings with no Items


LEFT JOIN: everything in the left table, whether
there's a match or not
Then filter out the non-matches with IS NULL
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
63
Recipes: Circulation Transactions

Circ transactions (charges & discharges) are in
two places




CIRC_TRANSACTIONS (current charges, not yet
discharged)
CIRC_TRANS_ARCHIVE (older charges, which have
been discharged)
For a complete picture, you need to look in both
tables, generally via a UNION query
This applies to renewals too
(RENEW_TRANSACTIONS and
RENEW_TRANS_ARCHIVE)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
64
Recipes: Circulation Transactions


Union queries can't be displayed via the
Access Query Design view - only via SQL
The same columns must be selected from
each table, in the same order
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
65
Recipes: Circulation Transactions

Create a UNION query for renewals

Compare the tables

Make a new query, set as Union, and type away
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
66
Recipes: Blobs

Blob:


Field:



GetField(GetBibBlob([bib_id]),"245",1)
GetFieldAll(GetBibBlob([bib_id]), "650")
FieldRaw:



GetBibBlob([bib_id])
GetFieldRaw(GetBibBlob([bib_id]),"245",1)
GetFieldRawAll(GetBibBlob([bib_id]), "650")
Subfield:

GetSubfield(GetFieldRaw(GetBibBlob([bib_id]),"245",1),"b",1)
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
67
Recipes: Blobs


GetFieldRaw has tag, indicators, and
subfield codes; GetField has just text (the
contents of the subfields)
GetSubfield can only return a single
subfield; for multiple or repeated subfields,
use GetFieldAll

GetFieldAll(GetBibBlob(bib_id), "650", "z")
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
68
Recipes: Pivot Tables


Run query, then right-click and choose Pivot
Table View
Drag fields to row, column, and detail areas
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
69
Recipes: Pivot Tables

Click on the detail column heading (BIB_ID
in this example)


PivotTable  AutoCalc  Count
PivotTable  Hide Details
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
70
Recipes: Pivot Tables

Result: a way to explore your data
interactively
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
71
Recipes: Make-Table Queries

If a query takes a long time to run, or you
need to refer to its data repeatedly, use a
Make-Table query
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
72
Recipes: Make-Table Queries

The query's data will be stored in a new table in
your Access database. This table will be
refreshed every time you run the query.
Clean up
when you're
done!
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
73
Recipes: Joining Tables


Start with the table for the main thing you
want to know about
Add only the tables needed to support your
query




Limits (Criteria), like LOCATION
Bridging tables, like BIB_MFHD (to join BIB_TEXT
and MFHD_MASTER)
Make as few joins as possible
Be aware of one-to-many relationships
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
74
Recipes: Tables to Avoid

Some bridging tables are often inaccurate


Some "view" tables are overkill or can be
misleading


BIB_ITEM, BIB_LOCATION
BIBHISTORY_VW, SERIALS_VW
The *BLOB_VW tables should never be used

AUTHBLOB_VW, BIBBLOB_VW, MFHDBLOB_VW

Per Alan Manifold, "BIBBLOB_VW is DEATH!"
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
75
Resources - Voyager Reports

Many fine presentations from EndUser 20052007 on SupportWeb


Updated BLOB functions



http://support.endinfosys.com/cust/voy/resource/files/manifold@p
urdue.edu/BLOBfuncs%2020050110.txt
Voyager-L email list
Voyager Wiki


https://support.endinfosys.com/cust/community/vgroup/index.html
http://www.voyagerwiki.com/
Share knowledge with the community!
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
76
Resources - Access


Built-in help
Access functions


http://www.techonthenet.com/access/functions/index.php
Tutorials

http://www.functionx.com/access/index.htm
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
77
Resources - SQL

Oracle 9i SQL Reference


W3Schools SQL Tutorial


http://downloadwest.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm
http://www.w3schools.com/sql/default.asp
SQL Tools (a good, free Oracle SQL
client)

http://www.sqltools.net/
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
78
Resources - Me



Andy Kohler
[email protected]
(310) 206-8312
eSWUG: Feb 6, 2008 [Andy Kohler - UCLA]
79