Report fields

Complete

OpenVPMS supports reporting using:

  • JasperReports - these are created using Jaspersoft Studio
  • Microsoft Word documents with MERGEFIELDs
  • OpenOfffice documents with User Fields

In order to create a report using any of these, you will need to know the names of the available fields.

The first step is to determine the template Type, from this you can get from the table below the name of the archetype. Note that three (those in CAPS) are not archetypes - see Special Reports below.

Template Type Archetype
Appointment act.customerAppointment
Bank Deposit act.bankDeposit
Customer Account Balance CUSTOMER_BALANCE
Customer Counter Sale act.customerAccountChargesCounter
Customer Credit act.customerAccountChargesCredit
Customer Estimation act.customerEstimation
Customer Form act.customerDocumentForm
Customer Invoice act.customerAccountChargesInvoice
Customer Letter act.customerDocumentLetter
Customer Payment act.customerAccountPayment
Customer Refund act.customerAccountRefund
Customer Statement act.customerAccountOpeningBalance
Grouped Reminders GROUPED_REMINDERS
Message act.userMessage
Patient Form act.patientDocumentForm
Patient Image act.patientDocumentImage
Patient Letter act.patientDocumentLetter
Patient Medication Label act.patientMedication
Patient Visit act.patientClinicalEvent
Prescription act.patientPrescription
Problem act.patientClinicalProblem
Reminder Report act.patientReminder
Stock Adjustment act.stockAdjust
Stock Transfer act.stockTransfer
Supplier Credit act.supplierAccountChargesCredit
Supplier Delivery act.supplierDelivery
Supplier Form act.supplierDocumentForm
Supplier Invoice act.supplierAccountChargesInvoice
Supplier Letter act.supplierDocumentLetter
Supplier Order act.supplierOrder
Supplier Remittance act.supplierAccountPayment
Task act.customerTask
Till Balance act.tillBalance
Work in Progress Charges WORK_IN_PROGRESS_CHARGES

 

To determine the available fields to use in the report, looking at the corresponding archetypes (see Administration|Archetypes).

For example, the act.customerAppointment has the following nodes:

        <node name="id" path="/id" type="java.lang.Long" hidden="true" readOnly="true" />
        <node name="name" type="java.lang.String" path="/name" hidden="true" minCardinality="1" derived="true"
            derivedValue="'Appointment'" />
        <node name="customer" path="/participations" type="java.util.HashSet" minCardinality="1" maxCardinality="1"
            filter="participation.customer" />
        <node name="patient" path="/participations" type="java.util.HashSet" minCardinality="0" maxCardinality="1"
            filter="participation.patient" />
        <node name="appointmentType" path="/participations" type="java.util.HashSet" minCardinality="1" maxCardinality="1"
            filter="participation.appointmentType" />
        <node name="startTime" path="/activityStartTime" type="java.util.Date" minCardinality="1" />
        <node name="endTime" path="/activityEndTime" type="java.util.Date" minCardinality="1" />

Each of these nodes may be used in reports as follows:

Node JasperReports Word MERGEFIELD/OpenOffice User Field
Field Expression Class
id $F{id} java.lang.Long id
name $F{name} java.lang.String name
customer $F{customer.entity.name} java.lang.String customer.entity.name
patient $F{patient.entity.name} java.lang.String patient.entity.name
appointmentType $F{appointmentType.entity.name} java.lang.String appointmentType.entity.name
startTime $F{startTime} java.util.Date startTime
endTime $F{endTime} java.util.Date endTime

Where a node refers to an archetype (e.g. the customer node is a collection of participation.customer), you can use "." to drill down on the nodes of that archetype.

In the above, "customer.entity.name" means:

  1. retrieve the object associated with "customer.entity"; and
  2. return its name node

To flesh this out a little, let us consider the act.customerAccountChargesInvoice and the party.customerperson archetypes which display (in part) as follows:

If you look at the Path column you can see 8 types of entries as follows:

Type Name Path Access via
Simple amount /total total
Simple/Detail reference /details/reference reference
Participation location /participations location.entity.xxx
SourceAct items /sourceActRelationships items.target.xxx
TargetAct reverses /targetActRelationships reverses.source.xxx
EntityLink practice /entityLinks practice.target.xxx
Contact contacts /contacts [function]
Classification tax /classifications [function]
Lookup title /details/title title
title.xxx
Lookup Local status /status status
status.xxx

In the above xxx indicates one nodes of the entity, target or source - eg location.entity.name - the way to figure out whether it should be entity or target or source is to look at the archetype for the coupling archetype, eg participation.location, actRelationship.customerAccountInvoiceItem, entityLink.customerLocation.

The [function] entry indicates that for these nodes (which can have multiple occurances - eg a customer can have multiple contacts), one needs to use one of the party:get() functions to access the information.

For items which have their values constrained by either one of the Lookups (eg title) or a set of local settings in the archetype (eg status) then using the simple reference (eg title or status) will access the stored value (eg MRS or IN_PROGRESS).  However, you can also used the .xxx form as follows:

  • title.code -> MRS
  • title.name -> Mrs
  • title.id -> 123  (the id of the lookup)
  • title.shortName -> lookup.personTitle (the archetype)
  • title.displayName -> the display name of the lookup
  • status.code -> IN_PROGRESS
  • status.name -> In Progress

Application Fields

The following fields are available to all reports1, and represent the current selections in the application, if any.  Note that if on the Workflow|Scheduling or |Work Lists screen you select an appointment or task, these become the current aqppointment or task.  However, the fact that the associated customer and patient details are displayed in the left panel does NOT mean that these become the current customer and patient.  The current customer and patient are those displayed on the Customers|Information and Patients|Information screens.

These fields may also be used in XPath expressions, by prefixing the field name with a $ (i.e. they are available in expressions as variables).

 

Field Archetype Description
OpenVPMS.patient party.patientpet The current patient
OpenVPMS.customer party.customerperson The current customer
OpenVPMS.practice party.organisationPractice The current practice
OpenVPMS.location party.organisationLocation The current practice location
OpenVPMS.stockLocation party.organisationStockLocation The current stock location
OpenVPMS.supplier party.supplier* The current supplier
OpenVPMS.product product.* The current product
OpenVPMS.deposit party.organisationDeposit The current deposit account
OpenVPMS.till party.organisationTill The current till
OpenVPMS.clinician security.user The current clinician
OpenVPMS.user security.user The current user
OpenVPMS.invoice act.customerAccountChargesInvoice

The current invoice. Only valid:

  • if in checkin, consult, checkout workflows
  • if an invoice is selected in the charges workspace
OpenVPMS.visit act.patientClinicalEvent

The visit for the current patient. Only valid:

  • if a patient visit is selected
  • if in the checkin, consult, or checkout workflow
OpenVPMS.appointment act.customerAppointment

The current appointment. Only valid:

  • when checking, consulting or checking out using an appointment
  • if an appointment is selected
OpenVPMS.task act.customerTask The current task. Only valid:
  • when consulting or checking out using a task
  • if a task is selected

1 With the exception of JasperReports SQL sub-reports

Field Samples
Field JasperReports Word MERGEFIELD/OpenOffice User Field
Field Expression Class
Practice name $F{OpenVPMS.practice.name} java.lang.String OpenVPMS.practice.name
Customer first name $F{OpenVPMS.customer.firstName} java.lang.String OpenVPMS.customer.firstName
Customer last name $F{OpenVPMS.customer.lastName} java.lang.String OpenVPMS.customer.lastName
Patient identifier $F{OpenVPMS.patient.id} java.lang.Long OpenVPMS.patient.id
Appointment date/time $F{OpenVPMS.appointment.startTime} java.util.Date OpenVPMS.appointment.startTime
User name $F{OpenVPMS.user.name} java.lang.String OpenVPMS.user.name

Expression Samples

Description Expression
Practice telephone [party:getTelephone($OpenVPMS.practice)]
Practice Location address [party:getCorrespondenceAddress($OpenVPMS.location)]
The current appointment start time as date with 'No current ...' check [expr:var( 'OpenVPMS.appointment.startTime', 'No current appointment')]
The current appointment start time as long date/time [date:formatDateTime( $OpenVPMS.appointment.startTime, "long")]
The current appointment start time like 'Monday 16 March at 9:45 AM' with 'No current …' check [expr:if(boolean(expr:var( 'OpenVPMS.appointment.startTime')), date:format(expr:var( 'OpenVPMS.appointment.startTime'), "EEEE d MMMM 'at' K:mm a"),'No current appointment')]

Note that the expr:var() function needs the name of the variable to test (eg 'OpenVPMS.appointment.startTime') whereas the date:format functions needs the actual variable, eg $OpenVPMS.appointment.startTime or

expr:var( 'OpenVPMS.appointment.startTime').

Application Fields as Report Parameters

JasperReports queries support parameters using $P{parameter name} syntax.

To use Application Fields as parameters, reference the field within a parameter's Default Value Expression.

E.g. to pass the current customer identifier, create a parameter with the following settings:

  • Name: customerId
  • Class: java.lang.String
  • Is For Prompting: Yes
  • Default Value Expression: "$OpenVPMS.customer.id"

Note that Class must be java.lang.String to avoid compilation errors, and that the parameter must be prompted for otherwise the $OpenVPMS.... will not be replaced with its current value.

If there is no current item (ie customer in the above case) then the parameter will be initialised to null.  Hence if the parameter was set as follows:

then the SQL code should be like the following - note the comparison to the string "0" to handle the 'all' case:

and for the display of the parameter in the report, you might want to use an expression like the following so that the 'no current' case is clearly identified:

($P{customerID}==null)||($P{customerID}.compareTo("")==0)?"--NONE--":$P{customerID}

 

Special Reports

 

These are special reports that are generated by running queries that:

  • derive values
  • return partial objects
  • return multiple objects

For CUSTOMER_BALANCE, the fields are:

Field

Type

Description

customer.objectReference org.openvpms.component .business.domain.im.common .IMObjectReference The customer reference
customer.name java.lang.String The customer name
balance java.math.BigDecimal The customer balance
overdueBalance java.math.BigDecimal The customer overdue balance
creditBalance java.math.BigDecimal The customer credit balance
lastPaymentDate java.util.Date The customer's last payment date
lastPaymentAmount java.math.BigDecimal The customer's last payment amount
lastInvoiceDate java.util.Date The customer's last invoice date
lastInvoiceAmount java.math.BigDecimal The customer's last invoice amount
unbilledAmount java.math.BigDecimal The customer's unbilled amount

For GROUPED_REMINDERS, the fields are:

Field Type Description
customer party.customerperson The reminder customer
patient party.patientpet The reminder patient
reminderType entity.reminderType The reminder type
product product.* The reminder product
clinician security.user The reminder clinician
startTime java.util.Date The reminder start date
endTime java.util.Date The reminder due date
reminderCount java.lang.Integer The reminder count
act act.patientReminder The reminder act

For WORK_IN_PROGRESS_CHARGES, the report is supplied with Invoices, Credits and Counter charges (act.customerAccountChargesInvoice, act.customerAccountChargesCredit, and act.customerAccountChargesCounter archetypes respectively), that have an In Progress, Complete or On Hold status.

The available fields are therefore the nodes present in each of these archetypes.

JasperReports Expressions

JasperReports can evaluate xpath expressions by declaring a variable that uses the EVALUATE() function. This takes a single argument, the expression to evaluate e.g.:

EVALUATE("party:getAccountBalance(.)")

Report parameters can be accessed by the function by prefixing their names with $P. SQL-based JasperReports can also access the report fields, by prefixing their names with $F. e.g.:

EVALUATE("product:price($F.productId, $F.unitPrice, $P.includeTax)")

The above calls the product:price() function, supplying the values of the productId and unitPrice fields, and the includeTax parameter.

The EVALUATE() function is not supported in reports that use Sort Fields, due to a JasperReports limitation. It will evaluate to null.

As a workaround:

  • remove Sort Fields. For:
    • SQL reports, perform the sorting within the SQL statement.
    • sub-reports created using ${dataSource}.getDataSource(node), specify the sort fields e.g:
$P{dataSource}.getDataSource("items", new String[]{"target.product.entity.name", "target.startTime"})
  • define a field that uses the legacy [] notation instead of a variable using EVALUATE e.g.:
[document:text(., 'note')]