Designing Reports

I don't want funky labels anymore! I want nice ones, and I want to print them from Arctos.

This procedure explains how to design your own label, load it to Arctos, create handler(s) for the label, and use it to print almost anything that uses specimen data. You'll need to have mange_collections permissions to complete this procedure.

First, download and install the ColdFusion 8 Report Builder. You'll need a Windows box.

At setup, it will ask you a bunch of information about your ColdFusion environment. Just cancel out, or do whatever it takes to get it to stop bugging you; none of this will be useful. Once that's happy, fire up the Builder and create a new label.

Hint: Set the headers and footers to zero size for creating things like specimen labels. You can size any object precisely with the Properties menu to the right, or just drag things around until they look about right.

Adjust the size of individual labels, column count, etc. as necessary (see Report Properties by right-clicking on any unused part of the report). You should now have a blank label to work with. To add data fields, use the Field wizard (lightning-bolt icon). It will be necessary to know the name of fields that will be returned from the database (more on this later). Type the field into the Expression Builder and click OK. The following example is for a field named "some_database_query_variable."

Now, load your template to Arctos. From the Reports tab, choose Reporter. Click Browse, locate the CFR file on your drive, and upload it. When the page refreshes, you should see your report in the grid. Click the Create Handler link to get started.

Hint: Look at existing report handlers for SQL hints. You'll probably find something appropriate. Do not alter any handlers that are not yours!

A handler will be generated, and you'll be redirected to the Edit Handler page. Change the Report Name to something more intuitive. Make sure the Report Template is what you just uploaded. Select a Report Format. We'll come back to Pre-Function later.

Now, create the SQL you need to generate the data for your report. You may use built-in Oracle functions, some custom functions (ask a developer or see the branches/DDL/functions folder in the Arctos SVN Repository), and most ANSI SQL. Click Test SQL to see the results of your query. Click Save Handler when you're happy with your results.

Hint: See the gray box at the bottom of the Edit Handler page. Your SQL must use one of the pre-defined variables. Talk to a developer if you're having difficulties getting what you want. Oracle SQL may not be quite what you're used to, and we've probably done something like what you need before.

Sometimes it's not practical to use SQL to manipulate data. Concatenating locality elements into a string for label printing can be very tricky, for example. The Reporter has the ability to pass your query off to a ColdFusion Function, manipulate it using custom code (CFML or CFSCRIPT), and return the altered query object to your Report Template. Just contact a developer if you think you need this functionality. If you already have a function, you may simply type it's name into the Pre-Function box from the Edit Handler page.

A quick note about fonts. The designer runs only in Windows, while the template is executed from Linux. Only the fonts available to the Linux server may be printed. To print barcodes, you'll need to download the Free 3 of 9 fonts, install it on the machine you're developing the report from, and choose it as the font of the barcode. Both Free 3 of 9 and Extended 3 of 9 are available on Arctos. Most other common fonts are installed; talk to the development team before using anything uncommon. You will need to select "Embedded Font" from the field properties to print barcodes in PDF documents. FlashPaper documents always embed fonts.

You should now have a Report Template and an appropriate handler. Find some specimens and choose Print Any Report from the Management dropdown. Choose the handler that you just created from the list, click the button, and your report will print.



The reporter will accept three variables:

  1. collection_object_id (=cataloged_item.collection_object_id)
  2. transaction_id (=trans.transaction_id)
  3. container_id
Both are integers or lists of integers.

These must be passed in via URL or form, and may be accessed by the Pre-Function or the SQL_Text. You must wrap variables with ## to access them from the SQL.
...and cataloged_item.collection_object_id IN (#collection_object_id#)...
Loan data are best accessed by simply leaving the SQL_Text block blank and calling function get_loan. A query containing all normal loan data for {transaction_id} will be returned.


You may call reports by accessing the URL
/Reports/report_printer.cfm?[collection_object_id={list] | transaction_id={list}] [report={name}]
[sort={sort column list}]

Omitting the report parameter will allow you to select a report for printing. Including it will print a report without prompting. Report is a case-insensitive character match (no substrings).

Including Sort={list} will append an "order by {list}" clause to your SQL if doing so won't cause errors. If it will (i.e., you try to sort by a column that's not in the query), the sort parameter will simply be ignored.