Reports and Documents

Complete

If you do need to create of modify documents and reports, the following should be useful.

OpenVPMS uses two 'vehicles' to generate printed (or pdf) output: OpenOffice (specifically its 'soffice' component) and JasperReports. Note that since OpenOffice can handle Microsoft Word documents, you can happily use Word rather than Open Office Writer to prepare letters and forms.

Which of these two is used depends on the document content set for the document template. (See Administration|Templates and Create/Edit Template.) If the document content is a .jrxml file, then JasperReports is used, if it is an .odt or .doc or .docx file then OpenOffice is used.

To create and edit the .jrxml files you use JasperSoft's Studio (which has superseded iReports but which can still be used if desired).

Note that JasperReports is used for any document that has more complex datasets such as invoices, statements, reports etc as these typically have multiple rows of data and require groupings and other data processing functions not available in OpenOffice. These are also rarely changed except during implementation.

OpenOffice is used for customer, supplier and patient documents. These are more likely to be modified and added to by the practice so a standard word processor editor is more appropriate.

The reports and forms/letters/documents get data in two ways:

  • Reports (ie things run from the Reporting|Reports menu) are all handled by JasperReports. These MUST have their Report Type set to 'Report' in their document template. The report parameters (if any) are passed as 'Parameters' and the 'datasource' is set to access the MySQL database.  The report then uses SQL to extract the required information from the MySQL database. Selection criteria and other parameters are entered via a report criteria screen.
     
  • Forms, letters, and system documents & reports (ie invoices & statements etc) may be handled by either JasperReports (for the complicated ones) or OpenOffice. For these the Report Type in their document templates will always be other than 'Report', eg 'Customer Invoice', 'Patient Letter', 'Customer Statement' etc. In both cases an appropriate dataset is made available - and this is determined by the Report Type. For OpenOffice (or M/S Word) this is accessed via merge fields, ie the facility that is commonly used in word processors to do mail merge. For JasperReports, the 'datasource' is set to access this dataset. In both cases the field names are the same. Note that the term 'field name' is a little misleading - as well as things that look like names such as customer.entity.name and patient.entity.species, you can use more complicated expressions like [openvpms:get(party:getPatientOwner(.),'lastName')] and [macro:eval('standardConditions')]. Expressions are always enclosed by square brackets.

    The available field names are documented here; the available expressions here. In addition http://www.openvpms.org/document-merging-open-office-writer provides a useful 'cheat-sheet'.

    Input parameters (commonly user input to letters) are entered via a parameter input screen.

 

Open Office Notes
The simplest way to generate a new .odt template is to use the sample templates included in the distribution.  These call out almost all the available fields and you can copy the required fields from the sample document.

Microsoft Word Notes
The simplest way to generate a new .doc (or .docx) template is to use the sample templates included in the distribution.  These call out almost all the available fields and you can copy the required fields from the sample document.

Note that although Word supports conditional fields, these are not implemented by the Open Office component used by OpenVPMS. Hence if you need conditional fields (eg to switch between 'him' and 'her' based on the patient's sex) then you will either need to use .odt templates. Alternatively you use the macro:eval function to run a macro that will return his or her depending on the sex.

Studio Notes
The following is not intended to be 'how to use Studio' but rather a set of notes that may help you when you are examining an existing report and trying to understand how it works.

Field names: For reports, when the datasource is the MySQL database, the field names are the names of the fields returned by the SQL query. Otherwise the datasource is a data collection and the field names are used the access the data collection - either via simple archetype dot notation (eg customer.entity.name), or via xpath expressions enclosed in square brackets (eg [party:getPartyFullName(.)] ).  While the former feels quite normal - ie using $F{customer.entity.name} is a reasonable syntax, the latter appears peculiar - $F{[party:getPartyFullName(.)]} does not look like a normal way to access something.  However, it works - what is happening is that the OpenVPMS code examines the name of the field, sees that it starts with '[' and ends with ']' and knows to interpret the contents as an xpath expression.

Sorting and Grouping: The Grouping facility assumes that the records are appropriately sorted. That is, if you use grouping to organise the output by patient/date/product type then the data must be sorted in this order - defining the groups does not do the sorting for you. For SQL based reports, you do the sorting in the SQL query. Otherwise you can do it either in the datasource specification (see below) or using the inbuilt facility - this is accessed via the Sort Fields entry in the Outline window
.

or alternatively via the Sorting tab in the Dataset and Query Dialog window (accessed via the icon)

Note that using the inbuilt sort, you can sort using fields (including ones that are xpath expressions) and variables, whereas in the datasource specification (see below) you can only sort by node names. Hence it is normal to do all the sorting here and omit all sort fields from the datasource specification.

Sub-reports: [Note that in contrast with 1.8 and earlier releases, sub-reports are now supported in all bands.] When using sub-reports you must specify the datasource for the sub-report in the main report. If the main report is using the MySQL database as the datasource, then the datasource part of the sub-report field in the main report will be set like the following:

However, if the main report is not using MySQL as the datasource (ie this is the sub-report for Customer Invoices or Customer Statements) then the datasource part of the sub-report field in the main report will be set like the following:

The datasource expression can also have a second argument as follows:

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

Here, the first argument "items" specifies the node in the report's data source (eg act.customerAccountChargesInvoice for an invoice), and hence this accesses the invoice's items. The strings in the second argument specify the names of the nodes to sort the data by. Note that these must be node names, they cannot be xpath expressions. Hence, as indicated earlier sorting is normally done in the report itself rather than in the getDataSource call.

Now the 'items' node of the act.customerAccountChargesInvoice archetype is a collection of actRelationship.customerAccountInvoiceItem archetypes, and in these the node named 'target' is the collection of act.customerAccountInvoiceItem archetypes which are the invoice line items.

Hence in the sub-report, the field names are target.xxx where xxx is the node within the act.customerAccountInvoiceItem archetype.

In some cases we want to invoke a subreport just once - this is done in the invoice and other templates to invoke the letterhead and address block subreports. So here we pass a node such as 'customer' or 'patient'. This works for the letterhead and address block subreports because (unlike the invoice items case) the subreport contains nothing dependent on the data source and only things like OpenVPMS.location.letterhead.target.logoFile and [party:getTelephone(party:getLetterheadContacts($OpenVPMS.location))] that are independent of the data source.

NOTE that the Subreport Expression contains the name of the Content of subreport - not the template name.  Hence if you upate say the Invoice Items template with a modified content file (say InvoiceItems-BE-1.jxrml) then the Invoice template will not be able to find the items content because you have changed its name. That is, if you are editing the content of a subreport, you must not change the name of the content file.

Furthermore you should not have multiple templates that have content of the same name - IF one of the templates is a subreport. This is because when the subreport content is used when the report is run, it is highly likely that the wrong one will used since there are multiple content files with the same name.

Studio Preview: If you building a report that uses the MySQL datasource, then you can use Studio's Preview facility. To set this up you need to add the datasource.

Click the New Data Adaptor button:

First select a location to save the data adaptor XML file that is being built:

Press Next and select the Database JDBC Connection:

Then press Next to create a new 'Database JDBC connection' and then set the details as follows:

and then use the Driver Classpath tab to set the location of the mySQL connector jar:

You will need to adjust the C:\OpenVPMS\Current-release to match your <OPENVPMS-HOME> location.

Then press Finish.

You will find that when using the Preview facility that a common error is to leave the Data Adaptor set to its default of 'One Empty Record' - the symptom is that the report finds no data.  If this happens, check that you have the data adaptor set correctly.

 

 

 

Email Letterhead Support
If reports and documents are set up to be printed on letterhead paper, then if they are emailed rather than printed, one needs a facility to insert the letterhead when the item is being emailed but not if it is being printed.

This is achieved through the IsEmail property which is supported for OpenOffice and JasperReports templates.

To use it in OpenOffice templates:

  1. Create a new field by going Insert -> Fields -> Other -> User Field
  2. In Name, enter "IsEmail" (without quotes)
  3. In Value, enter "unset" (without quotes)
  4. Click on the green tick
  5. Create a section to display when printing by going Insert ->Section
  6. Name it "Print Letterhead"
  7. Tick the Hide box
  8. Enter: IsEmail == "true" in the With Condition box
  9. Click OK
  10. Repeat 5-9 for a section named "Email Letterhead" but enter IsEmail == "false" in the With Condition Box.

The "cartrophen first reminder.odt" and "desex first reminder.odt" templates in the release distribution demonstrate this. 

To use it in JasperReports templates:

  1. Add a Boolean parameter, IsEmail
  2. For elements that should only be displayed when emailing, set their "Print When Expression" expression to: $P{IsEmail}.equals(Boolean.TRUE)
  3. For elements that should only be displayed when printing/previwing, set their "Print When Expression" expression to: $P{IsEmail}.equals(Boolean.FALSE)

Any of the JasperReports in the release distribution illustrate this.