Printed Reports and Labels Documentation

Reports and Labels

Arctos has a built-in user-customizable reporter. It’s pretty great.

Access

There are three levels of access.

coldfusion_user

All Operators can print reports.

write_report

Special permissions are required to create and edit reports. Development in test is highly encouraged (an infinite loop might be disruptive) but not strictly required. Users should understand the shared nature of reports and be conscientious of creating good and frequent backups.

write_sql

Operators with write_sql can establish connections directly to the database. This is a very dangerous level of access, and the following rules must be strictly followed.

Note that DBAs are happy to help with data retrieval, and (once the data of interest are identified) this is generally a very quick process (minutes).

The following are the most basic considerations for this role.

• Do not ever under any circumstances run SQL against the production database until it has been tested, sanitized, and optimized in test.
• All sql must have a limit statement, and this should be thoroughly tested. (If test is happy prod probably will be too.)
• Arctos is generally resource-limited and SQL must be crafted to be efficient; note that valid != efficient. If a SQL statment takes more than ~10 seconds to complete, it’s probably unacceptably inefficient (or unlimited and about to melt the front end).
• Most report data can be drawn from FLAT; please do so to avoid processing costs when possible, and please consider filing an issue if some ‘normal’ data isn’t available from flat.

Dialect

The database is PostgreSQL.

Variables

SQL Input variables must be enclosed in hash marks, like “#table_name#”. (CFML will magic them back into object names.)

Functions

Many functions exist in Arctos. These can be used to simplify SQL, filter results, or package data in expected and portable formats. These may be viewed in the DDL repository.

Special Note

Loan metadata/header data is relatively normalized and less-than-trivial to query, so a CF Custom Tag is available.

<cf_getLoanFormInfo>

will return a data object under variable getLoan for any report for which loan.transaction_id is available.

Recommendations

• Start with a plan; do not write reactive SQL.
• Limit SQL to data; do not mix layout or formatting with data.
• Modularize; if you’re going to do someThing across many reports, write or request a function (or you will end up with inconsistent data).

Tables

Table structure is available from the Arctos Table Browser. Cache tables FLAT (restricted access, unfiltered data) and FILTERED_FLAT (unrestricted access, filtered data) are often good easy to use sources of data, but do have limitations. Talk to your friendly local DBA if you have any questions.

Language

The reporter uses CFML, HTML, and CSS, generally to produce PDF’s through a browsers print-to-PDF functionality. Extensive documentation for each of these languages is widely available.

Input

The reporter generally accepts the results of queries, or identifiers passed on from containers or transaction. This is readily expandable to most anything; file an Issue.

Static Data

It should be considered good design to build fully dynamic reports which can continue to function when things like addresses and institutional nomenclature change, or work across collections and institutions. Such reports require little maintenance, while reports which include static text require constant upkeep, and are prone to containing incorrect data when changes are not reflected in reports.

Output

Output is generally either PDF-friendly HTML for labels, or CSV for consumption by external applications. However, it is possible to produce most anything that can be pushed through a browser.

Fields

The reporter has the following structure:

• Report Name is the primary report identifier and must be unique. We recommend all lower-case ASCII characters (eg “myreport”), but at least locally any unique string will work.
• Report Type is for categorization and sorting.
• Accepts Variable controls what reports are avaiable when printing, and allows report selection to include only reports which work with the current data. Details are provided below.
• Report Description should contain enough information for anyone to understand why this report exists and how to use it.
• Used By Collections is used to filter and sort. This does not control access; all reports may be used by all collctions.
• Report SQL is used to pull data from the database.
• Report CFM is (sorta) dynamic HTML with processing capability.
• Report CSS is code that controls the page layout. It’s generally better manage separately, but can be included in the cfm section in style tags as well.

Accepts Variable

Accepts Variable controls what reports are avaiable when printing, and allows report selection to include only reports which work with the current data. The variable should be used in the SQL for selecting records (examples below). Supported values:

• table_name: temporary/cache table name of last catalog record search. This will always contain collection_object_id, which may be used to join to catalog records or flat. This is almost always the best choice for catalog record based labels.
• collection_object_id: a list of cataloged_item (or flat and filtered flat) collection_object_ids.
• transaction_id: a list of transaction (or loan, borrow, or accn) transaction_ids.
• container_id: a list of container IDs. May be be used to print container labels, or by joining to parts, catalog records.

About Lists

Note that 1 is always an acceptable list length, and is most common for things like loan forms. However, the reporter will accept comma-separated lists of identifiers of virtually any length. Printing labels for a thousand hand-selected containers is easily accomplished.

Editors

Reports may be edited in the browser fields, but it is often better to use a more specialized editor, and copy to the browser. We use sublime with postgresql and cfml packages; many, many other choices are available.

Backups

All reports and individual reports may be downloaded as CSV, and new reports may be created from this. Save early and often; assume someone’s going to delete all of your reports the very second you look away. https://github.com/ArctosDB/arctos-assets is available for more-persistent backups; consider stashing periodic copies of ‘mature’ reports there.

Clean up

Take backups, then delete any reports for which you are responsible and do not use. Huge piles of abandoned reports cause problems for everyone, and drastic action may result. (OK, not that drastic, there will be backups.)

Printing

Use the browser’s built-in print to PDF functionality. Click settings and turn off headers.

Debugging

Variable debug default false is available to the reporter. Use open+debug to set it to true. See examples below for usage.

Libraries

• In the CFM section of the report, <cfset inclPagedJs=true> (or inclPagedJs=true in cfscript) will make the Paged.js library avaialble to reports.

Examples and usage

Creating

It is almost always best to work with the DBA team or copy an existing report to get started building a report, but all of the necessary information to start from scratch is in this doument and the Arctos Table Browser.

Using

From transactions, containers, or catalog item search results, choose ‘print any report,’ select the report you wish to print (you can sort by clicking headers or search with your browser’s built-in functionality), then click ‘open’ to execute the report. This will usually result in a HTML page which can be printed to PDF, or a CSV file which should automatically download to your selected download folder.

Code Snippets

CFML

The “heavy lifting” is performed by CFML, which can be written as tags or script. Lucee is used to process, but Adobe Coldfusion is almost always functionally identical and often has better documentation.

HTML

HTML can be mixed freely with CFML.

<p>#myvar#</p>

will simply print the value of myvar in a paragraph, for example.

CSS

We recommend CSS for layout. In general, keep it simple: Various browsers have slightly different handling of some CSS, and this tends to be much more noticeable in very new or obscure/little-used tags. (Older browsers may also have issues; developers use the current production release of Firefox, and you should too.)

Headers and Footers

Paged.js provides header and footer functionality, but is extremely twitchy. Fixed-size layouts and precalculated headers and footers tend to work much better than the alternatives. (And please let us know if you have a better solution, or need more JS/CSS libraries.)

SQL

SQL is more-controlled that other report content; only users with write_sql role may edit SQL.

The report_sql query returns a CFML query object (“table”) named d.

Debug

<cfif debug>
    <cfdump var="#d#">
</cfif>

will dump the object d returned by the code in report_sql when the ‘open+debug’ option is selected (or debug is manually set to true).

Comment

<!---
    this is a CFML comment
--->

Comments are ignored by the processor; future-you (and future everyone else!) will thank you for including lots of comments.

Choosing Format

There are a great number of reporing tools available, choosing one suitble for your needs is critical to a smooth process. A very few possibilities are listed here, please contribute if something not listed here works for you.

HTML

The Arctos Reporter will generate HTML, which is generally then fed to the browser’s Print to PDF functionality. This is generally the easiest browser-centric approach and provides access to the widest range of tools, but various browsers may handle the PDF transformation slightly differently, and the PDF transformation may be a bit mysterious and probably will not be documented. This is usually the proper approach for things like loan forms or large labels, but pixel-perfect layout may be difficult, particularly if multiple users, computers, operating systems, browsers, etc. are involved.

Server-Side PDF generation

The Arctos Reporter will directly produce PDF, which may then be printed. The layout of this method is generally more predictable than generating PDF via the browser, but the server-side tools are limited to strict version of various languages, most notably CSS2.1. This is often a good choice for fixed-layout reports.

Bartender

The label generating software which accompanies many thermal transfer printers seems to work well for feeding those printers. (This is all second-hand, someone please help!)

Crystal Reports

Various high-end reporting tools are commercially available. These generally accept CSV, which is readily generated by the Arctos Reporter. The costs of acquisition, infrastructure, and development are often high, but these powerful tools will produce reports of any type.

Word / Mail Merge

The Arctos Reporter will readily produce CSV which may be used to produce labels in various document and spreadsheet tools. (Someone please elaborate!)

Edit this Documentation

If you see something that needs to be edited in this document, you can create an issue using the link under the search widget at the top left side of this page, or you can edit directly here.