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.

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:

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:

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 reports¹, 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).

¹ With the exception of JasperReports SQL sub-reports

Field Samples

Expression Samples

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:

For GROUPED_REMINDERS, the fields are:

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.

$P{dataSource}.getDataSource("items", new String[]{"target.product.entity.name", "target.startTime"})

[document:text(., 'note')]