Designing Custom Reports

• Overview
• Intended Audience
• Report System Architecture
• Report Types
  • Global Report Rendering Chain
  • Instance Report Rendering Chain
• Report Data Sources
• Custom Uploaded Reports
• Local Development Setup
  • Download and Install Jaspersoft Studio
  • Install and run MySQL Server
  • Download a Backup
  • Initialize MySQL and Load Backup
  • Configuring Jaspersoft
  • Configure XML Test Connection
• Global SQL Report Tutorial
  • Open Jaspersoft Studio
  • Create New Report File
  • Set Report Language
  • Define Report Query
  • Add Query Fields To the Report
  • Upload Report to Flex
• The Flex Data Model Explained
  • Legacy Modules
  • Keys and Normalization
    • Managed Resources
    • Resource Types
    • Financial Aggregation
    • Project Elements
  • Soft Deletes
  • Audit Trail Columns
• User Parameters
• Injected Parameters
  • Injected Parameter Example
• XML-based Reports
• XML Data Producers
• Modern Localization

Overview

This section describes the process of developing new reports for Flex. In this section we will discuss the Jasper Reports engine, the report pre-processing architecture, injected parameters and various techniques for accessing report data. Since most reports are SQL-based, we'll describe in simple terms the structure of the Flex database. The last part of this section documents in detail the various XML data providers for XML-based reports.

Intended Audience

Custom report design is considered a task for specially trained Data Analysts or other IT professionals. While it's possible for lay users to develop these skills, report design for Flex (or for most systems) will require a working knowledge of the MySQL database, SQL (Structured Query Language), and XML. In addition, it's desirable for report designers to have experience with iReport, Jaspersoft Studio, or a comparable report design tool such as Crystal Reports. In most cases, iReport and Jaspersoft Studio can be used interchangeably, but Jaspersoft Studio is the modern incarnation of the tool and as of Flex 4.18.1, JasperReports 6.1.1 is supported.

Report System Architecture

As part of Flex's commitment to open standards and open source technologies, reporting and most printed output in Flex is generated using an open source reporting library called JasperReports. To this architecture, Flex adds a pre-processing layer for dealing with injected parameters (more on that later) and localization issues.

Report Types

In general, there are two types of reports in Flex.

• Global Reports – Global reports are not bound to any specific data object and are designed to operate independently on the entire data set. Global reports will typically have a link on the system's main menu and be accessible from anywhere in the system. When most people think of reports, they think of high level aggregation of data that provides a summary of financial activity for some period of time, like a month. That type of report is a global report.
• Instance Reports – These reports are designed to be bound to data objects like Inventory Items, Quotes, Purchase Orders, etc. They will only be accessible in the context of the object to which they are bound. For example, a common type of instance report would be a custom cover sheet for Quotes, which would only be accessible while viewing a Quote. A cover sheet isn't necessarily a "report" in the traditional sense, but since Flex makes use of JasperReports for most of its PDF generation, the architecture does consider all PDF generation to be reporting. From a business perspective however, these kinds of reports might best be thought of as custom documents or formats.

Both report types share a lot of the same elements in what we call the rendering chain. The major differences concern how the reports are invoked and the use of injected parameters. Consider the following flowcharts:

Global Report Rendering Chain:

Instance Report Rendering Chain:

Report Data Sources

There are also two ways to get data for a report. The most common is to use an SQL query to pull data directly from the database. Since the Flex backend is a multi-layer architecture (separating business logic from data), it may be that information desired in a report is not available from the database, rather it requires the business logic of the application layer. For this kind of information, Flex has created a number of XML data producers that can be invoked via the API. The report can then organize and query the XML data using XPath.

Custom Uploaded Reports

The Flex architecture supports a variety of techniques for developing and defining custom reports including embedded Jasper reports, custom PDF generators, external report server integration, and so on. However the primary area of interest for privately developed reports is the Custom Reports section. To familiarize yourself with this area, select Custom Reports from the System Settings menu. You should see an empty list of custom reports or a very short list (unless Flex has provided you with custom reports as part of your support agreement). In either case, click the New Report button and take a look at the form. Some documentation is provided in this user interface, but the table below provides even greater detail.

• Name: This is the name by which you will refer to the report. This name will appear in any configuration drop downs or menu links.
• Report Source: This is where you will upload your custom report file and incorporate it into the system. The file must be a JasperReport file, with file extension .jrxml.
• Valid Extensions: The element extensions the report can be ran against. Examples are Financial Documents (Quotes, Invoices, POs, etc) and Equipment Lists (Pull Sheets, Manifests, etc).
• Authorized Groups: This important security setting is where you configure which users are authorized to run a report. Click one or more groups and only members of those groups will be able to run the report (or even know it exists).
• Report Formats: Sets which output formats will be available from the run-time drop-down menu. Typically, most reports are PDF, but CSV, Excel, and Word are also available.

Local Development Setup

The process of developing a new report includes a number of steps, some of which are necessary for each report and some of which are needed only to setup a report development environment. The basic steps are as follows.

1. Download and Install Jaspersoft Studio

While the engine used to render reports is JasperReports and it's possible to create report source files by directly editing the XML, most people prefer to use a report design tool. For JasperReports, this tool is called Jaspersoft Studio. Jaspersoft Community offers free-to-download software, but does require a free account to do. Download the correct version for your operating system here.

Once installed, be sure to change the compatibility mode to match the version in Flex, which is currently 6.1.1 - instructions to change this are available here.

2. Install and run MySQL Server

If you plan to use SQL-based reports, you will need to run a local copy of the MySQL database. This can be on your local desktop or laptop or on a server if you have one. We recommend downloading MySQL Community Server from MySQL. For browsing the data and testing queries, you might also wish to download the MySQL Workbench. After installation, you will need to start the server.

On Windows, you should have two options. The first is to click Start or use the Start button on your keyboard, then type "Services" and hit Enter. You should find a "MySQLXY" (where XY is the version) in the list of Services. Right-click it and then click Start.

The second option is to run CMD as Administrator. To do this, click Start or use the Start button on your keyboard, then type "cmd." You should get a Command Prompt result. Right-click it and select "Run as Administrator." Then, change to your MySql Server directory by entering the following (substitute X and Y for the version which you've installed:

MySQL Server Start on Windows

cd C:\Program Files\MySQL\MySQL Server X.Y\

net start mysqlXY

On Mac OS, you may have two options. The first is to open System Preferences > MySQL and click "Start MySQL Server." If you lack that option, run Terminal.app and enter the following:

MySQL Server Start on Mac OS

cd /usr/local/mysql/support-files

sudo ./mysql.server start

The second option is via System Preferences. You should have a MySQL icon in the bottom section. Click it, then click the "Start MySQL Server" button.

3. Download a Backup

Login to your Flex system and select Backup History from the System Settings menu. Click the download button next to any backup you like, although for the most recent backup, click the first button. The backups are compressed tarballs in gzip format. While this is a format common on Linux or other Unix-based systems, Windows and Mac OS both recognize this format and will be able to extract it. The backup file contains a directory structure and one file ending with .mysql, usually starting with the name of your company. For example, if your company is called Awesome AV, this file will more than likely be called awesomeav.mysql. Once the download is complete, extract the .mysql file and move it to a convenient location on your hard drive.

4. Initialize MySQL and Load Backup

Once you've installed MySQL and started MySQL server, open the command line interface. On Windows, you may have a MySQL 5.x Command Line Client depending on your installation. To check, click Start or use the Start button on your keyboard and type "MySQL." You should see a list of "Apps" and one will be a Command Line Client. If not, you can use Start > Run > Cmd to get a command prompt. If you run the MySQL 5.x Command Line Client, you should be prompted for a password. If you run Cmd, you will need to run the mysql executable first. The following is an example of how to do so:

Start mysql on Windows

cd C:\Program Files\MySQL\MySQL Server 5.7\bin

mysql -u root -p

Start mysql on Mac OS

cd /usr/local/mysql/bin

./mysql -u root -p

At this point, regardless of the method or operating system used, you should see the "Enter Password:" prompt. Enter the password used or provided during installation.

Once access is granted, type the following commands, substituting the correct values for your system as needed. Note that "report_dev" can be substituted for whatever you prefer to call the new schema/database.

Create database on Windows

create database report_dev;

use report_dev;

source C:\Users\Default\Downloads\awesomeav.mysql;      (Windows)

source /full/path/to/awesomeav.mysql;                   (Mac)

This will restore the full database to your local development server. It should only take a few minutes to complete the process. If you have an especially large amount of data and the process takes more than an hour, we recommend you reconfigure MySQL to use the same high availability InnoDB engine we use on all our cloud servers.

If you installed the MySQL Workbench, the next step would be to open it and define a connection to your MySQL Server. Regardless of operating system, provided you supply the correct password, the following parameters should work for any locally hosted server:

Once the connection to the server is configured, you should be able to open a connection by clicking or double-clicking the button in the MySQL Connections list. Once open, choose the Schemas tab on the right-hand side of the screen and double-click your new database/schema to select it as the default for all queries. After that, make sure you can browse the tables and see the data. You should be able to browse your inventory, for example, by browsing the st_ivt_inventory_item table.

5. Configuring Jaspersoft

Once installed, be sure to change the compatibility mode to match the version in Flex, which is currently 6.1.1 - instructions to change this are available here.

In the Java world, connections to databases are typically provided via a special driver called a JDBC driver. In order for Jaspersoft to talk to your database you'll need to first install the MySQL JDBC driver. Start by downloading the driver from MySQL. Once the driver is downloaded, extract the archive and launch Jaspersoft. Open the Preferences dialog and under Java > Build Path > Classpath Variables, add the mysql-connector-java-5.1.16-bin.jar file using the New... button.

Next, create a connection to the database in Jaspersoft. Using the Data Adapter Wizard dialog, select the Database JDBC Connection, enter the server and security information for your development database server and click 'Test'. If you get a successful test, you're ready to begin developing reports. Please note, not all reports will preview correctly in Jaspersoft. As such, it's highly recommended that you simply upload the report to your Flex instance and test it there. Please reference the User Manual for details.

6. Configure XML Test Connection

Flex includes a special framework for providing data for XML-based reports. These are useful in those situations where SQL-based data is unavailable or impractical for a given report. Flex supports this through a set of custom XML Data Producers. Each data producer generates a unique XML stream for a unique purpose. All data producers have an id and a set of parameters that they support. For example, the Financial Document data producer requires the id of a financial document (Quote, PO, etc.) as a parameter.

These producers can be configured inside Jaspersoft as data sources using the Data Adapter Wizard. The best way to start is to construct a valid URL for retrieving XML data using a standard web browser. Please note, not all web browsers display unformatted XML content correctly. If you see a string of text without an HTML structure, try another browser or search the Internet for a solution. As a note, Firefox tends to be the most reliable for displaying useful XML data across operating systems.

XML Data Producer URIs

Legacy XML Data Producer URIs took the following general form:

https://<server-host-name>/xml-data/<data-producer-id>?<PARAM_ID_1>=<param-value-1>&<PARAM_ID_2>=<param-value-2>

Here's what a valid XML Data Producer URL for the Financial Document Data Producer configured to run against our QA system looked like:

http://qatest.flexrentalsolutions.com/xml-data/data-producer-financial-document?PROJECT_ELEMENT_ID=e732edb6-7fd0-11e0-9f70-1231390c71d1

Modern XML Data Producer URIs take the following general form:

https://<server-host-name>/f5/api/data-producer/<data-producer-id>/<ENTITY_ID>?<PARAM_ID_1>=<param-value-1>&<PARAM_ID_2>=<param-value-2>

And the same example:

https://qatest.flexrentalsolutions.com/f5/api/data-producer/financial-document/e732edb6-7fd0-11e0-9f70-1231390c71d1

In the previous example, the data producer id is data-producer-financial-document and the parameter is PROJECT_ELEMENT_ID. Also note that the value provided for the PROJECT_ELEMENT_ID parameter is a 36-character string. This value is a UUID (Universally Unique Identifier) used internally by Flex to uniquely identify data elements. For this example to work on your system, you'll first need to find a valid UUID for one of your own project elements, such as a Quote. To find this value, login to Flex and open any Quote, Invoice or Purchase Order. In the address bar of your browser, you will see a URL like this:

https://qatest.flexrentalsolutions.com/f5/ui/#fin-doc/5b33d2bc-4c8c-41d7-9a75-be892f85dd1d/doc-view/ca6b072c-b122-11df-b8d5-00e08175e43e/header

The UUID value following the /#fin-doc/ part is the UUID for the Financial Document. For non-Financial Documents, it will be the value after the /#element/ part

For data producers that require an Element Definition's UUID, navigate to Projects > Element Definitions and double-click the Definition you need; again the UUID will be in the URL.

To test this, copy the value from the URL in your system and replace the UUID in our legacy example. If you paste the full URL in your web browser, the system should prompt you to login and return with a valid XML document describing the Quote, Invoice or Purchase Order you selected. If you are not prompted to login, you may not have sufficient permissions.

To test the modern method, you will need to first have API integration turned on and an API key created. See API Getting Started Guide for details. You can then use the URL for you system and element, along with the API key and any 3rd-party HTTP request application to generate the output.

If you have Jaspersoft Studio, use the Repository Explorer and it's "Create Data Adapter" button in its toolbar. If you don't see a Repository Explorer, go to Window > Show View > Other and type in "repo." You should see the result under the Jaspersoft Studio directory.

In the Data Adapter Wizard, choose "XML document," give it a name, paste your URL into the File/URL field and then click the Options button to its right. Click the Http Headers tab, then Add "X-Auth-Token" as the name and your API key as the value, click OK, and OK again to close the Http Connection Options dialog. Check the "Enable namespaces support" checkbox and select the "Use the report Xpath expression when filling the report" radio button. Then click Finish. You now have a reference to real data from your live system to work with while designing a report.

A note on XML Data Producer URLs

Global SQL Report Tutorial

In this section, we'll work through a short Jaspersoft tutorial and create a simple SQL-based report that prints out a list of inventory items.

1. Open Jaspersoft Studio

Launch Jaspersoft and if you have not yet created a connection to your local database (JDBC) as described above, do so now.

2. Create New Report File

Click File > New > Jasper Report and select a paper size from the dialog. Then click Next and give your report a file name. Make sure you note the file for easy access later.

3. Set Report Language

Open the report properties pane and set the Language property to an appropriate value. If targeting the Flex4 engine, you will need to use 'java.' However, Flex5 supports both 'java' and 'groovy.'

4. Define Report Query

Open the Report Query Dialog. Make sure the query language is SQL and type enter the following SQL query...

SELECT * FROM st_ivt_inventory_item AS i
JOIN st_biz_managed_resource AS r ON i.id = r.id
WHERE r.is_deleted = 0
ORDER BY r.resource_name;

Jaspersoft should automatically load a list of fields based on this query. For performance reasons, you should enumerate the fields you wish to retrieve in practice, but we'll leave the * for now.

Click OK to save the query.

5. Add Query Fields To the Report

Back in the design view, you should see a collapsible tree containing design elements you can drag into the report. Open the node labeled "Fields". Here you'll see a list of columns returned by the report query. Drag a few of these fields into the detail section of the design layout. Feel free to choose any fields you like, but we'd suggest you include bar_code_id and resource_name.

Next, click the Preview tab in Jaspersoft. The system will then execute the query and generate the report.

6. Upload Report to Flex

We recommend that you test your report in Flex fairly early in the design process, especially if your report relies on injected parameters.

Login to Flex and open the Custom Reports page from the System Settings menu. Click New Report, enter a name, and select the report source file. You might also wish to confine the report to the Administrator's security group until it's finished and ready for public consumption. To improve performance, Flex caches compiled reports and menu settings in a special cache called the config-cache. To see your report in the Reports menu, you'll first need to force the cache to refresh. Since this example does not use Injected Parameters, the quickest way to do this is to log out and log back in.

Once you're logged back in, you should see your test report under the Reports menu. Click the link and, if your report is properly formatted, you'll be able to download it as a PDF. If it is not properly formatted, you will get an HTTP error.

If this worked, you're off and running. Simply return to Jaspersoft, work with your report until it's exactly how you want it and re-upload it (clearing the config-cache by logging out should no longer be necessary).

The Flex Data Model Explained

The data model that underpins Flex consists of over 500 database tables, but only a fraction of these are important for most report applications. In this section, we'll discuss the basic structure of the database and a few key concepts needed to understand it. We assume that the reader understands SQL, but we will provide a few basic query templates for the most common situations.

Legacy Modules

The Flex database is organized, much like the legacy source code, as a series of modules. Each table has a prefix that identifies the module. The table below lists each module, the table prefix, and a brief description

With the modernization of the Flex code base through the Flex5 effort, the above is no longer the complete story. For instance, the innodb_monitor table no longer exists and the ACTIVEMQ_* and st_sys_connection_pool tables are no longer used. Additionally, many new tables do not follow the rh_ and st_ naming convention.

Keys and Normalization

Most databases use sequential integers as primary keys. Flex was designed to run in clustered database environments without the need to centralize the key generation process and for this reason, Flex is based on 36-character UUID's instead of sequential integers. Other than the datatype, primary keys in Flex work just as they would in any other database.

The Flex data model adheres almost exclusively to Third Normal Form. This means data is never duplicated in multiple places. We consistently use foreign keys and this means that developers with access to database modeling tools should be able to easily reverse engineer a visual representation of our data model.

We've tried to keep the database self-documenting in that the table names and the column names are reasonably descriptive. However, there are a few key areas where we do rely on design abstractions and any productive use of our database for reporting purposes will require knowledge of these abstractions. There are two key abstractions to know about: managed resources and project elements.

Managed Resources

Any data object that can be tracked and scheduled (contacts, services, inventory, business locations) is considered a managed resource. Therefore all tables used to model inventory items or any other managed resource must be joined to the base managed resource table. A partial data model diagram showing the structure of managed resources is shown below. Note that st_biz_managed_resource is the base table and that the other tables join to it through its id field. In many cases, the joining table uses another id field, but in some cases, like st_biz_rc_resource_types, the resource_id column is the matching field.

click image to expand

This diagram shows that for a complete picture of a given resource, the table for a specific kind of resource (we call them resource classes) must be joined to the base managed resource table.

Let's consider a few examples. The following query will return a list of all inventory items:

SELECT * FROM st_biz_managed_resource AS r
JOIN st_ivt_inventory_item AS i ON r.id = i.id
WHERE is_deleted = 0;

This query returns a list of contacts:

SELECT * FROM st_biz_managed_resource AS r
JOIN st_crm_contact AS c ON r.id = c.id
WHERE is_deleted = 0;

If you compare the two queries, you can see that they both join with the st_biz_managed_resource table. If you don't need any data from the st_biz_managed_resource table, its perfectly fine to omit it, but for certain types of queries, this isn't possible.

Resource Types

For example, consider a situation where you want a report that lists each vendor you work with. Vendors are contacts, but not all contacts are vendors, so the contacts must be filtered based on the resource type. To return a list of vendors, use the following query:

SELECT * FROM st_biz_managed_resource AS r
JOIN st_crm_contact AS c ON r.id = c.id
WHERE r.is_deleted = 0
AND r.id IN
    (SELECT l.resource_id
    FROM st_biz_rc_resource_types AS l
    JOIN st_biz_resource_type AS t ON l.resource_type_id = t.id
    WHERE t.resource_name = 'Vendor');

Note that this query has sub-par performance because of the sub-select in the where clause. A more performant version would look something like this:

SELECT * FROM st_biz_managed_resource AS r
JOIN st_crm_contact AS c ON c.id = r.id
LEFT JOIN st_biz_rc_resource_types AS rrt ON rrt.resource_id = r.id
WHERE r.is_deleted = 0
AND rrt.resource_type_id = ?

Where the ? is the UUID of your "Vendor" Resource Type.

Financial Aggregation

We'll cover project elements in a bit more detail later, but the next query example illustrates the power of custom reports when managed resources and project elements are combined. This query will return a list of clients that have spent at least $1 with you and will also return the total amount of money spent by the client.

SELECT *, sum(p.actual_revenue)
FROM st_biz_managed_resource AS r
JOIN st_crm_contact AS c ON r.id = c.id
JOIN st_prj_project_element AS p ON r.id = p.client_id
WHERE r.is_deleted = 0
AND r.id IN
    (SELECT l.resource_id
    FROM st_biz_rc_resource_types AS l
    JOIN st_biz_resource_type AS t ON l.resource_type_id = t.id
    WHERE t.resource_name = 'Client')
GROUP BY r.id
HAVING p.actual_revenue >= 1;

Project Elements

As most Flex users know, Flex supports a flexible, configuration-based approach to Quotes, Invoices, Purchase Orders, Pull Sheets, etc. The downside for report designers is that this abstraction means there are no tables called 'Quote' or 'Invoice'. Instead we have the concept of Project Elements. Project Elements work much like Managed Resources in that the design uses an inheritance architecture with the st_prj_project_element table as the base table. This approach differs from managed resources in that there are three parallel data models: one for the project elements themselves, one for the project element configuration, and one for line items. The project element data model is shown below (with selective columns for clarity).

In order to use this data model for a useful purpose like displaying a list of Quotes, it's first necessary to determine which project element definition is used for Quotes on your system. The best way to do this is to run a simple query like this one:

SELECT id, name_singular FROM st_prj_element_definition;

On one of our test systems, this query produces the following results:

+--------------------------------------+-----------------------------+
| id                                   | name_singular               |
+--------------------------------------+-----------------------------+
| 0627fc58-16c1-102d-9c2d-00e081c0bae2 | Quote                       |
| 2333d1de-6a52-11e0-afa7-1231390c71d1 | Subrental Intake Manifest   |
| 23e51460-519b-11df-81b5-1231390c71d1 | Manifest                    |
| 24e77cb8-38fe-102d-b833-00e081c0bae2 | Proposal                    |
| 298fe90a-1656-102d-9c2d-00e081c0bae2 | Document                    |
| 444c0fa6-1711-102d-9c2d-00e081c0bae2 | Dead Sheets                 |
| 517f80f8-1663-102d-9c2d-00e081c0bae2 | Event Folder                |
| 61880d20-40ee-11e0-a0fa-1231390c71d1 | Event Session               |
| 7ab77a60-7285-11e0-afa7-1231390c71d1 | Subrental Return Manifest   |
| 7b2304aa-838a-11df-96ff-1231390c71d1 | Memo                        |
| 7b36db76-16d6-102d-9c2d-00e081c0bae2 | Rental PO                   |
| 8119f2cc-31b0-102d-b833-00e081c0bae2 | Sale Quote                  |
| 84a43130-1664-102d-9c2d-00e081c0bae2 | Task                        |
| 939839ce-1660-102d-9c2d-00e081c0bae2 | Crew List                   |
| 967555a2-7cb1-11e0-9f70-1231390c71d1 | Purchase PO Intake Manifest |
| 97e9edc4-7862-102d-917d-00e081c0bae2 | Worksheet                   |
| a49da632-67d2-102d-917d-00e081c0bae2 | Vacation                    |
| a5001f18-17fc-102d-9c2d-00e081c0bae2 | Invoice                     |
| a578d282-3fb1-11e0-a0fa-1231390c71d1 | Multi Session Quote         |
| a922dfd4-3266-102d-b833-00e081c0bae2 | Pull Sheet                  |
| bb064608-1661-102d-9c2d-00e081c0bae2 | Expense Sheet               |
| c5335df4-7ad0-102d-917d-00e081c0bae2 | Quick Quote                 |
| dff658d0-7a3f-11e0-9f70-1231390c71d1 | TO Manifest                 |
| e52c45fa-1705-102d-9c2d-00e081c0bae2 | Purchase PO                 |
| f3345af6-170b-102d-9c2d-00e081c0bae2 | Transfer Order              |
+--------------------------------------+-----------------------------+
25 rows in set (0.00 sec)

Now that we have a list of project element definitions and their ids (bear in mind the id's may be different in your system), we can construct a query. The next query shows how to obtain a list of Quotes based on the above table of ID's.

SELECT * FROM st_prj_project_element AS p
JOIN st_fin_document AS f ON p.id = f.id
WHERE p.is_deleted = 0
AND p.def_id = '0627fc58-16c1-102d-9c2d-00e081c0bae2'

Soft Deletes

You may have noticed in the above examples that most queries contain a reference to an is_deleted column in the WHERE clauses. The reason for this is that Flex employs a soft delete scheme for many types of data elements, including the following:

• Managed Resources
• Project Elements
• Project Element Line Items (when the owning element is deleted)

Make sure that all queries take this into consideration or deleted data may be included in the reports. Note that its use can also be written like this:

SELECT * FROM st_prj_project_element AS p
WHERE !p.is_deleted;

Audit Trail Columns

Though they've been omitted from the diagrams above for clarity, almost all tables in the Flex data model contain the following four columns:

• created_by_date
• created_by_user
• last_edit_date
• last_edit_user

These columns can be used for any reports that need to take into account when changes were made and by whom.

User Parameters

Sometimes a report will require input from a user in order to be processed. The most common example of this would be date ranges. For example, consider a report that prints out a list of Quotes starting between a range of dates.

The JasperReport and Jaspersoft documentation has more detail on parameters, but to start the process, create a new report in Jaspersoft and add two new parameters called START_DATE and END_DATE, taking care to make sure the parameter type is java.util.Date.

Next, create a query that restricts the data based on these parameters. We'll modify the Quote listing example from above to incorporate the date range. Note that the following query is not valid for any other purpose than use in a Flex Custom Report. Attempting to run such a query in mysql or MySQL Workbench will result in a syntax error. If you want to test such a query, replace the parameters with strings or MySQL variables.

SELECT * FROM st_prj_project_element AS p
JOIN st_fin_document AS f ON p.id = f.id
WHERE !p.is_deleted
AND p.def_id = '9bfb850c-b117-11df-b8d5-00e08175e43e'
AND p.planned_start_date > $P{START_DATE}
AND p.planned_start_date < $P{END_DATE}

Next, add some fields from the query in the report, then upload it to Flex. You'll then need to map the parameters in the Flex report configuration by clicking the Add Parameter button. Configure each parameter in accordance with the table below.

Click UPDATE and run the report from the Reports menu. You should then see a popup dialog prompting you for the start and end dates. Fill in the dates and run the report.

Injected Parameters

Certain parameters are special signals to Flex that it needs to do something special with a report prior to executing it. Usually, this involves injecting a Project Element id in a Quote cover sheet or similar report, but it could mean any number of injected parameters. The table below lists all automatically injected parameters supported as of this writing:

Injected Parameter Example

To create a report with injected parameters (we'll use Quotes for this example), first define a new report and add a parameter called PROJECT_ELEMENT_ID of type java.lang.String.

The following query will return all line items for any given Quote:

SELECT * FROM st_prj_project_element_line_item AS p
JOIN st_fin_document_line_item AS f ON p.id = f.id
WHERE p.project_element_id = $P{PROJECT_ELEMENT_ID}

Add a few fields to the report and upload it to Flex. Since the parameters are injected at run time, there's no need to manually add parameters to the Custom Report configuration.

Since this is an Instance-level report, this report cannot be added to the reports menu. Instead, it must be configured in the context of a Project Element. To run this report in context, upload the code to Flex as before and UPDATE, then go to the Project Element configuration page for Quotes (Projects > Project Elements > Quote). Click on the Reports tab, scroll down the list of Instance Level Reports and check the box for the new report, then click UPDATE. Now, if you open any Quote, you'll see a link on the right-hand menu and should be able to generate the new report.

XML-based Reports

XML-based reports are a special type of injected parameter report. At design time, the data source for the report consists of a locally loaded URL as described above. At runtime, however, most report parameters are injected, even the XML data producer itself.

We'll explore XML-based reports in the context of the Financial Document data producer, which is designed to produce XML that fully describes a financial document, such as a Quote or Purchase Order.

To recap, here's a sample data producer URL:

http://qatest.flexrentalsolutions.com/xml-data/data-producer-financial-document?PROJECT_ELEMENT_ID=e732edb6-7fd0-11e0-9f70-1231390c71d1

To create an XML-based report, create a new report in Jaspersoft, configure a new XML document Data Adapter using an XML file saved to your local disk, and set the query language to xpath2. The query can be any valid xpath expression. For this particular XML file, the xpath query for iterating over line items is this one:

/report/record

The system will load the XML and display a list of XML tags on a tree to the right. Drag any fields you wish to use in the report down to the field list and close the query dialog.

Once back in the design view, drag a few fields into the report and do a test run.

Next, define a new parameter called XML_PRODUCER_BEAN_ID of type java.util.String and set the default value to the data producer ID, which in this example would be data-producer-financial-document. Though this parameter is not used in any query, it is stored in the report source file and used by Flex to load the proper XML data producer.

Finally, define a new parameter called PROJECT_ELEMENT_ID of type java.util.String. This will signal Flex to inject the project element id.

Save the report and upload it to Flex. As in the Injected Parameter example, open the configuration screen for Quotes to enable the report. Then, open any Quote and launch the report.

XML Data Producers

There are several custom XML data producers currently available with Flex. The tables below enumerate all supported data producers along with configuration information needed to utilize them.

Note that many of the parameter "names" are slightly different in the modern system compared to the legacy system. Names like INVENTORY_GROUP are now INVENTORY_GROUP_ID. Consequently, the names shown below are displayed in the modern way; if you're using the legacy data producer, you may need to adjust such names to see expected results.

Modern Localization

The modern report engine allows for alternative methods to perform localization. By adding the following class imports to your Jasper Report code, you can access Flex's internal localization features.

<import value="com.flex.flexreport.report.dataproducer.format.LocalizationTools"/>
<import value="com.flex.flexreport.report.dataproducer.format.DatePattern"/>
<import value="com.flex.commons.date.DateTools"/>

Imports are placed after any Jasper "property" lines.

The request process generates and stores date and time "patterns" according to the locale applicable to the request. In many cases, the locale is dictated by your system configuration and the Project Element against which the request is being made. For Definition and Global reports, the locale is determine through other configuration. In any case, the resulting patterns are largely generated from the Java API. We do, however, use a custom formatter to arrive at the customary Flex "timestamp" pattern that is (in the US) MM/DD/YY hh:mm a, though this is also a modified version of the legacy version which uses a 4-digit year.

Methods available in LocalizationTools are the following:

// for dates:
/*
* Localize and format the dateTime to the provided datePattern.
*/
String localizeAndFormat(LocalDateTime dateTime, DatePattern datePattern)

/*
* Localize and format dateTime to the provided pattern datePattern and zoneId.
*
* Converts the dateTime to the timezone of the zoneId provided, treating it as though
* it started in UTC, then applies the datePattern.
*/
String localizeAndFormatToTimezone(LocalDateTime dateTime, DatePattern datePattern, String zoneId)

/*
* Formats the two dates provided into a hyphenated string.
* 
* Uses two TIME_STAMP patterned strings when the dates are on different days,
* or a SHORT_DATE with two TIME values when the dates are on the same day.
*
* Ex: given 2025-12-21T10:00 and 2025-12-21T12:00
* Result: 12/21/25 10:00am - 12:00pm
*
* Ex: given 2025-12-21T10:00 and 2025-12-22T12:00
* Result: 12/21/25 10:00am - 12/22/25 12:00pm
*/
String formatDateRange(LocalDateTime start, LocalDateTime end) // e.g., 12/21/25 10:00am - 12:00pm

// for numbers/currency

/*
* Formats the value provided using the current request's locale's currency data.
*/
String formatCurrency(BigDecimal value)

/*
* Formats the value provided using the current request's locale's current data, without a symbol.
*/
String formatCurrencyNoSymbols(BigDecimal value)

/*
* Formats the value provided using the current request's locale's numerical data.
*
* If the value provided lacks a decimal value, the INTEGER pattern is applied,
* otherwise the NUMBER_AMOUNT pattern is applied.
*/
String formatQuantityValue(BigDecimal value)

/*
* Formats the value provided using the current request's locale's percentage data.
*
* The value provided should reference 1 as 100%. Outputs up to 3 decimal places;
* rounds half-up.
*/
String formatPercentValue(BigDecimal value)

// for booleans
/*
* Returns the localized yes/no for the value provided.
* e.g., Yes, No, Oui, Non, Ja, Nein, Sí, No
*/
String formatBooleanValue(String bool)

The options for DatePattern are as follows:

FULL_DATE        // Friday, November 7, 2025
LONG_DATE        // November 7, 2025
SHORT_DATE       // 11/7/25
TIME             // 10:51 am
TIMESTAMP        // 11/7/25 10:51 am

Our DateTools class has many methods we use internally. The ones pertinent to report design are:

/*
* Converts an ISO date string into a LocalDateTime.
*
* Capable of handling string representing date-time, date, time, and Epoch long values.
*/
LocalDateTime parseDateString(String dateString)

/*
* Converts the given sqlTimestamp to a LocalDateTime.
*
* Timestamp is the class returned by SQL queries.
*/
LocalDateTime fromSQLTimestamp(Timestamp sqlTimestamp)

Examples

(content elided and formatting altered from actual Jasper output for readability)

<textFieldExpression><![CDATA[LocalizationTools.localizeAndFormat(
DateTools.fromSQLTimestamp($P{START_DATE_BEGIN}), DatePattern.SHORT_DATE) 
+ " - " + 
LocalizationTools.localizeAndFormat(
DateTools.fromSQLTimestamp($P{START_DATE_END}), DatePattern.SHORT_DATE)
]]>
</textFieldExpression>

<field name="amountEach" class="java.math.BigDecimal">
    <fieldDescription><![CDATA[amountEach]]></fieldDescription>
</field>
// ....
<textField isBlankWhenNull="true">
    <reportElement style="light" stretchType="RelativeToTallestObject" x="493" y="0" width="50" 
       height="12" isPrintInFirstWholeBand="true" uuid="75cf58f3-8ee1-465d-83b2-47a2368bc88c">
    </reportElement>
    <textElement textAlignment="Right" verticalAlignment="Middle" />
    <textFieldExpression><![CDATA[$F{amountExtended} != null ? 
        LocalizationTools.formatCurrency($F{amountExtended}) : ""]]>
    </textFieldExpression>
</textField>