How to add and run reports

Prerequisites

To author reports, you need to have:

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

Download and install the Jaspersoft Studio application. This is free, open source software from Jaspersoft, which you can download from:

https://community.jaspersoft.com/project/jaspersoft-studio/releases
Read the tutorials on the same page (and any others you find useful).
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 allow remote access to the database by editing the PostgreSQL configuration files:
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..)

Writing a report

Steps to create a new report in Jaspersoft Studio:

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.
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 soft-deleted (marked as deleted, but still present in the database). Here is an example query that gets values 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 (tenantid) in Jaspersoft Studio, and the property value is used in the query. CollectionSpace will pass in the current tenant ID when the report is run on the CollectionSpace server, so the same report can be used for different tenants in the system. For your testing, set the tenantid property to have a default value corresponding to the tenant you most commonly use. This id 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 reports provided with CollectionSpace, this parameter often has a default value similar to:

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

The effect of this definition is to look for a csid parameter passed in from CollectionSpace, and produce a report on that single record if it is set. If the value is not set by CollectionSpace (when the report is run without any context), the report is run on all records (of a certain type). Authoring a report this way allows it to be run either for a single record, or without a context (from the Tools/Reports screen).

There are currently several reports installed with CollectionSpace. You can see these reports (and download them to view in Jaspersoft Studio) in the CollectionSpace source code repository.

Preparing reports for running from within CollectionSpace

To make the report available to CollectionSpace:

Copy the .jrxml file to the reports directory on the server. Report files should be installed in the cspace/reports directory under the Tomcat directory (whose path should be in the CSPACE_JEESERVER_HOME environment variable). Note the name of the file, e.g., "acq_basic.jrxml".

Create a report record to register the report with CollectionSpace. This requires you to run a curl command to send the record to the server's REST API. The following example declares that the report "acq_basic.jrxml" should be shown to users when they are editing Acquisition records. Set the outputMIME value to the desired default output type for the report. This can be any output type supported by JasperReports.

<?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.jrxml</filename>
    <outputMIME>application/pdf</outputMIME>
  </ns2:reports_common>
</document>

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 "report.xml":

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

Documents and forDocType values

It's common to create reports that describe Object records that are related to a procedural record, such as an Intake. In this case, the context document is the procedural record (the Intake, Accession, etc.), even though the report will actually generate output about the related Object records. The forDocType values present in the XML above control where 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 a report summarizes Objects associated with multiple kinds of procedural records (Intakes, Acquisitions, Loans, etc.). The value of the csid parameter passed to the report from CollectionSpace will be the unique id of the record on which the user ran the report (e.g., the Intake, or the Acquisition). Your report query must select Object records associated to that record through relations.

Running a report

You can run (invoke, or generate) a report:

Via the CollectionSpace user interface:
- By clicking on a report from the "Reports" list in the sidebar of record editor screens. This list will display reports, if any, that you can run in the current context.
- By selecting search results, and clicking on a report from the "Reports" list in the sidebar of search result screens. This list will display reports, if any, that you can run in the current context.
- By selecting a report on the Tools/Reports screen, and clicking the "Run..." button. This allows you to select the context in which to run the report.
Programmatically, via the Reporting Service REST APIs.

Deleting a report

To delete a report, 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.

Note that deleting a Report record in this way only has the effect of removing its availability from the UI and REST API. Doing so does not delete the corresponding JasperReports file(s) (ending with the .jxrml and possibly also .jasper filename extensions) from the reports directory on the CollectionSpace server, nor does it delete the .jrxml JasperReports file from the Services 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.)