Printed Reports and Labels Documentation
Reports and Labels
Arctos has a built-in user-customizable reporter. It’s pretty great.
Access
[!NOTE]
This process is ongoing, rules are temporarily relaxed while development and optimization proceeds.
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.
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.
#myvar#
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
> [!NOTE]
> This process is ongoing, rules are temporarily relaxed while development and optimization proceeds.
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