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
  • 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
• The Design Process

---

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:

click to expand

Instance Report Rendering Chain:

click to expand

---

Report Data Sources

There are also two ways to get data for a report. The most common is to use an SQL query and pull data directly from the database. Since Flex is based on an N-Tier Service Oriented Architecture, some information that may be desirable for inclusion in reports is the result of business logic and is therefore not available in the data tier. For this kind of information, reports must be based on information from the service tier. For these situations, Flex has created a number of XML Data Producers that can be invoked as URL's. 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.

A valid XML Data Producer URL takes 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 looks like:

http://qatest.flexrentalsolutions.com/xml-data/data-producer-financial-document?PROJECT_ELEMENT_ID=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. Next, click Edit Header from the Workbench Menu (right-hand side of the screen). If you have System Administrator permission for your system you will see a Show System Identifiers link at the top; if you do not have the System Administrator permission, contact your System Administrator to have them grant it. Clicking this link will show valid UUID's for the Project Element and the Project Element Definition. (These terms will be covered in more detail below under The Flex Data Model Explained.) Copy the value from the Project Element field and replace the UUID in our 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.

XML Data Producer URL's

If you get a valid result in your web browser, right click on the page and choose Save Page As to save the source XML to your local disk drive. Then, in Jaspersoft start creating a new Data Adapter, select XML document from the list and click Next. Give the Data Adapter a Name, then click Browser and locate the XML file you saved. Check the box to Enable namespaces support and the radio button for Use the report Xpath expression when filling the report. Finally, click Finish. Now you have a valid XML data source to work with in creating an XML-based report.

---

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 make sure the Language property is set to 'Java'. (The default is '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 350+ 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.

Modules

The Flex database is organized, much like the 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

 

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 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');

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 on 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 the following types of data elements:

• Managed Resources
• Project Elements
• Project Element Line Items

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.

Field Name	Description
Key	The name of the parameter as defined in the report.
Caption	The name of the parameter as you'd like it presented to the user.
Type	The data type of the parameter. Determines what type of input widget will be used in the report dialog.
Default Value	An optional default value for the parameter.

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:

Parameter Key	Description
RESOURCE_ID	The ID of a managed resource.
PROJECT_ELEMENT_ID	Most commonly used injected parameter. ID of a Quote or any other project element
PROJECT_ELEMENT_DEFINITION_ID	The id of a project element definition.
STATUS_OPTION_ID	ID of status if it's necessary to restrict report data by a status.
STATUS_OPTION_IDS	List of status ID's in order to restrict a report to a set of statuses.
XML_PRODUCER_BEAN_ID	For XML-based reports, this special parameter is used to tell the Flex architecture which data producer to use. Once an XML report is uploaded to Flex, this technique is used instead of the producer URL to locate the data.
DOCUMENT_VIEW_ID	For financial documents, this parameter refers to an View ID.

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.

Producer Name:	Location Data Producer
Description:	This data producer provides details for all configured business locations
Data Producer ID:	data-producer-bizops-location
Parameters:	None

Producer Name:	Financial Document Data Producer
Description:	This data producer is intended to support special cover sheets or print formats for financial documents.
Data Producer ID:	data-producer-financial-document
Parameters:	PROJECT_ELEMENT_ID

Producer Name:	Financial Budget Summary Data Producer
Description:	Special producer that aggregates financial data for a document. Useful for combining financial information for large Quotes with many line items.
Data Producer ID:	data-producer-financials-budget-summary
Parameters:	PROJECT_ELEMENT_ID

Producer Name:	Financial Elements Summary Data Producer
Description:	Special producer that aggregates financial data for an entire project tree. Useful for combining financial information from multiple Quotes or Invoices associated with a project.
Data Producer ID:	data-producer-financial-elements
Parameters:	PROJECT_ELEMENT_DEFINITION_ID, STATUS, CLIENT, DUE_BEFORE, DUE_AFTER, PERSON_RESPONSIBLE

Producer Name:	Equipment List Data Producer
Description:	This data producer is intended to support print formats for equipment lists (e.g. Manifest, Pullsheet, etc).
Data Producer ID:	data-producer-equipment-list
Parameters:	PROJECT_ELEMENT_ID, SORT_TYPE, PRODUCER_MODE
SORT_TYPE Options:	hierarchy, MODE_TOP_LEVEL
PRODUCER_MODE Options:	(default is none), MODE_CONTAINER, MODE_UNRETURNED_LINE_ITEMS, MODE_ADDITIONAL_SCANNED, MODE_SAFE_WORK_METHOD

Producer Name:	Equipment List Line Item Data Producer
Description:	This data producer is intended to support aggregate data across equipment list line items.
Data Producer ID:	data-producer-equipment-list-line-item
Parameters:	PROJECT_ELEMENT_DEFINITION_ID, PRODUCER_MODE, START_DATE, END_DATE, LOCATION, INVENTORY_GROUP, INCLUDE_NON_UTILIZED_INVENTORY, RESOURCE_KIND
PRODUCER_MODE Options:	(default is none), MODE_CONTAINER, MODE_UNRETURNED_LINE_ITEMS, MODE_ADDITIONAL_SCANNED, MODE_SAFE_WORK_METHOD
INCLUDE_NON_UTILIZED_INVENTORY Options:	(default is none = false), true
RESOURCE_KIND Options:	INVENTORY, SERVICES

Producer Name:	Project Element Data Producer
Description:	This data producer is intended to support print formats for any type of project element however the available data is limited.
Data Producer ID:	data-producer-project-element
Parameters:	PROJECT_ELEMENT_ID

Producer Name:	Staffing Data Producer
Description:	This data producer is intended to provide staffing information
Data Producer ID:	data-producer-staffing
Parameters:	MODES, START_DATE, END_DATE
MODES Options:	unfilled

Producer Name:	Threshold Data Producer
Description:	This data producer is intended to support the Minimum and Maximum Threshold values for inventory items
Data Producer ID:	data-producer-threshold-qty
Parameters:	PRODUCER_MODE, RESOURCE_TYPE, LOCATION
PRODUCER_MODE Options:	less-than, greater-than

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

---

The Design Process

More coming soon....

---