How to add and run reports

Task Description

This document explains how to make set up report authoring, and create a report.

Prerequisites

To author reports, you need to have:

• Downloaded and installed the iReport Designer application
• Obtained a basic familiarity with using this application.
• Set up or arranged for read-only access to the CollectionSpace databases.

1. Download and install the iReport Designer application. This is free, open source software (GPL) from Jaspersoft, which you can download from:
   
   http://sourceforge.net/projects/ireport/files/iReport/
   
   Ensure that you get at the minimum the 5.0.1 version, which is known to work with current CollectionSpace services.
2. Read the tutorials on the same page, and any others you choose to. These are a good introduction, even for our use of iReport.
3. Ensure you have at least read-only access to the database. If you are working on the server that hosts CollectionSpace, the default configuration will allow access. Otherwise, you need to have your admin give you access to the database.
   1. If you are using PostgreSQL, then have your administrator edit the PostgreSQL configuration files to grant remote access.

      1. In the main postgresql.conf file, there must be an entry like:

         listen_addresses = 'localhost, {your-machine}'        # what IP address(es) to listen on;
         

         where you or your administrator will replace "{your-machine}" with the IP address or hostname of the computer from which you will be connecting when you're authoring reports. See also the PostgreSQL documentation on Connections and Authentication.

      2. In PostgreSQL's host-based authentication file, pg_hba.conf, there must be an entry for a PostgreSQL database user account that will be given read-only privileges to the nuxeo database, for the purpose of performing queries and running reports. The following example specifies that access is granted to the nuxeo database to the PostgreSQL database user account named "reader," for remote connections from IP address 172.20.143.89, via username and password authentication (md5).

         host nuxeo reader 172.20.143.89/32 md5
         

         (In the example above, replace the example IP address given there with the actual IP address of the machine you will be connecting from. In place of specifying an individual IP address, you can instead specify a subnet range of addresses. For details, see the PostgreSQL documentation on the pg_hba.conf file..)

   2. If you are using MySQL, then have your administrator either execute the following command in the mysql client console, or by add the equivalent access in your admin console (replacing the placeholders in curly braces, below, with the appropriate values for your institution - be sure to remove the curly braces, as well):

      GRANT SELECT ON nuxeo.* TO 'reader'@'%.{YOURDOMAIN.ORG}' IDENTIFIED BY '{READER_PASSWORD}';
      

Procedure

Steps to create a new report in iReport Designer:

1. In iReport Designer, create a new datasource to access the data in your repository. The easiest way is to select Window->Welcome to iReport, and then use the Quick Start wizard to create a new database connection.
   1. Click the Step 1 - Create a new database connection link.
   2. Select Database JDBC connection
   3. Type in a name you choose to refer to this connection (E.g., "Our Museum on CollectionSpace")
   4. In the JDBC URL Wizard section, type in the name (or IP address) of the server that runs your database, and enter "nuxeo" in the Database field, and then click on the Wizard button to generate the JDBC URL.
   5. Enter "reader" for the Username, and your local password.
   6. Click on the Test button to ensure your entries are correct.
2. Create a new report. The easiest way is to use the Quick Start wizard on the Welcome window:
   1. Click the Step 2 - Create a new report link.
   2. Select a template you like, and click the Launch Report Wizard button.
   3. Set a name and location, and click Next.
   4. Choose the datasource your defined in Step 4 above.
   5. Click Design Query, select a table (e.g., collectionobjects_common), choose fields, and complete the wizard according to the tutorials.

You can explore the report authoring tool from this basic report. You will likely want to rename the labels, as the default labels based upon the data-model are not very user-friendly. In addition, certain fields will benefit from some expressions to simplify the value.  For example, field values that refer to authority terms (Person, Org, etc.) can be easily converted to a display value using the following expression, replacing "field" with the field name you are accessing:

($F{field}==null || $F{field}.isEmpty())?
  "":$F{field}.substring(
     $F{field}.lastIndexOf( ")" )+2,
       $F{field}.length()-1).replaceAll("[+]"," ")

In addition, you will want to add two additional clauses to the query, to filter your results to the appropriate tenant, and to filter documents that have been deleted (soft-deleted, but saved in the repository). Here is an example query that gets values in from the acquisition schema:

SELECT
acquisitions_common."originalobjectpurchasepricevalue" AS acquisitions_common_originalobjectpurchasepricevalue,
acquisitions_common."transferoftitlenumber" AS acquisitions_common_transferoftitlenumber,
acquisitions_common."acquisitionreferencenumber" AS acquisitions_common_acquisitionreferencenumber,
acquisitions_common."acquisitionmethod" AS acquisitions_common_acquisitionmethod,
acquisitions_common."acquisitionauthorizerdate" AS acquisitions_common_acquisitionauthorizerdate,
acquisitions_common."acquisitionauthorizer" AS acquisitions_common_acquisitionauthorizer,
acquisitions_common."acquisitionreason" AS acquisitions_common_acquisitionreason
FROM
"public"."acquisitions_common" acquisitions_common
INNER JOIN "public"."hierarchy" hierarchy ON acquisitions_common."id" = hierarchy."id"
INNER JOIN "public"."collectionspace_core" core ON acquisitions_common."id" = core."id"
INNER JOIN "public"."misc" misc ON misc."id" = hierarchy."id"
WHERE core.tenantid = $P{tenantid} AND misc.lifecyclestate != 'deleted'
$P!{where_clause}

In the query above, the tenant ID has been specified as a property in the iReport tool, and then the property value is used in the query. CollectionSpace will pass in the current tenant ID when the report is run from the UI, so we can actually re-use the same report for different tenants in the system. For your testing, set the tenantid property to have a default value of the tenantid you most commonly use. This tenantid is the one specified in the tenant configuration file for the services, for your tenant. See also: Creating your new tenant.

You will also note an additional "where_clause" parameter. This is used to pass in specific id values, when the report is to be run on a particular record. In the sample report provided in the

release, this parameter has the following default value:

(($P{csid}==null || ($P{csid}.length()==0))?"":" AND hierarchy.name = '"+$P{csid}+"'")

This effect of this definition is to look for a passed csid value from CollectionSpace, and produce a report on that single acquisition record if it is set. If the value is not set by CollectionSpace (when the report is run without any context), it runs a report on all acquisition records. Authoring this report this way allows it to be run either for a selected acquisition record, OR without a context (from the report admin UI, available in an upcoming release).

There is currently an example report (with more to come) as part of the CollectionSpace distribution. You can see the

version of this report (and download/copy it to review in iReport) here in the CollectionSpace source code repository. You can also find the compiled version where you have installed CollectionSpace. It (and all reports you author and want to use in CollectionSpace) are located in the directory: cspace/reports within the Tomcat installation directory. If you open the compiled version in iReport, it will convert it to a jrxml file for you, and let you review and/or edit the report.

Preparing reports for running from within CollectionSpace

In order to make the report available to CollectionSpace, you must follow 2 steps:

1. Copy the compiled .jasper file to the reports folder:  cspace/reports within the Tomcat server folder (whose path should be in your CSPACE_JEESERVER_HOME environment variable). Note the name of the file, e.g., "acq_basic.jasper".

   Beginning with CollectionSpace version 3.2.1, you can instead copy your original .jrxml file (rather than your compiled .jasper file) to the reports folder. Doing so will make it easier to keep your compiled reports to date when JasperReports is updated.

   See http://wiki.collectionspace.org/display/collectionspace/Release+3.2.1#Release3.2.1-AutomaticupdatingofreportsusingnewJasperReportsversions%28CSPACE4876 for details.

2. Create a report record within CollectionSpace. This requires you to run a simple curl command in

   Unknown macro: {multi-excerpt-include}

   (we will provide an admin UI as soon as possible in an upcoming release). Here is an example XML payload to use with curl. It declares that a report based upon the file "acq_basic.jasper" should be created and should be shown to users when they are editing Acquisition records. In the

   Unknown macro: {multi-excerpt-include}

   release, the supportsDocList and supportsGroup elements should always have the values "false" (future versions of CollectionSpace will let users run reports on lists of records, and on groups of records). You can set the default outputMIME type as you wish, so long as it is one of the supported jasper output types, and so long as your browser knows how to handle these files (or pass them to a program that does).

   <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
   <document name="report">
    <ns2:reports_common
    xmlns:ns2="http://collectionspace.org/services/report">
    <name>Sample Acquisition Report</name>
    <notes>This is a sample Acquisition report.</notes>
    <forDocTypes>
      <forDocType>Acquisition</forDocType>
    </forDocTypes>
    <supportsSingleDoc>true</supportsSingleDoc>
    <supportsDocList>false</supportsDocList>
    <supportsGroup>false</supportsGroup>
    <supportsNoContext>true</supportsNoContext>
    <filename>acq_basic.jasper</filename>
    <outputMIME>application/pdf</outputMIME>
    </ns2:reports_common>
   </document>
   
   

   Then your admin can use the following command to create the report record in CollectionSpace. Replace "{yourtenant}" with the name of your tenant, and replace "{password}" with your admin password. This assumes you have saved the XML file above as "report1.xml":

   curl -X POST http://localhost:8180/cspace-services/reports
     -i -u admin@{yourtenant}.collectionspace.org:{password}
     -H "Content-Type: application/xml" -T report1.xml
   
   

Documents and "forDocType" values

It is common to create reports that will describe collection objects that are associated to something like an Intake. In this case, the context document is an Intake (or Accession, etc.), even though the report will actually generate output about the cataloging (collection object) records. The forDocType values described in the XML above control when the user will see the report in the UI. You may infer from the XML above that one report can appear for multiple types of records - this is useful when, e.g., a report summarizes collection objects associated any kind of basic record (Intakes, Acquisitions, Loans, etc.). The value of the "csid" property passed in from CollectionSpace will be the unique id of the record the user is editing (e.g., the Intake, or the Acquisition). Your report query must select collection objects associated to that record through relations (although we are currently considering a simpler model for invocation to simplify report authoring).

Running a report

You can run (invoke, generate, etc.) a report:

• Via the CollectionSpace user interface, by selecting a report from the Run Report dropdown menu on most record editing pages, and clicking the Run button. This menu will display reports, if any, that you can run in the current context. (If one or more reports appear in the dropdown menu, you may still need to save a new record, or be editing an existing record, for the Run button to be enabled.)
• Programmatically, via the Reporting Service RESTful APIs.

Deleting a report

To delete a report; that is, to remove its entry from the Run Report dropdown menu, use the Services Layer REST API to:

• Make a call to that API to list Report records.
• From that list, find the CSID of the relevant Report record.
• Make a second call to that API to delete that record, via its CSID.

The CRUD+L (Create, Read, Update, Delete, List) section of the Reporting Service RESTful APIs describes how to make these calls. Here's also a succinct example of how to do so, in a message posted to the CollectionSpace Talk list.

Note that deleting a Report record in this way, only has the effect of removing its user-visible entry from the Run Report dropdown menu. Doing so does not delete the corresponding JasperReports file(s) (ending with the .jxrml and possibly also .jasper filename extensions) from the CollectionSpace server folder, nor does it delete the .jxrml JasperReports file from the Service Layer source code tree. (Optionally, those JasperReports files can be manually deleted and/or later updated with newer versions of those files, reflecting fixes, changes, and enhancements.)

See also

Some useful external links describing Jasper Reports files and how to author them:

Overview (Stack Overflow)

Samples demonstrating various types of queries and designs (JasperForge)