Notes on using iReport

Owned by John Brandon Lowe MLIS PhD
Last updated: Nov 14, 2013 by Rick Jaffe

Links to iReport and reporting documentation on CSpace:

Reporting Service Description and Assumptions
How to add and run reports

How to connect iReport to the correct database

What you will need:

  • A computer and internet connection (Details not provided here!)
  • Depending on where you are accessing the database from, you may need
  • CalNet credentials (which will enable you to access calnet-restricted resources, specifically, the VPN.)
  • Your UCB-approved VPN setup working. The campus provides this free, see the following link:
  • PAHMA uses v2.4 of CSpace and this in turn as has a specific JasperSoft runtime install. You'll want to install iReport 4.0.2
  • A datasource for Dev and Production in iReport.
    The instructions, more or less current:
here

Walk-through an example

  • "Basic Group" iReport (groupObject.jrxml is attached to this page). Have a look!

Reports, etc. we might work on

  • The above: tweak? (It is supposed to show
  • SimpleObjectSummary: query works, revise layout
  • groupObject report : review
  • Key Info by group: is there one?

Design patterns for:

  • given authority item, find objects (e.g. given performer, find works, "find everything from China")
  • given group, get objects + metadata (e.g. groupObject)
  • given object, get associated data (from authorities, etc.)
  • given procedure, find objects (e.g. for an accession, find objects)

Below is a group you can use to test the installed iReport. You can run the iReport on your machine for this group: cut-and-paste the CSID below when prompted.

http://pahma.cspace.berkeley.edu:8180/collectionspace/ui/pahma/html/group.html?csid=c624b5e2-6916-43ea-85c0-efb6608a37c5

Let's do some real work. Here is the list of iReports needed for PAHMA:

http://issues.collectionspace.org/browse/PAHMA-224

it looks like the "Show all objects from a place report" is next in priority order
to work on.

The Brief Object Summary report (PAHMA-431). (A version exists but needs help; see attached)

It is described in PAHMA-431 as:

  • fields: Museum number, object name, current location (incl. container), site, culture
  • grouped by: -
  • ordered by: Museum number

(it is pretty much just the "Inventory report" without grouping by location)

Here is some SQL we looked at that still needs further testing. This is starting to show some best practices for SQL queries in CollectionSpace..

select
h1.name objectCSID,
c.objectnumber objectNumber,
ong.objectName objectName,
regexp_replace(pfc.item, '^.*\)''(.*)''$', '\1') site,
regexp_replace(apg.assocpeople, '^.*\)''(.*)''$', '\1') culture,
bd.item description
from collectionobjects_common c
left outer join hierarchy h1 on (c.id = h1.id)
inner join misc on misc.id = c.id
inner join collectionspace_core core on core.id=c.id
left outer join hierarchy hong on (c.id = hong.parentid and hong.name='collectionobjects_common:objectNameList' and hong.pos=0)
left outer join objectnamegroup ong on (ong.id=hong.id)
left outer join collectionobjects_pahma_pahmafieldcollectionplacelist pfc on (pfc.id=c.id and pfc.pos=0)
left outer join hierarchy hapg on (c.id = hapg.parentid and hapg.name='collectionobjects_common:assocPeopleGroupList' and hapg.pos=0)
left outer join assocpeoplegroup apg on (apg.id=hapg.id)
left outer join collectionobjects_common_briefdescriptions bd on (c.id = bd.id and bd.pos=0)
where h1.name='293a0eac-19e8-4c61-88ec'
and misc.lifecyclestate <> 'deleted'
and core.tenantid=15

Best practices for developing and testing reports

  • Use the dev database for development and testing of course!
  • Save frequently and take care of your versions (iReport does none of this).
  • Sketch out your report first: make a mockup, showing bands, headers, fields, etc.
  • Try to find a report that resembles what you want to do. It is esp. important to use an existing report as a template if you want to match the look-and-feel.

SQL queries for iReports

starting with an existing report that is similar (and what we might
do to create a starter set of queries based on existing reports); extracting display
names from refnames;

Using DBVisualizer to develop queries

  • take care not to run queries that might be bad ones; and so on.

E-R diagrams to help visualize how the tables in CSpace relate.

Once you have a query ...

  • To "connect" your query to your iReport:
  • Copy your query into the clipboard
  • Open your .jrxml file.
  • Click on the DB query icon
  • Paste your query there, check the set up fields and parameters (i.e. usually there are two {tenantid} and $P{CSID})

How to use the iReport interface to develop the report layout

Positioning fields and renaming labels: bands, etc.

Adding text and simple graphics

Grouping

Installing and testing your report in CSpace

  • You don't need to know this if you are using your report "standalone". If you want to be able to get a report on CSpace entities (e.g. objects, groups, etc.) you'll need to install your report.

Proposed Reports by Procedure
Reporting Service RESTful APIs