Creating custom reports

Principles

The BizDock reporting is based on Jaspersoft. The goal is to create a report template in Jaspersoft Studio and to publish it in BizDock.

There are 2 kinds of report:

  • Standard report: represents a report common to all customers, so the standard reports are available in all BizDock instances.
  • Custom report: represents a specific report for a client, so a custom report is normally available only in one BizDock instance.

Principle of publication:

  • Standard report: it's directly included in a BizDock instance, the template files (Jaspersoft) are directly located in maf-desktop component and the needed SQL in maf-dbmdl. A standard report is published with a release and so cannot be updated until the next one.
  • Custom report: it's located “outside” a BizDock instance, the template files (Jaspersoft) are put on an SFTP server (one for each client) and the needed SQL are manually executed directly on the DB of the client. A custom report can be published and updated outside the releases.

Install and configure Jaspersoft Studio

First download the community Jaspersoft Studio version 6.1.1 (http://community.jaspersoft.com/download), then use the wizard for the installation.

After starting Jaspersoft Studio, some configurations are needed to render the reports as in BizDock.

Configure the Excel Exporter
  1. Click on “Window” and select “Preferences”
  2. Choose “Jaspersoft Studio → Exporters → Excel Exporter”
  3. Select the tab “Common”
  4. Check the option “Remove Empty Space Between Rows”
  5. Check the option “Remove Empty Space Between Columns”
  6. Select the tab “Cell”
  7. Check the option “Detect Cell Type”
  8. Click on “Apply” and “OK”
Configure the CSV Exporter
  1. Click on “Window” and select “Preferences”
  2. Choose “Jaspersoft Studio → Exporters → CSV Exporter”
  3. Set “;” under “Field Delimiter”
  4. Click on “Apply” and “OK”

Jaspersoft Studio folders' structure

This section gives an proposal of folder structure in Jaspersoft Studio. It's not mandatory to follow it, but it's really compliant with the structure in SVN/GitHub. The objective here is to explain which files should be committed (and published) and which ones not. With the proposed structure it's easy to checkout SVN/GitHub folders in order to work correctly and directly in Jaspersoft Studio.

Workspace structure

The root of the workspace contains one project for all standard reports and one project for each client.

The process to create a new project for the standard reports or for a client is the same:

  1. Go to “File”
  2. Select “New → Project…”
  3. Choose “Jaspersoft Studio → JasperReports Project” and click on “Next”
  4. Set “Standard” or the client name under “Name” and click on “Finish”
  5. Right-click on the new project name and select “New → Data adapter”
  6. Set “CI.xml” under “File name” and click on “Next”
  7. Choose “Database JDBC Connection” and click on “Next”
  8. Set “CI” under “Name”, select “com.mysql.jdbc.Driver” for “JDBC Driver”
  9. Configure the connection (JDBC Url, Username and Password) and click on “Finish”
  10. Redo the steps 5 to 9 for each wished DB (ST, client…)

Project structure

A Jaspersoft Studio project is composed by:

  • The list of Data Adapters
  • A folder for each report

The name of a report is also called “template”

Here is the process to create a new report:

  1. Create a new folder at the root of the project, its name should be the report name (for example “top_management”, special chars and spaces are not allowed)
  2. Right-click on the new folder and select “New → Jasper report”
  3. Select the template “Blank A4” (or “Blank A4 landscape”) and click on “Next”
  4. Set “{template}_main.jrxml” (for example top_management_main.jrxml) under “File name” and click on “Finish”

The template name and the key word “_main” in the file name of a report is indispensable to work correctly with BizDock.

Report structure

A report is represented by a folder with:

  • The main report
  • The resources (styles sheet, translations…)
  • The sub reports (if needed)

See the section Content of a report to understand the components and the elements of a report.

Commit a report

Only the report folders are committed, all other files (like the Data Adapters) should not.

Standard reports (GitHub):

https://github.com/theAgileFactory/maf-desktop-app/tree/master/conf/jasper

The standard reports are embedded in the desktop, so by committing a report no more effort is required to deploy the report folder. Note that DB requests are necessary (see the section BizDock database).

The custom reports should be manually deployed on an SFTP server (see the section Publish a report) in order to work with BizDock, but to keep a version history they are also committed in SVN, in the client folder of the dataload:

https://prod.the-agile-factory.net/svn/maf/maf-dataload/trunk/src/main/resources/custo/{client}/jasper/

The compiled reports (files .jasper) should not be committed, except the sub-reports.

Content of a report

Styles sheet

A best practice is to use a styles sheet in a report:

  1. Right-click on the folder of a report and select “New → Style Template File”
  2. Set “styles.jrtx” under “File name” and click on “Finish”
  3. Open the report (double-click on the report file)
  4. In the “Outline”” panel (bottom-left), right-click on “Styles” and select “Add Style Template Reference”
  5. Select the file “styles.jrtx” and click on “OK”

It's not possible to use a same styles sheet in many reports, so for each report a new styles sheet should be created.

Internationalization

When a user generates a report in BizDock, the configured languages of the report are proposed. If the report is not translated in the selected language then the default one is rendered.

To internationalize a report, a resource bundle should be created:

  1. Right-click on the folder of a report and select “New → Other…”
  2. Choose “Messages Editor → ResourceBundle” and click on “Next”
  3. Add the locales under “Choose or type a Locale”, set only the field “Lang.” with the language's short codes (en, fr, de…)

    The [Default] language should be left in order the report works if the selected language by the user is not available for the report.

  4. Click on “Finish”
  5. Open the report (double-click on the report file)
  6. In the “Outline” panel (bottom-left) simply click on the report name
  7. In the “Properties” panel (bottom-right) choose the tab “Report”
  8. Select the created resource bundle for the property “Resource Bundle”

It's not possible to use a same resource bundle in many reports, so for each report a new resource bundle should be created.

It's possible to change the current locale of Jaspersoft Studio in order to test a report in a specific language:

  1. Right-click on a report (the jrxml file) and select “Properties”
  2. Choose “Jaspersoft Studio → Report Execution”
  3. Check “Use File Settings”
  4. Select the wished Locale (for example “French”)
  5. Click on “Apply” and then on “OK”

To use the current language in an SQL query, a parameter should be created:

  1. Open a report (double-click on the report file)
  2. In the “Outline” panel (bottom-left) right-click on “Parameters” and select “Create Parameter”
  3. Click on the created parameter (usually named “Parameter1”)
  4. In the “Properties” panel (bottom-right) choose the tab “Object”
  5. Set “lang” under “Name”, uncheck “Is For Prompting” and set “$P{REPORT_LOCALE}.getLanguage()” under “Default Value Expression”

Usage example of the “lang” parameter:

SELECT 
  COUNT(v_pe.id) AS count,
  IFNULL(m_type.value, IFNULL(v_pe.type_key, '')) AS type,
  SUM(v_pe.budget) AS budget	
FROM v_custom_pe AS v_pe
  LEFT OUTER JOIN i18n_messages m_type ON v_pe.type_key = m_type.`key` AND m_type.language=$P{lang}
GROUP BY v_pe.type_key

Output format

The output format of a report is selected by the user when he generates it from BizDock. Currently the available formats are: PDF, EXCEL, CSV, WORD and POWER POINT. The available formats are specific to each report.

Sometimes it is useful to condition a report according to its output format, for example hiding the pagination or a chart in a non-PDF report. To do this, the BizDock report generator injects a “format” parameter (that corresponds to the format selected by the user) to all reports.

The “format” parameter is optional in a report: it's possible to create a report without it.

To exploit the “format” parameter, first it's needed to create it:

  1. Open a report (double-click on a report file)
  2. In the “Outline” panel (bottom-left) right-click on “Parameters” and select “Create Parameter”
  3. Click on the created parameter (usually named “Parameter1”)
  4. In the “Properties” panel (bottom-right) choose the tab “Object”
  5. Set “format” under “Name”, uncheck “Is For Prompting” and set “PDF” (with the quotes) under “Default Value Expression”

Then, it can be used in all expression fields, for example “display the page footer band only for a PDF output”:

The default value “PDF” could be also settled to any other. When a report is generated from BizDock, the value is overloaded by the real output format selected by the user.

BizDock parameters

BizDock provides some generic parameters related to the configuration of the instance. They can be simply used by creating a Jasper parameter with the same name and type (“Class”).

Name Description Type
use_purchase_order Corresponds to the system preference “Use purchase order”. java.lang.Boolean
default_currency_code The code of the default currency java.lang.String.
budget_tracking_active True if the budget tracking is active in the instance. java.lang.Boolean

Report parameters

A report parameter is a parameter specific to a report, it represents a business data generally used to filter a query. Such parameters are represented by custom attributes in BizDock.

Here is the process to create a report parameter in Jaspersoft Studio:

  1. Open a report (double-click on a report file)
  2. In the “Outline” panel (bottom-left) right-click on “Parameters” and select “Create Parameter”
  3. Click on the created parameter (usually named “Parameter1”)
  4. In the “Properties” panel (bottom-right) choose the tab “Object”
  5. Set the name of the parameter under “Name”, it will represents the {uuid} of the corresponding custom attribute. The convention is to use the format REPORT_{TEMPLATE}_{PARAMETER_NAME} (see below for an example)
  6. Set the type of the parameter under “Class”, it must match with the type of the corresponding custom attribute (see the mapping table below)
  7. Uncheck “Is For Prompting”
  8. Set the default value under “Default Value Expression”

Here is an example of a report parameter:

Here is the mapping table for the type of a report parameter according to a custom attribute type:

Custom attribute type Report parameter type Default value example
INTEGER java.lang.Integer 5
DECIMAL java.math.BigDecimal 12.5
BOOLEAN java.lang.Boolean true
DATE java.util.Date TODAY( )
STRING java.lang.String “abc”
TEXT java.lang.String “def”
SINGLE_ITEM java.lang.Long 1L
DYNAMIC_SINGLE_ITEM java.lang.Long 17L

The process to create the custom attribute relative to a report parameter is given in the section BizDock Database

Subreport

When a report includes a subreport, this last should be referred correctly in order the BizDock report generator finds and interprets it correctly.

The subreport file ({subreport_file_name}.jrxml) should be created in the report folder.

Here is the process to add a subreport in a report:

  1. In the “Elements” panel (top-right), drag and drop the “Subreport” element
  2. Check the box “Just create the subreport element” and click on “Finish”
  3. Click on the created element and select the “Subreport” tab in the “Properties” panel (bottom-right)
  4. Set “{subreport_file_name}.jasper” (with the quotes) under “Expression” (for example “portfolio_name.jasper”)
  5. Set “$P{REPORT_CONNECTION}” under “Connection Expression”

A subreport is included thanks the compiled file (“.jasper”), so this file should be also published.

When a subreport is updated, it must be recompiled (by clicking on the icon ), and for sure republished.

Publish a report

Processes

The publication of a report depends of its nature (standard or custom) and if this is a new report (meaning never published) or an updated report. Find below the 4 processes.

New standard report

  1. Commit the Jaspersoft report folder in the BizDock packaging, see the section Commit a report
  2. Create a MyBatis script (see the section BizDock database) with
    • the report configuration
    • the necessary views
    • the needed custom attributes
  3. Add and commit the translations (name, description, custom attributes) directly in the messages files of the Desktop (https://github.com/theAgileFactory/maf-desktop-app/tree/master/conf)
  4. The report will be available in the next release

Updated standard report

  1. Commit the Jaspersoft report folder in the BizDock packaging, see the section Commit a report
  2. If necessary, create a MyBatis script with the new custom attributes, see the section BizDock database)
  3. Edit and commit the translations (name, description, custom attributes) directly in the messages files of the Desktop (https://github.com/theAgileFactory/maf-desktop-app/tree/master/conf)
  4. The report modifications will be available in the next release

New custom report

  1. Put the Jaspersoft report folder in the SFTP of the client
  2. Create an SQL script (see the section BizDock database) with
    • the report configuration
    • the necessary views
    • the needed custom attributes
  3. Execute the SQL script directly in the database of the client
  4. Reload the report definitions in the BizDock instance of the client, see the section Reload the report definitions in BizDock
  5. The report is directly available in the BizDock instance of the client

Updated custom report

  1. Put the Jaspersoft report folder in the SFTP of the client
  2. Create an SQL script with the new custom attributes, see the section BizDock database
  3. Execute the SQL script directly in the database of the client
  4. Reload the report definitions in the BizDock instance of the client, see the section Reload the report definitions in BizDock
  5. The report modifications are directly available in the BizDock instance of the client

BizDock Database

Report configuration

Here is the pattern of the needed SQL to create a new report in BizDock:

INSERT INTO `reporting_authorization` (`expression`, `type`, `deleted`, `last_update`) 
  VALUES ('report.{template}', '0', '0', NOW());

INSERT INTO `reporting` (`deleted`, `last_update`, `reporting_category_id`, `reporting_authorization_id`, `name`, `description`, `is_public`, `is_active`, `is_standard`, `template`, `languages`, `formats`) 
  VALUES (
    '0', 
    NOW(),
    (SELECT `id` FROM `maf`.`reporting_category` WHERE `name`='reporting_category.default.name'), 
    (SELECT `id` FROM `maf`.`reporting_authorization` WHERE `expression`='report.{template}'),
    'report.{template}.name', 
    'report.{template}.description', 
    '0', 
    '1', 
    '{is_standard}', 
    '{template}',
    '{languages}',
    '{formats}');

#the DB translations are only for custom reports (in standard reports they are included in the messages files)
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.name', 'en', '{en_name}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.name', 'fr', '{fr_name}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.name', 'de', '{de_name}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.description', 'en', '{en_description}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.description', 'fr', '{fr_description}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.description', 'de', '{de_description}');

Some explanations about the variables:

  • {template}: the template name of the report (corresponds to the folder name in Jaspersoft Studio)
  • {is_standard}: 1 for Standard, 0 for Custom
  • {languages}: the available languages separated by a coma, for example “en,fr,de”
  • {formats}: the available formats separated by a coma, for example “PDF,EXCEL”
  • {en_name}, {fr_name}, {de_name}: the name of the report for each language
  • {en_description}, {fr_description}, {de_description}: the description of the report for each language

And finally an example:

INSERT INTO `maf`.`reporting_authorization` (`expression`, `type`, `deleted`, `last_update`) 
  VALUES ('report.roadmap', '0', '0', NOW());

INSERT INTO `maf`.`reporting` (`deleted`, `last_update`, `reporting_category_id`, `reporting_authorization_id`, `name`, `description`, `is_public`, `is_active`, `is_standard`, `template`, `languages`, `formats`) 
  VALUES (
    '0', 
    NOW(),
    (SELECT `id` FROM `maf`.`reporting_category` WHERE `name`='reporting_category.default.name'), 
    (SELECT `id` FROM `maf`.`reporting_authorization` WHERE `expression`='report.roadmap'),
    'report.roadmap.name', 
    'report.roadmap.description', 
    '0', 
    '1', 
    '1', 
    'roadmap'
    'en,fr,de',
    'PDF,EXCEL');

#the DB translations are only for custom reports (in standard reports they are included in the messages files)
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.name', 'en', 'Roadmap');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.name', 'fr', 'Feuille de route');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.name', 'de', 'Roadmap');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.description', 'en', 'List of active and non-concept initiatives.');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.description', 'fr', 'Liste des initiatives actives et non-conceptuelles.');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.description', 'de', 'Liste der aktiven und nicht-Konzept-Initiativen.');

Custom attribute

Here is the pattern of the needed SQL to create a new custom attribute in BizDock for a report:

INSERT INTO `maf`.`custom_attribute_definition`
  (`object_type`, `configuration`, `order`, `attribute_type`, `uuid`, `name`, `description`, `deleted`, `last_update`, `is_displayed`) 
  VALUES ('utils.form.ReportingParamsFormData:{template}','constraint.required=true',1,'{type}','REPORT_{TEMPLATE}_{PARAMETER_NAME}','report.{template}.{parameter_name}.name','report.{template}.{parameter_name}.description',0,NOW(),0);
  
#the DB translations are only for custom reports (in standard reports they are included in the messages files)
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.{parameter_name}.name', 'en', '{en_name}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.{parameter_name}.name', 'fr', '{fr_name}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.{parameter_name}.name', 'de', '{de_name}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.{parameter_name}.description', 'en', '{en_description}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.{parameter_name}.description', 'fr', '{fr_description}');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.{template}.{parameter_name}.description', 'de', '{de_description}');

Some explanations about the variables:

  • {template}: the template name of the report (corresponds to the folder name in Jaspersoft Studio)
  • {parameter_name}: the parameter name
  • {type}: the type of the custom attribute (INTEGER, DATE…)
  • {en_name}, {fr_name}, {de_name}: the name of the custom attribute for each language
  • {en_description}, {fr_description}, {de_description}: the description of the custom attribute for each language

And finally an example:

INSERT INTO `maf`.`custom_attribute_definition` 
  (`object_type`, `configuration`, `order`, `attribute_type`, `uuid`, `name`, `description`, `deleted`, `last_update`, `is_displayed`) 
  VALUES ('utils.form.ReportingParamsFormData:roadmap','constraint.required=true',1,'DATE','REPORT_ROADMAP_START_DATE','report.roadmap.start_date.name','report.roadmap.start_date.description',0,NOW(),0);
  
#the DB translations are only for custom reports (in standard reports they are included in the messages files)
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.start_date.name', 'en', 'Start date');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.start_date.name', 'fr', 'Date de début');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.start_date.name', 'de', 'Startdatum');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.start_date.description', 'en', '...');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.start_date.description', 'fr', '...');
INSERT INTO `maf`.`i18n_messages` (`key`, `language`, `value`) VALUES ('report.roadmap.start_date.description', 'de', '...');

Reload the report definitions in BizDock

When a custom report (new or updated) is published, its definition should be reloaded in BizDock, use the following root API to do it:

https://{client}.bizdock.io/api/core/root/report/reload

It should be also necessary to reload the i18n cache.